Django - Oracle backend error

Question

I have the following model in Django:

class Event(models.Model):
    # some fields
    start_date = models.DateField()
    end_date = models.DateField()

I'm using Oracle 10g Database with Django 1.5 and cx_oracle 5.1.2. The issue here is when I try to create a new object in the admin interface (picking dates from the calendar), the following error is raised:

ORA-01843: not a valid month

syncdb has created a DATE field in oracle for start_date and end_date. Does this look like a backend bug or am I doing something wrong?

I do have other models with DateTimeField() and they work fine when I persist new objects, the issue looks related to DateField itself.

UPDATE: I have checked the backend implementation, and in backends/oracle/base.py lines 513 to 516:

cursor.execute(
    "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'"
    " NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF'"
    + (" TIME_ZONE = 'UTC'" if settings.USE_TZ else ''))

Executing this statement allows an insert statement to have literal values for DATE fields. I have checked the query generated by the backend and it is inserting '2013-03-20' in start_date and end_date. The date matches NLS_DATE_FORMAT, so this in theory should work!

UPDATE: I believe my case is related to cx_oracle.

UPDATE: Since I still don't have a definite answer (although I'm almost sure it's cx_oracle that's causing this issue), I changed my DateField into a DateTimeField which translates into oracle's TIMESTAMP and works perfectly fine.

Answer 1

Based on jtiai problem description, I made following workaround - before calling any problematic sql-s (e.g. oracle 10.5.0.2 and 11.2.0.1, cx_oracle 5.1.2), reset NLS_DATE_FORMAT/NLS_TIMESTAMP_FORMAT again - done in django/db/backends/oracle/base.py in method def execute(...):

--- base.py 2013-10-31 12:19:24.000000000 +0100
+++ base_new.py 2013-10-31 12:20:32.000000000 +0100
@@ -707,6 +707,18 @@
         query = convert_unicode(query % tuple(args), self.charset)
         self._guess_input_sizes([params])
         try:
+            # BUG-WORKAROUND: ulr1-131031 
+            # https://stackoverflow.com/a/17269719/565525
+            # It's actually a bug in the Oracle 10.5.0.2 and 11.2.0.1. Bug can be reproduced as following:
+            #     - set NLS_TIMESTAMP_FORMAT in session.
+            #     - Run any implicit or explicit TO_DATE conversion with unicode data.
+            #     - **Next implicit or explicit TO_TIMESTAMP with unicode data will trigger internal reset of timestamp format.**
+            #     - All consecutive TO_TIMESTAMP will fail and TO_CHAR of timestamp will produce invalid output.
+            self.cursor.execute(
+                "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'"
+                " NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF'"
+                + (" TIME_ZONE = 'UTC'" if settings.USE_TZ else ''))
+
             return self.cursor.execute(query, self._param_generator(params))
         except Database.IntegrityError as e:
             six.reraise(utils.IntegrityError, utils.IntegrityError(*tuple(e.args)), sys.exc_info()[2])