Follow Us on Twitter

Talking in Tongues – NLS with dbms_scheduler

by Tony van Esch on April 26, 2013 · 0 comments

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.

 

Regards,
Tony

Ratings:
VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)

Leave a Reply

Your email address will not be published. Required fields are marked *

*

* Copy This Password *

* Type Or Paste Password Here *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 

Previous post:

Next post:

About Whitehorses
Company profile
Services
Technology

Whitehorses website

Home page
Whitebooks
Jobs

Follow us
Blog post RSS
Comment RSS
Twitter