dongmu9253
2015-10-23 14:17
浏览 110

Php,Oracle包和NLS设置


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

图片转代码服务由CSDN问答提供 功能建议


在此环境中:

Oracle Database 11g 11.2.0.3。 0 64bit生产
PHP版本:5.3.3
OCI8版本2.0.7

我有一个用Php语言执行的网站:
ALTER SESSION SET NLS_DATE_FORMAT =''YYYY / MM / DD HH24:MI:SS“
当它加载第一页时; 它是否执行Oracle包(ZZZJOB):
< code> $ 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);

然后,在包返回后,Php网页中的新默认nls_date_format(执行其他查询)是数据库默认值,即DD-Mon-YYYY HH24:MI :SS
如何防止执行(任何)包更改Php使用的nls格式?
似乎先前的Php连接/会话被“Oracle Package Connection / Session”替换。
谢谢,
Igor

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dssk35460 2015-10-27 10:22
    已采纳

    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

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • dqb77047 2015-10-24 05:30

    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 ;
        /
    
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题