Refs #25002 -- Supported textual to temporal column alteration on Oracle.

Thanks to Tim Graham for the report and Shai Berger for the review.

1 files changed, 27 insertions, 12 deletions

diff --git a/django/db/backends/oracle/schema.py b/django/db/backends/oracle/schema.py
index e4b02fcc7b..a10bb08e17 100644
--- a/django/db/backends/oracle/schema.py
+++ b/django/db/backends/oracle/schema.py
@@ -1,6 +1,7 @@
 import binascii
 import copy
 import datetime
+import re
 
 from django.db.backends.base.schema import BaseDatabaseSchemaEditor
 from django.db.utils import DatabaseError
@@ -49,44 +50,58 @@ class DatabaseSchemaEditor(BaseDatabaseSchemaEditor):
 
     def alter_field(self, model, old_field, new_field, strict=False):
         try:
-            # Run superclass action
             super(DatabaseSchemaEditor, self).alter_field(model, old_field, new_field, strict)
         except DatabaseError as e:
             description = str(e)
-            # If we're changing to/from LOB fields, we need to do a
+            # If we're changing type to an unsupported type we need a
             # SQLite-ish workaround
             if 'ORA-22858' in description or 'ORA-22859' in description:
-                self._alter_field_lob_workaround(model, old_field, new_field)
+                self._alter_field_type_workaround(model, old_field, new_field)
             else:
                 raise
 
-    def _alter_field_lob_workaround(self, model, old_field, new_field):
+    def _alter_field_type_workaround(self, model, old_field, new_field):
         """
-        Oracle refuses to change a column type from/to LOB to/from a regular
-        column. In Django, this shows up when the field is changed from/to
-        a TextField.
+        Oracle refuses to change from some type to other type.
         What we need to do instead is:
-        - Add the desired field with a temporary name
+        - Add a nullable version of the desired field with a temporary name
         - Update the table to transfer values from old to new
         - Drop old column
-        - Rename the new column
+        - Rename the new column and possibly drop the nullable property
         """
         # Make a new field that's like the new one but with a temporary
         # column name.
         new_temp_field = copy.deepcopy(new_field)
+        new_temp_field.null = True
         new_temp_field.column = self._generate_temp_name(new_field.column)
         # Add it
         self.add_field(model, new_temp_field)
+        # Explicit data type conversion
+        # https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements002.htm#sthref340
+        new_value = self.quote_name(old_field.column)
+        old_type = old_field.db_type(self.connection)
+        if re.match('^N?CLOB', old_type):
+            new_value = "TO_CHAR(%s)" % new_value
+            old_type = 'VARCHAR2'
+        if re.match('^N?VARCHAR2', old_type):
+            new_internal_type = new_field.get_internal_type()
+            if new_internal_type == 'DateField':
+                new_value = "TO_DATE(%s, 'YYYY-MM-DD')" % new_value
+            elif new_internal_type == 'DateTimeField':
+                new_value = "TO_TIMESTAMP(%s, 'YYYY-MM-DD HH24:MI:SS.FF')" % new_value
+            elif new_internal_type == 'TimeField':
+                # TimeField are stored as TIMESTAMP with a 1900-01-01 date part.
+                new_value = "TO_TIMESTAMP(CONCAT('1900-01-01 ', %s), 'YYYY-MM-DD HH24:MI:SS.FF')" % new_value
         # Transfer values across
         self.execute("UPDATE %s set %s=%s" % (
             self.quote_name(model._meta.db_table),
             self.quote_name(new_temp_field.column),
-            self.quote_name(old_field.column),
+            new_value,
         ))
         # Drop the old field
         self.remove_field(model, old_field)
-        # Rename the new field
-        self.alter_field(model, new_temp_field, new_field)
+        # Rename and possibly make the new field NOT NULL
+        super(DatabaseSchemaEditor, self).alter_field(model, new_temp_field, new_field)
 
     def normalize_name(self, name):
         """