Geek asks and answers

Django - Oracle Database Error

I have the following model in Django:

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

I am using Oracle 10g Database with Django 1.5 and cx_oracle 5.1.2. The problem here is that when I try to create a new object in the admin interface (selecting a date from the calendar), the following error occurs:

ORA-01843: not a valid month

syncdbcreated a field DATEin oracle for start_dateand end_date. Is this like a bug in the backend or am I doing something wrong?

I have other models with DateTimeField(), and they work fine when I save new objects, the problem is related to myself DateField.

UPDATE: I checked the backend implementation on backends/oracle/base.pylines 513 through 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 ''))

insert DATE. , , '2013-03-20' start_date end_date. NLS_DATE_FORMAT, !

. , cx_oracle.

. ( , cx_oracle, ), DateField DateTimeField, TIMESTAMP .

+5
2

jtiai , - - sql- s (, oracle 10.5.0.2 11.2.0.1, cx_oracle 5.1.2), reset NLS_DATE_FORMAT/NLS_TIMESTAMP_FORMAT - django/db/backends/oracle/base.py 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 
+            # /questions/1150015/cxoracle-ora-01843-not-a-valid-month-with-unicode-parameter/3802935#3802935
+            # It 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])
+1

, . Oracle .

1. MONTH MON. :

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


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

2 - , to_date function .

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

More articles:


All Articles