Date and Timestamp formats for Oracle Backend incorrect with Oracle RAC 11g R2
|Reported by:||Owned by:||Anssi Kääriäinen|
|Component:||Database layer (models, ORM)||Version:||1.4|
|Severity:||Normal||Keywords:||oracle backend NLS Date Format|
|Has patch:||yes||Needs documentation:||no|
|Needs tests:||no||Patch needs improvement:||no|
When trying to do a datetime range query, I began receiving the below error after migrating to Oracle RAC 11g R2
DatabaseError at /url/path/ ORA-12801: error signaled in parallel query server P026, instance [domain]:[Instance] (2) ORA-01861: literal does not match format string
We determined that this was due to incorrect NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT, as manually setting those session parameters in sqlplus and executing the same query was working correctly. We found the below code which seemed to be doing the right thing, as querying the NLS_SESSION_PARAMETERS was returning the correct information.
# Set oracle date to ansi date format. This only needs to execute # once when we create a new connection. We also set the Territory # to 'AMERICA' which forces Sunday to evaluate to a '1' in TO_CHAR(). cursor.execute("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'" + (" TIME_ZONE = 'UTC'" if settings.USE_TZ else ''))
Oracle documentation here mentions that setting the NLS_TERRITORY parameter will set a number of parameters (including NLS_TIMESTAMP_FORMAT and NLS_DATE_FORMAT) to the territory default. The above SQL that alters the session information has undefined (I couldn't find it if it exists) behaviour for changing the territory and format parameters in a single statement.
The territory can be modified dynamically during the session by specifying the new NLS_TERRITORY value in an ALTER SESSION statement. Modifying NLS_TERRITORY resets all derived NLS session parameters to default values for the new territory.
The solution is to set the territory first and, as a separate statement set, the time/date formats to ensure the territory defaults are overridden correctly.
I will update this ticket with a link to the change in git.
This problem also affects at least version Django 1.2 >