为每次执行的oracle更改NLS_DATE_LANGUAGE

Question

有个job，用于每天执行删除过期数据，但现在有个存储时间的字段是varchar2，值是'01JAN10'，字符集是中文字符集，这样当执行的时候就无法用to_date()进行转换，如果每次调用ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';就可以执行，但这个语句现在无法放在下面job中的begin前或后，单独执行一次ALTER，再执行job是可以的，但当job建立之后，每次就不会执行ALTER了，有什么办法么？
DECLARE
  job_table number;
BEGIN
  dbms_job.submit(job => job_table,
                      what => ' clear_table;',
                      next_date => trunc(SYSDATE)+1085/1440,
                      interval => 'trunc(sysdate)+1085/1440');
  commit;
end;后来尝试了：
declare
  job_pre_ldp number;
  sql_string varchar2(2000);
BEGIN
  sql_string := 'ALTER SESSION SET NLS_DATE_LANGUAGE='''||'AMERICAN'||'''';
  execute immediate sql_string;
  dbms_job.submit(job => job_pre_ldp,
                      what => 'clear_pre_ldp;',
                      next_date => trunc(SYSDATE)+1125/1440,
                      interval => 'trunc(sysdate)+1125/1440');
  commit;
end;也不行，请高手指点！

Answer 1

可以在to_date函数中指定
SELECT to_date('01JAN10', 'ddmonyy', 'nls_date_language=american') FROM dual;

Answer 2

SELECT to_date('01JAN10', 'ddmonyy', 'nls_date_language=american') FROM dual;这个是改变当前session的nls_date_language么？还是改变系统的呢？

Answer 3

弱弱地再问问：session与当前查询环境不同么？如果这里使用：
create or replace procedure clear_table is
begin
delete FROM PRE_LDP where (sysdate - to_date(cdate, 'DDMONYY', 'nls_date_language=american')) > 730;
commit;
end;
就相当于：
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
DECLARE
  job_table number;
BEGIN
  dbms_job.submit(job => job_table,
  what => ' clear_table;',
  next_date => trunc(SYSDATE)+1085/1440,
  interval => 'trunc(sysdate)+1085/1440');
  commit;
end;是这样么？

Answer 4

可能我之前没有说清楚，包含有一个用varchar2类型存储的日期字段中(值类似于“01JAN09”)，先建立一个存储过程：
create or replace procedure clear_bak is
begin
delete FROM TABLE where (sysdate - to_date(cdate, 'DDMONYY', 'nls_date_language=american')) > 730;
（或delete FROM TABLE where (sysdate - to_date(cdate, 'DDMONYY') > 730;）
commit;
end;
测试可以执行，没有问题，然后建立一个job：
declare
  job_bak number;
BEGIN
  dbms_job.submit(job => job_bak,
                      what => 'clear_bak;',
                      next_date => trunc(SYSDATE)+855/1440,
                      interval => 'trunc(sysdate)+855/1440');
  commit;
end;
用于定时执行这个存储过程，但无论使用：
(1)、
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
+以上job的定义还是
(2)、
declare
  job_bak number;
  sql_string varchar2(2000);
BEGIN
  sql_string := 'ALTER SESSION SET NLS_DATE_LANGUAGE='''||'AMERICAN'||'''';
  execute immediate sql_string;
  dbms_job.submit(job => job_bak,
                      what => 'clear_bak;',
                      next_date => trunc(SYSDATE)+815/1440,
                      interval => 'trunc(sysdate)+815/1440');
  commit;
end;还是：
(3)、
建立存储过程时使用：
delete FROM TABLE where (sysdate - to_date(cdate, 'DDMONYY') > 730;
有的只是在plsql中第一次用RUN(点job右键选择)时可以正常运行，一旦利用其中定义的时间点让其自动运行时就会出错，不能执行job。请问这是怎么回事？有人说是job对应的字符集是中文的，那我这里用了这些方法来改变当前session的nls_date_language字符集，哪里有问题么？谢谢！

Answer 5

首先你手动运行的话,使用的当前用户的SESSION,
而系统自动运行的话,并没有改变当前是语言环境,使用系统默认的
没有遇到你这样的问题,按道理来说delete FROM TABLE where (sysdate - to_date(cdate, 'DDMONYY', 'nls_date_language=american')) > 730;
这样运行的话是会改变的,
可是JOB是以什么身份运行的呢,创建者的身份>?还是SYS?

Answer 6

是job的创建者，我试了下，建立好job运行没有问题，即使关闭当前plsql再打开一个新的，手动执行这个job也没有问题，但当job自动运行时就报错，session的范围是什么？不是关闭plsql就结束了？那为什么重新打开一个plsql后仍可以手工执行job(此时应包括ALTER SESSION的作用)，自动执行时就无效了？

Answer 7

你断开后,此SESSION就失效了啊,你设置的语言环境也就失效了,刚才查了查,发现每个JOB似乎可以设置不同的NLS,正在查看设置方法,
select * from user_jobs;,可以看到每个JOB的信息(包括NLS,强行修改的NLS不知道会怎样哈),继续GOOGLE中...

Answer 8

但奇怪的是，在建立job前用ALTER SESSION改下NLS_DATE_LANGUAGE，然后我关闭plsql，再打开一个新的plsql，手动执行job，却可以执行，查看这个job的SQL是从begin开始的，并没有ALTER SESSION语句，为什么好像还是执行了ALTER SESSION所以才正常呢？但等job自动运行时就会报错

Answer 9

1:看看你用户配置文件是否默认的NLS_lang 设置
2:有篇文章说客户端的NLS决定SESSION的NLS...

Answer 10

http://xiaoxinshome.javaeye.com/blog/343967
找到了,在建立的时候指定他的参数字段（列）          类型                 描述
JOB                NUMBER          任务的唯一标示号
LOG_USER           VARCHAR2(30)    提交任务的用户
PRIV_USER          VARCHAR2(30)    赋予任务权限的用户
SCHEMA_USER        VARCHAR2(30)    对任务作语法分析的用户模式
LAST_DATE          DATE            最后一次成功运行任务的时间
LAST_SEC           VARCHAR2(8)     如HH24:MM:SS格式的last_date日期的小时，分钟和秒
THIS_DATE     DATE            正在运行任务的开始时间，如果没有运行任务则为null
THIS_SEC     VARCHAR2(8)     如HH24:MM:SS格式的this_date日期的小时，分钟和秒
NEXT_DATE          DATE            下一次定时运行任务的时间
NEXT_SEC           VARCHAR2(8)     如HH24:MM:SS格式的next_date日期的小时，分钟和秒
TOTAL_TIME         NUMBER          该任务运行所需要的总时间，单位为秒
BROKEN             VARCHAR2(1)     标志参数，Y标示任务中断，以后不会运行
INTERVAL           VARCHAR2(200)   用于计算下一运行时间的表达式
FAILURES    NUMBER     任务运行连续没有成功的次数
WHAT               VARCHAR2(2000) 执行任务的PL/SQL块
CURRENT_SESSION_LABEL RAW          MLSLABEL 该任务的信任Oracle会话符
CLEARANCE_HI      RAW MLSLABEL     该任务可信任的Oracle最大间隙
CLEARANCE_LO      RAW              MLSLABEL 该任务可信任的Oracle最小间隙
NLS_ENV           VARCHAR2(2000)   任务运行的NLS会话设置
MISC_ENV          RAW(32)          任务运行的其他一些会话参数

Answer 11

我使用：
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
declare
  job_table number;
BEGIN
  dbms_job.submit(job => job_pre_ldp,
                      what => 'clear_table;',
                      next_date => trunc(SYSDATE)+945/1440,
                      interval => 'trunc(sysdate)+945/1440');
  commit;
end;之后查看user_jobs表，看到其中的NLS_DATE_LANGUAGE='AMERICAN'是改变了字符集，即使重新打开一个plsql也是如此，而且手动执行job没有问题，就是自动运行时会出错，我推断是不是：
“NLS_DATE_LANGUAGE='AMERICAN'这个是当前plsql客户端的会话设置，因此当每次手动执行时，会调用这个改为AMERICAN的设置执行job，但自动执行job时，会是在oracle server端执行，oracle server端并未给job的NLS_DATE_LANGUAGE环境变量进行更改，依旧使用中文字符集，所以出错了”。但比较奇怪的是，之前不知道是怎么建立的job，可以自动执行成功，但最近在尝试的时候，都是不能自动执行，难道就不能自动执行了？

Answer 12

有篇博文这样写：
“例如，*plus开始，被一个拥有oracle软件的unix用户使用rdbms软件通过指定acle_sid的适配器，它仍然作为客户端并且其行为由客户端的nls参数确定。当一个应用被开始时，它从环境设置中初始化客户的nls环境，使用这些设置所有nls执行局部性的被完成。当应用连接到数据库时，一个会话在服务器上被建立。这新的会话初始化它的nls环境使用在初始化参数文件中规定的nls初始化参数。那些设置之后能被alter session语句改变。这些语句仅仅改变nls环境，它不能改变本地客户的nls环境。这些会话改变在服务器上可以使用sql或pl/sql语句来执行。紧跟着连接，如果这些nls环境设置在客户端被定义，那么一个隐含的alter session语句同步客户和会话的环境。”但不是太懂不知道和这个有没有关系？

Answer 13

http://blog.sina.com.cn/s/blog_543e392e01009bz3.html应该是这样的,看来客户端对JOB的影响比较大呢...

Answer 14

谢谢两位的帮助，使用ALTER SESSION NLS_DATE_LANGUAGE或NLS_LANGUAGE可以建立正确的job执行了。

Answer 15

看到有的帖子说：
“oracle中每个job都可以指定不同的NLS_LANG信息。同时pl/sql中修改job时，无论你是否对NLS_LANG信息是否进行过修改，pl/sql都将会把job的NLS_LANG信息修改为客户端注册表中的NLS_LANG的值，至此找到了问题的根源。修改客户端的NLS_LANG的注册表信息与oracle服务器的一致，再次对job进行修改，此时job的NLS_LANG的值即与服务器一致，同时job也能正常执行。”就是说如果在PL/SQL中EDIT一个job的内容，即使之前使用ALTER SESSION修改了NLS_LANG变量为AMERICAN，这时也会按照PL/SQL所在客户端的NLS_LANG变量进行赋值，此时就无法执行这个job了。但我看了下客户端的注册表，其中的NLS_LANG值是NA，这应该会引起错误的啊？

Answer 16

create or replace procedure rebuild_index is
begin
execute immediate 'ALTER INDEX TABLE_INDEX REBUILD';
commit;
end;
/
declare
job_rebuild number;
begin
sys.dbms_job.submit(job => job_rebuild,
               what => 'rebuild_index;',
               next_date => TRUNC(LAST_DAY(SYSDATE))+1+1/24,
               interval => 'TRUNC(LAST_DAY(SYSDATE))+1+1/24');
  commit;
end;
/上面这个存储过程用来重建TABLE_INDEX索引，job是让每个月的1日执行这个存储过程，为什么在plsql中建立之后，手动执行这个job可以，但若让job自动运行，则到了1日却无法执行。这里数据库使用的是中文字符集，但这里存储过程与job没有涉及到这方面的影响好像，还请指教如何知道job失败的原因？谢谢！ 

Answer 17

1：show error看看有错误？创建JOB有编译错误？
2：在存储过程里加入日志功能，看是否有执行这个存储过程，如果没有，说明JOB有问题

Answer 18

show error是否是admin用户才可以使用呢？
弱弱地问下在存储过程中加入日志功能就是打印一句话？