Change NLS_DATE_FORMAT via logon triggers

NLS_DATE_FORMAT is a parameter heavily dependent on a session. A good practice is to write a logon trigger depending on the application requirement to set this parameter write at the session logon. It can be done via a small script for a logon trigger.

[sourcecode language=”sql”]

CREATE OR REPLACE TRIGGER SCOTT.CHANGE_DATE_FORMAT
AFTER LOGON ON DATABASE
WHEN (
USER=’SCOTT’
)
begin
execute immediate ‘alter session set nls_date_format = ”DD/MM/YYYY HH24:MI:SS” ‘;
end ;
/

[/sourcecode]

Leave a Reply