icon-arrow icon-check icon-mail icon-phone icon-facebook icon-linkedin icon-youtube icon-twitter icon-cheveron icon-download icon-instagram play close close icon-arrow-uturn icon-calendar icon-clock icon-search icon-chevron-process icon-skills icon-knowledge icon-kite icon-education icon-languages icon-tools icon-experience icon-coffee-cup
Werken bij Integration & Application Talents
Blog 26/04/2013

Talking in Tongues – NLS with dbms_scheduler

Not understanding the other

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
dbms_session.set_nls(param => 'NLS_LANGUAGE',value => 'AMERICAN');
dbms_session.set_nls(param => 'NLS_DATE_FORMAT',value => '''DD-MM-YYYY HH24:MI:SS''');

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.

Overzicht blogs

Geen reacties

Geef jouw mening

Reactie plaatsen

Reactie toevoegen

Jouw e-mailadres wordt niet openbaar gemaakt.


  • Geen HTML toegestaan.
  • Regels en alinea's worden automatisch gesplitst.
  • Web- en e-mailadressen worden automatisch naar links omgezet.

Wil je deel uitmaken van een groep gedreven en ambitieuze experts? Stuur ons jouw cv!