"Alter session" by logon trigger on an Scheduled Job

user_4937873 0
Viewed 589

All of my oracle procedures and functions are developed in a way that requires a specific set in nls_territory parameter. Due to a server migration, and in order to NOT alter my procedures and functions, i need to set the nls_territory at session level. But some of my procedures run under Oracle Scheduler. To avoid enter in all objects a command "alter session ..." I need to know if i create a "logon trigger" to all users would affect the objects running under the Oracle Scheduler.

Initiating the procedures / functions with "execute immediate 'alter session...'" does work, but i need an alternative to alter all programs.

EDIT: I think my question was not clear enough. If i create a logon trigger that alters nls_territory, does it will automatically affect those scheduled jobs that work in repeated intevals?

oracle-apex scheduler oracle
2 Answers

TL;DR: 

DBMS_SCHEDULER.SET_ATTRIBUTE('j', 'NLS_ENV', 'NLS_TERRITORY=''AMERICA'' ');

Interesting. You can set the NLS_ENV for each job, but it seems to be overriden by a logon trigger. An example table and procedure:

CREATE TABLE t (d DATE, nls VARCHAR2(100));

CREATE OR REPLACE PROCEDURE p AS 
  v VARCHAR2(100);
BEGIN
  SELECT value INTO v
    FROM V$NLS_PARAMETERS 
   WHERE parameter='NLS_TERRITORY';

   INSERT INTO t(d, nls) VALUES (SYSDATE, v);
END;
/

ALTER SESSION SET NLS_TERRITORY='AMERICA';
EXEC p;
SELECT * FROM t;
20-MAY-20   AMERICA

When you create a job, IO believe the active NLS settings are captured in the job table:

ALTER SESSION SET NLS_TERRITORY='ITALY';

BEGIN
   DBMS_SCHEDULER.create_job(
     job_name        => 'j', 
     job_type        => 'STORED_PROCEDURE',
     job_action      => 'P',
     start_date      =>  SYSTIMESTAMP,
     repeat_interval => 'FREQ=MINUTELY',
     enabled => TRUE); 
END;
/

SELECT job_name, nls_env FROM USER_SCHEDULER_JOBS;
J  ITALY

SELECT * FROM t ORDER BY d;

20-MAY-20   AMERICA
20-MAY-20   FRANCE
20-MAY-20   ITALY
20-MAY-20   ITALY

You can change this setting:

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE('j', 'NLS_ENV', 'NLS_TERRITORY=''GREECE'' ');
END;
/

SELECT * FROM t ORDER BY d;

20-MAY-20   AMERICA
20-MAY-20   FRANCE
20-MAY-20   ITALY
20-MAY-20   ITALY
20-MAY-20   GREECE
20-MAY-20   GREECE

But this setting gets apparently overriden by a logon trigger, which needs to be created by SYS:

CREATE OR REPLACE TRIGGER sys.logon_trigger 
  AFTER LOGON ON PLUGGABLE DATABASE
BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TERRITORY=''FINLAND'' ';
END; 
/

SELECT * FROM t ORDER BY d;

20-MAY-20   AMERICA
20-MAY-20   FRANCE
20-MAY-20   ITALY
20-MAY-20   ITALY
20-MAY-20   GREECE
20-MAY-20   GREECE
20-MAY-20   FINLAND
20-MAY-20   FINLAND
user_18640290

While this is not an answer to your question, I feel somewhat obliged to point out how to avoid this issue in the first place. I put it in a separate answer to see which answer is more helpful to the stackoverflow community...

So, logon triggers work, but I find them just horrible. Everybody in the database will have to work with the language, dateformat, decimal point v comma etc that the application needs. And just imagine the poor people who have to trace a bug in 5 years time. I wonder how long it will take them to find the logon trigger...

The proper way to solve the localisation problem is to specify it in the code right next to the function you are using, wherever you must hardcode a locale dependent value:

SELECT created, 
       TO_CHAR(created,'DAY'),
       TO_CHAR(created,'DAY', 'NLS_DATE_LANGUAGE = FRENCH')
  FROM all_objects;

2017-01-26 13:52  THURSDAY   JEUDI

Now it's safe to write queries like

SELECT created
  FROM all_objects
 WHERE TO_CHAR(created,'DAY', 'NLS_DATE_LANGUAGE = FRENCH') = 'JEUDI';

It's a bit more to type, but you're code is stable, can be sold to other countries, and doesn't need a logon trigger...

The same is true for date literals. I see this just soo often, that people write adhoc queries like

SELECT * FROM mytable WHERE mydate > '21.5.20';

We have now to use jump servers at work, and guess what, the default date format there is different. Personally, I use the ANSI date format, which has to use the format YYYY-MM-DD:

SELECT * FROM mytable WHERE mydate > DATE '2020-05-21';
user_18640290
Related