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.

So just to be clear, you're checking the Oracle SYS tables for the SQL being run and seeing something like `TO_DATE('2013-03-20', 'YYYY-MM-DD')` in your insert? From your question, it looks like `NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'`, which is different from the actual insert, though I don't know what effect this would have in this context. — woemler, Mar 22 '13 at 17:58
No, because of the alter session statement setting `NLS_DATE_FORMAT`, you can insert the date as a literal without to_date, which is the case when I check the SQL being executed. — Aziz Alfoudari, Mar 22 '13 at 18:08
Can you post the relevant SQL that is being run on the server that results in the `ORA-01843` error? — woemler, Mar 22 '13 at 18:13
I don't have access to the system currently, but I have tried executing the SQL statement in an SQL client and it worked fine. I couldn't get the query from the database by the way, since it raised an error and wasn't executed I believe. I printed it in the console. — Aziz Alfoudari, Mar 22 '13 at 18:21
This might be useful in diagnosing your problem: http://stackoverflow.com/questions/3468216/oracle-is-there-a-way-to-get-recent-sql-syntax-errors — woemler, Mar 22 '13 at 18:42
What is the `cursor`? Is that a connection object or a recordset object? — Rachcha, Mar 22 '13 at 19:39
Models.DateField() uses datetime.date [https://docs.djangoproject.com/en/dev/ref/models/fields/#django.db.models.DateField] In base.py lines, 182-191, it mentions how cx_oracle always returns datetime.datetime so it does some conversion. To confirm, you checked the query by turning on verbose output? — John D, Mar 22 '13 at 21:54
@JohnD I added a print statement in base.py in line 774 to print the queries and another print statement for the list generated by `self._param_generator(params)`; I wanted to see what's being passed to the DB just before it gets executed since it's an ORA error. — Aziz Alfoudari, Mar 23 '13 at 05:35

score 1 · Answer 1 · edited May 23 '17 at 12:01

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])

score -2 · Answer 2 · answered Apr 04 '13 at 07:01

The cause of the error is you entered a date, but the month portion of the date was not a valid month. Oracle give resolutions for this problem.

1 - Re-enter the date value using either a MONTH or MON format mask. The valid values for month are:

January
February
March
.......
//and soon

OR

Jan
Feb
Mar
.......
//and soon

2 - if the above resolution is failed, use to_date function instead.

to_date( string1, [ format_mask ], [ nls_language ] )

Django - Oracle backend error

2 Answers2