#18465: Date and Timestamp formats for Oracle Backend incorrect with Oracle RAC
11g
R2
-------------------------------------+-------------------------------------
Reporter: josh.smeaton@… | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.4
(models, ORM) | Keywords: oracle backend NLS Date
Severity: Normal | Format
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
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
[http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch3globenv.htm#autoId14
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 >
--
Ticket URL: <https://code.djangoproject.com/ticket/18465>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com.
To unsubscribe from this group, send email to
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en.
