Why is DBMS Scheduler changing database session language?

Question

I have LEVSTAT_PLNGRP procedure that needs to be called at certain time and in response it makes an entry in one of the DB table.

Initially select VALUE from V$NLS_PARAMETERS; query returns SWEDISH as output.

when I run LEVSTAT_PLNGRP procedure manually, it makes correct data entry in respective table and select VALUE from V$NLS_PARAMETERS; query returns SWEDISH as output after the run.

But when I try to run LEVSTAT_PLNGRP procedure via DBMS Scheduler, it fails to make entry in the table and select VALUE from V$NLS_PARAMETERS; query returns ENGLISH as output after the run.

Does DBMS scheduler change the language of the DB session or is there a default parameter set somewhere?

The underlying problem is that your PL/SQL makes assumptions about the current NLS language - why is it doing that? And what exactly is the code that fails? (**[edit]** your question, do not post code in comments) — , Apr 19 '18 at 14:27

score 1 · Answer 1 · answered Apr 30 '18 at 02:04

DBMS_SCHEDULER uses the NLS settings of the session that created the job. Which may not be the same as the NLS settings of the session that created the procedure.

For example, my session default NLS_LANGUAGE is ENGLISH:

SQL> declare a number; begin a := 1/0; end;
  2  /
declare a number; begin a := 1/0; end;
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 1

If I change it to Swedish and create the job it runs with Swedish:

SQL> alter session set nls_language = 'Swedish';

Session ar andrad.

SQL> begin
  2     dbms_scheduler.create_job(
  3             job_name => 'TEST_JOB1',
  4             job_type => 'PLSQL_BLOCK',
  5             job_action => 'declare a number; begin a := 1/0; end;',
  6             enabled => true
  7     );
  8  end;
  9  /

PL/SQL-procedur har utforts.

SQL> select errors from dba_scheduler_job_run_details where job_name = 'TEST_JOB1';

ERRORS
--------------------------------------------------------------------------------
ORA-01476: division med noll
ORA-06512: vid rad 1

SQL>

But programs should never fail because of NLS settings. PL/SQL code should never make assumptions about client settings. If something must be done in a specific language, avoid all implicit conversions and hard-code that language in the code.

Why is DBMS Scheduler changing database session language?

1 Answers1