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):' ').'</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; }
}
?>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
