Search code examples
phporacle-databaseoracle-call-interfacenls

Php, Oracle Package and NLS Settings



with this environment:

Oracle Database 11g 11.2.0.3.0 64bit Production
PHP version: 5.3.3
OCI8 Version 2.0.7

I have a web site in Php language that executes:
ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY/MM/DD HH24:MI:SS"
when it loads the first page; then it executes an Oracle package (ZZZJOB):
$ORA_DB = oci_pconnect(ORA_USER, ORA_PASSWORD, ORA_TNSCATALOG, "UTF8");
--$sql="BEGIN ZZZJOB.RUN_ZZZTEST_JOB; END";
$stid = oci_parse($ORA_DB, html_entity_decode($sql));
$r = oci_execute($stid, OCI_DEFAULT);

Then, after the package returns, the new default nls_date_format in Php web pages (executing other queries) is the database default, i.e. DD-Mon-YYYY HH24:MI:SS
How can I prevent the execution of (any) package changes the nls format used by Php?
It seems that the previous Php connection / session is replaced by the "Oracle Package Connection / Session".
Thanks,
Igor


Solution

  • Finally, I found the solutions: if I write:
    DBMS_SCHEDULER.run_job ('ZZZTEST_JOB', TRUE); --the default
    the NLS settings are changed; instead,with
    DBMS_SCHEDULER.run_job ('ZZZTEST_JOB', FALSE);
    no changes are done.
    So, I need to run the job in another session and not in the session of the procedure that was invoked from.
    See also https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm#i1013568 Thanks,
    Igor