We have a trigger setup so that when a specific user logs in, their NLS session parameters are modified. This used to work on Oracle 10g. We've just migrated to Oracle 11g RAC, and the session parameters are no longer persisting. To explain, I've pasted a session that shows the NLS_DATE_FORMAT not being used properly.
This is the trigger we're using:
create or replace
TRIGGER schmea.nls_tr
AFTER logon ON DATABASE
BEGIN
execute immediate 'ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY-MM-DD HH24:MI:SS'' NLS_TIMESTAMP_FORMAT = ''YYYY-MM-DD HH24:MI:SS.FF'' NLS_TERRITORY = ''AMERICA''';
END;
The formats above are not default, so they appear to change on login.
SQL> select * from nls_session_parameters where parameter = 'NLS_TIMESTAMP_FORMAT' or parameter = 'NLS_DATE_FORMAT';
PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_DATE_FORMAT
YYYY-MM-DD HH24:MI:SS
NLS_TIMESTAMP_FORMAT
YYYY-MM-DD HH24:MI:SS.FF
SQL> select count(*) from TABLE where start_date > '2012-06-10 00:00:00';
select count(*) from TABLE where start_date > '2012-06-10 00:00:00'
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P024, instance
[domain.com]:[instance] (1)
ORA-01861: literal does not match format string
SQL> alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
Session altered.
SQL> select count(*) from TABLE where start_date > '2012-06-10 00:00:00';
COUNT(*)
----------
4901
SQL> select * from nls_session_parameters where parameter = 'NLS_TIMESTAMP_FORMAT' or parameter = 'NLS_DATE_FORMAT';
PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_DATE_FORMAT
YYYY-MM-DD HH24:MI:SS
NLS_TIMESTAMP_FORMAT
YYYY-MM-DD HH24:MI:SS.FF
Please help. I've been tearing my hair out for 13 hours, 7 minutes and 4 seconds. Any ideas?
Thank you.
TO_DATE()
in their queries.