Oracle Scheduler - NLS_ENV

What kind of NLS env is used when a scheduler job is running? And what if this job creates some more children jobs?

The answer is that this job and its children will inherit NLS env from the creator's. That means, if you create a scheduler job in an English session, all relative jobs run in English language enviroment. On other hand, if creator runs in non-English languange, so do all jobs.

Some confusing error show up when we use NLS related functions, such as to_char(), to_date() and next_day(). For instance, the next_day() accepts a string type argument and returns the date of next weekday. If we give it the 'Monday' as argument and run it in a non-English session, the error message 'ORA-01846: not a valid day of the week' shows up, because Oracle couldn't recognize 'Monday' as a valid weekday.

Let's test it and have a look. First of all, we are preparing some scripts.

SQL_1:
-- create tales to contain test result
drop table testnls;
create table testnls(dt date, msg varchar2(1000));
truncate table testnls;
select * from testnls;

-- proc uses next_day
create or replace procedure p_testnls1 is
  l_msg varchar2(1000);
begin
  insert into testnls
    (dt, msg)
  values
    (sysdate, to_char(next_day(sysdate, 'Monday'),'yyyy-mm-dd'));
  commit;
exception
  when others then
    l_msg := sqlerrm;
    rollback;
    insert into testnls (dt, msg) values (sysdate, l_msg);
    commit;
end;
/

-- proc uses next_day in subjob
create or replace procedure p_testnls2 is
  l_job int;
begin
  dbms_job.submit(job  => l_job,
                  what => '
  declare   
    l_msg varchar2(1000);
  begin
  insert into testnls
    (dt, msg)
  values
    (sysdate, to_char(next_day(sysdate, ''Monday''),''yyyy-mm-dd''));
    exception
  when others then
    l_msg := sqlerrm;
    rollback;
    insert into testnls (dt, msg) values (sysdate, l_msg);
    commit;
  end; 
');
  commit;
end;
/

 

SQL_2:  re-create the scheduler
begin
  dbms_scheduler.drop_job('TEST_NLS');
end;
/
begin
  -- here uses subjob version process to simulate the WF
  -- that means a running job craetes another subjob to do actual work 
  DBMS_SCHEDULER.CREATE_JOB(job_name        => 'TEST_NLS',
                            start_date      => systimestamp,
                            repeat_interval => 'SYSDATE + 10 / 86400',
                            end_date        => null,
                            job_class       => 'DEFAULT_JOB_CLASS',
                            job_type        => 'PLSQL_BLOCK',
                            job_action      => 'BEGIN p_testnls2(); END;',
                            comments        => 'TEST_NLS');
  DBMS_SCHEDULER.ENABLE(name => 'TEST_NLS');
end;
/

SQL_3: check the scheduler's nls attribute
select j.job_name,
       substr(j.nls_env, instr(j.nls_env, 'NLS_DATE_LANGUAGE'), 40)
  from user_scheduler_jobs j
 where j.job_name in ('TEST_NLS');


OK,  now we do the whole test under Oracle10g
SQL> select * from v$version; 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0    Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

Step_1
Run SQL_1 to create all necessary objects.

Step_2
Now we are running in an English language session, and we can tell it by this

SQL> select * from nls_session_parameters p where p.parameter='NLS_DATE_LANGUAGE'; 
PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_DATE_LANGUAGE              AMERICAN

Step_3
Run SQL_2 to create the scheduler job and check its NLS_ENV attribute by running SQL_3

SQL> @sql_3
JOB_NAME                       SUBSTR(J.NLS_ENV,INSTR(J.NLS_E
------------------------------ ----------------------------------------
TEST_NLS                       NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='B

Step_4
So far it work very well.

SQL> select * from testnls;
DT          MSG
----------- --------------------------------------------------------------------------------
2008-10-7 2 2008-10-13
2008-10-7 2 2008-10-13
2008-10-7 2 2008-10-13
2008-10-7 2 2008-10-13

Step_5
Let's change nls_date_language to a non_English, and re-run SQL_2,3,4 to check the error message

SQL> alter session set nls_date_language='simplified chinese'; 
SQL> select * from nls_session_parameters p where p.parameter='NLS_DATE_LANGUAGE';
PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_DATE_LANGUAGE              SIMPLIFIED CHINESE

SQL> select * from testnls;
 
DT          MSG
----------- --------------------------------------------------------------------------------
2008-10-7 2 2008-10-13
2008-10-7 2 2008-10-13
2008-10-7 2 2008-10-13
2008-10-7 2 2008-10-13
2008-10-7 2 ORA-01846: not a valid day of the week
2008-10-7 2 ORA-01846: not a valid day of the week
2008-10-7 2 ORA-01846: not a valid day of the week