id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
18465	Date and Timestamp formats for Oracle Backend incorrect with Oracle RAC 11g R2	josh.smeaton@…	Anssi Kääriäinen	"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 >"	Bug	closed	Database layer (models, ORM)	1.4	Normal	fixed	oracle backend NLS Date Format		Accepted	1	0	0	0	0	0