Re: OCI ignoring NLS_DATE_FORMAT parameter

Doug McMaster wrote:

Regardless of how I set the NLS_DATE_FORMAT parameter, when I do a select statement DATE fields are returned in the Oracle default DD-MON-RR format. I can successfully set NLS_DATE_FORMAT using either an environment variable and restarting apache or by using ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-RRRR HH24:MI:SS' (I would prefer to use this method). I can verify that the NLS_DATE_FORMAT is set to my desired format both before and after my select statement by running the query SELECT * FROM NLS_SESSION_PARAMETERS WHERE PARAMETER='NLS_DATE_FORMAT' in my script. This problem occurs with both PEAR DB, and with ADODB oci8 drivers. However if I use the ADODB oracle drivers, which use the older ora functions instead of the oci functions, ALTER SESSION works and I am able to retrieve dates in the format I set. Unfortunately, the software I'm working on uses PEAR DB_DataObject, and the PEAR DB Oracle driver uses the oci calls. Version info: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production PHP 4.3.4 Apache 1.3.29 Anyone have any ideas or suggestions on why I'm seeing this problem? Thanks, Doug

In ADOdb, make sure to set the format before connecting: $db = ADONewConnection("oci8"); // $db->debug = true; // Date format is set before connecting. $db->NLS_DATE_FORMAT = 'RRRR-MM-DD HH24:MI:SS'; if (!@$db->Connect(false, "scott", "tiger"))

    error('Connect', $db->ErrorMsg());

In OCI8, see the scipt below. In PEAR, . . . it's been a while since I used dates in PEAR. Chris ---------- <?php $conn = OCILogon("scott", "tiger"); if (!$conn) { echo "Error connecting"; die; } query($conn, "select * from nls_session_parameters where parameter='NLS_DATE_FORMAT'"); query($conn, "select sysdate from dual"); alterdate($conn); query($conn, "select * from nls_session_parameters where parameter='NLS_DATE_FORMAT'"); query($conn, "select sysdate from dual"); exit; function query($conn, $query) {

    $stid = OCIParse($conn, $query);
    if (!$stid) { echo "Error parsing"; die; }

    $r = OCIExecute($stid, OCI_DEFAULT);
    if (!$r) { echo "Error executing"; die; }

    print '<table border="1">';
    while ($succ = OCIFetchInto($stid, $row, OCI_RETURN_NULLS)) {
      print '<tr>';
      foreach ($row as $item) {

print '<td>'.($item?htmlentities($item):'&nbsp;').'</td>';

      }
      print '</tr>';
    }
    print '</table>';

} function alterdate($conn) {

    $cmd = "alter session set nls_date_format = 'YYYY-MON-DD HH:MI'";
    $stid = OCIParse($conn, $cmd);
    if (!$stid) { echo "Error parsing"; die; }

    $r = ociexecute($stid, OCI_DEFAULT);
    if (!$r) { echo "Error executing"; die; }

} ?>