Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
I don't know why that won't work, but relying on a specific NLS setting (and implict data conversion) is a bad habit to start with. You should write your SQL statements so that they are independent of any locale settings. –  a_horse_with_no_name Jun 10 '12 at 10:57
2  
All the cool kids use TO_DATE() in their queries. –  Phil Jun 10 '12 at 12:39
    
I use to_date for all of my queries - but django does not (at least not in the version we're running). I will be writing patches and making it work properly after todays debacle though. FYI, django is supposed to set the session up when the cursor opens, but like noted, it does not work. –  Josh Smeaton Jun 10 '12 at 14:29
add comment

2 Answers 2

A search of 'My Oracle Support' (MOS) showed that this is a bug reported in at least versions 11.1.0.6 and 11.1.0.7. One of the bug reports, #6747927, seems to indicate it was fixed in 11.2 although judging by one of your tags, [oracle-11g-r2], that may not be the case. I suggest you open a ticket with MOS.

share|improve this answer
    
Version we're running is 11.2.0.3.0. I'll mention this to our providers, thanks. –  Josh Smeaton Jun 10 '12 at 14:32
add comment

Changing the order of the SET seemed to make a difference, as it is working now.

create or replace
TRIGGER schema.django_nls_tr
AFTER logon ON DATABASE
BEGIN
execute immediate 'ALTER SESSION SET NLS_TERRITORY = ''AMERICA'' NLS_DATE_FORMAT = ''YYYY-MM-DD HH24:MI:SS'' NLS_TIMESTAMP_FORMAT = ''YYYY-MM-DD HH24:MI:SS.FF''';
END;

I don't really like this solution, as I still don't understand exactly why changing the order should matter. Perhaps the TERRITORY isn't being set correctly, but it was already the default so we're not TOO worried right now.

Also, django does do a TO_TIMESTAMP. It expects most date fields to be TIMESTAMP(6) but we had it pointed at a legacy db with DATE type fields. The NLS shouldn't matter too dramatically in standard deployments.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.