How can I alter NLS_DATE_FORMAT in PHP

Question

How can I alter the NLS_DATE_FORMAT from an Oracle database using PDO. So far I have this:

    $date_set = "DD-MM-YYYY HH24:MI:SS";
    $query = $this->conn->prepare("ALTER SESSION SET 'NLS_DATE_FORMAT' = ?");
    $query->execute(array($date_set));

But when I try to fetch the session:

    $query = "select * from nls_session_parameters where parameter='NLS_DATE_FORMAT'";
    $parameters = array();
    $test = $this->fetchRow($query, $parameters);
    var_dump($test); exit();

it returns:

DD-MON-RR"

score 2 · Accepted Answer · edited May 23 '17 at 12:33

This query:

ALTER SESSION SET 'NLS_DATE_FORMAT' = 'DD-MM-YYYY HH24:MI:SS'

... should trigger this error:

ORA-00922: missing or invalid option

If you cannot detect it, there's an issue in your error checking code. Most likely, you haven't configured PDO to throw exceptions.

The problem is that NLS_DATE_FORMAT is not a string but an identifier:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';

score 2 · Answer 2 · answered Jul 15 '14 at 09:09

In my system, setting NLS_DATE_FORMAT have no effect, maybe because I used oracle instant client. The cure for my system is to add another alter session command :

  ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';
  ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN';

It seems that the OCI driver got confused when NLS_DATE_FORMAT is set and NLS_DATE_LANGUAGE is unset, and reverting to the DD-MON-RR format.

Jan · Answer 3 · 2015-07-24T12:21:32.627

When using PHP/PDO/Oracle you can use:

//change settings below
$username='user';
$password='mypassword';
$dbname='databasename';
$hostname='localhost';
$port='1521';
$sid='XE'; //could also be ORCL
$charset='WE8ISO8859P1'; //or UTF8 or whatever charset
$oraclelangauge='AMERICAN';

$tns = "(DESCRIPTION = 
      (ADDRESS = (PROTOCOL = TCP)(HOST = ".$hostname.")(PORT = ".$port."))
      (CONNECT_DATA = (SID = ".$sid.")(SERVER = DEDICATED)))";

$dbDB = new PDO("oci:dbname=".$tns.";charset=".$charset, $username, $password);
$result = $dbDB->query("ALTER SESSION SET NLS_DATE_LANGUAGE = '".$oraclelanguage."'");

Be sure to have php_pdo_oci extension enabled and first double check your Oracle connection settings with a different Oracle client like SQL Developer.

Also first check if ALTER SESSION SET NLS_DATE_LANGUAGE = <language> is working, for the $oraclelanguage setting you have set, in an Oracle client.

How can I alter NLS_DATE_FORMAT in PHP

3 Answers3

Linked