为每次执行的oracle更改NLS_DATE_LANGUAGE

[bisal(Chen Liu)]

有个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;

也不行，请高手指点！

[tangren]

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

[bisal(Chen Liu)]

SELECT to_date('01JAN10', 'ddmonyy', 'nls_date_language=american') FROM dual;

这个是改变当前session的nls_date_language么？还是改变系统的呢？

[iqlife]

都不是,改变当前查询环境

[bisal(Chen Liu)]

弱弱地再问问：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;

是这样么？

[bisal(Chen Liu)]

可能我之前没有说清楚，包含有一个用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字符集，哪里有问题么？谢谢！

[iqlife]

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

[bisal(Chen Liu)]

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

[iqlife]

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

[bisal(Chen Liu)]

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

但等job自动运行时就会报错

[iqlife]

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

[iqlife]

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) 任务运行的其他一些会话参数

[bisal(Chen Liu)]

我使用：
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，可以自动执行成功，但最近在尝试的时候，都是不能自动执行，难道就不能自动执行了？

[bisal(Chen Liu)]

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

但不是太懂。。。不知道和这个有没有关系？

[iqlife]

http://blog.sina.com.cn/s/blog_543e392e01009bz3.html

应该是这样的,看来客户端对JOB的影响比较大呢...

[bisal(Chen Liu)]

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

[bisal(Chen Liu)]

看到有的帖子说：
“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，这应该会引起错误的啊？

[bisal(Chen Liu)]

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失败的原因？谢谢！

[iqlife]

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

[bisal(Chen Liu)]

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