Php, Oracle Package and NLS Settings

Question

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

score 0 · Answer 1 · answered Oct 24 '15 at 05:30

0

The NS_DATE_FORMAT is a parameter that depends on a session. Normal practice is to write a logon trigger depending on the application requirement to set this parameter write at the session logon. It can be done in below way:

  CREATE OR REPLACE TRIGGER CHANGE_DATE_FORMAT
     AFTER LOGON ON DATABASE
    WHEN (
    USER='Oracle Package Connection / Session User'
     )
    begin
    execute immediate 'alter session set nls_date_format = ''YYYY/MM/DD HH24:MI:SS'' ';
    end ;
    /

answered Oct 24 '15 at 05:30

dcieslak

2,697
1
12
19

Thanks for your suggestion; I noticed that if I execute a query from Php, the nls is not affected, but if I run a package, then the following querie come with altered settings. An importante note: the packages are executed calling a stored procedure thar run a job that execute the package (that is, a different session run the package). – user1 Oct 26 '15 at 08:33
Running the package "directly" (without the job) doesn't modify the nls settings. – user1 Oct 26 '15 at 08:44
how do you create a job? Do you use DBMS_SCHEDULER? The DBMS_SCHEDULER jobs are supposed to use the same nls setting to these when the job was created . Describe more how you call/create your job. – dcieslak Oct 27 '15 at 00:38
Thanks for your replay; I have the following situation: Php run a procedure (in the target package) that run a Job (created with dbms_scheduler.create_job); the job run a Program (created with dbms_scheduler.create_program) that fires the "main" procedure of the package. – user1 Oct 27 '15 at 07:51

user1 · Accepted Answer · 2015-10-27T10:28:38.377

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

Php, Oracle Package and NLS Settings

2 Answers2