Talking in Tongues – NLS with dbms_scheduler

I’ve implemented enterprise scheduling with dbms_scheduler at an international customer. All is working like a charm, until some jobs started ‘talking’ the local lingo (dutch).

The simple answer is that a submitted scheduler job stores the NLS session settings along with the job definition when creating a scheduler job. To verify these settings for your jobs:

select job_name, nls_env from all_scheduler_jobs;

Unfortunately you cannot modify the NLS attributes of a scheduler job. The only way to change the NLS attributes, is to recreate the job with your session having the correct NLS attributes. That’s not what I wanted as it depends on people being aware of this issue.

The easy way to circumvent and fix this issue here, was to implement a schema logon trigger.

create or replace
trigger SYSJCS_LOGON_NLS_TRG after logon on SYSJCS.schema
begin
dbms_session.set_nls(param => 'NLS_LANGUAGE',value => 'AMERICAN');
dbms_session.set_nls(param => 'NLS_DATE_FORMAT',value => '''DD-MM-YYYY HH24:MI:SS''');
end;

As all scheduler jobs are created in the SYSJCS schema, I only needed a logon trigger for SYSJCS. This way all SYSJCS jobs always have the one NLS setting we need without worrying about the NLS client configuration. All other schemas and users are unaffected.