Commits

Mike Bayer  committed dfca505

- Re-established support for Oracle 8 with cx_oracle,
including that use_ansi is set to False automatically,
NVARCHAR2 and NCLOB are not rendered for Unicode,
"native unicode" check doesn't fail, cx_oracle
"native unicode" mode is disabled, VARCHAR() is emitted
with bytes count instead of char count. [ticket:1808]

  • Participants
  • Parent commits a84fef1

Comments (0)

Files changed (7)

 
   - Fixed use_ansi=False mode, which was producing broken 
     WHERE clauses in pretty much all cases.  [ticket:1790]
+
+  - Re-established support for Oracle 8 with cx_oracle, 
+    including that use_ansi is set to False automatically, 
+    NVARCHAR2 and NCLOB are not rendered for Unicode, 
+    "native unicode" check doesn't fail, cx_oracle 
+    "native unicode" mode is disabled, VARCHAR() is emitted 
+    with bytes count instead of char count. [ticket:1808]
     
   - oracle_xe 5 doesn't accept a Python unicode object in 
     its connect string in normal Python 2.x mode - so we coerce

File doc/build/reference/dialects/oracle.rst

 
 .. automodule:: sqlalchemy.dialects.oracle.base
 
+Oracle Column Types
+-------------------
+
+In addition to those types at :ref:`types`, datatypes specific to the
+Oracle dialect include those listed here.
+
+.. automodule:: sqlalchemy.dialects.oracle
+
+
+.. autoclass:: BFILE
+  :members: __init__
+  :show-inheritance:
+
+.. autoclass:: DOUBLE_PRECISION
+   :members: __init__
+   :show-inheritance:
+
+.. autoclass:: INTERVAL
+  :members: __init__
+  :show-inheritance:
+
+.. autoclass:: NCLOB
+  :members: __init__
+  :show-inheritance:
+
+.. autoclass:: NUMBER
+   :members: __init__
+   :show-inheritance:
+
+.. autoclass:: LONG
+  :members: __init__
+  :show-inheritance:
+
+.. autoclass:: RAW
+  :members: __init__
+  :show-inheritance:
+
 cx_Oracle Notes
 ---------------
 

File doc/build/reference/sqlalchemy/types.rst

 .. autoclass:: NCHAR
   :show-inheritance:
 
+.. autoclass:: NVARCHAR
+  :show-inheritance:
+
 .. autoclass:: NUMERIC
   :show-inheritance:
 

File lib/sqlalchemy/dialects/oracle/base.py

 Oracle 8 Compatibility
 ----------------------
 
-When using Oracle 8, a "use_ansi=False" flag is available which converts all
-JOIN phrases into the WHERE clause, and in the case of LEFT OUTER JOIN
-makes use of Oracle's (+) operator.
+When Oracle 8 is detected, the dialect internally configures itself to the following
+behaviors:
+
+* the use_ansi flag is set to False.  This has the effect of converting all
+  JOIN phrases into the WHERE clause, and in the case of LEFT OUTER JOIN
+  makes use of Oracle's (+) operator.
+
+* the NVARCHAR2 and NCLOB datatypes are no longer generated as DDL when 
+  the :class:`~sqlalchemy.types.Unicode` is used - VARCHAR2 and CLOB are issued 
+  instead.   This because these types don't seem to work correctly on Oracle 8
+  even though they are available.  The :class:`~sqlalchemy.types.NVARCHAR` 
+  and :class:`~sqlalchemy.dialects.oracle.NCLOB` types will always generate NVARCHAR2 and NCLOB.
+
+* the "native unicode" mode is disabled when using cx_oracle, i.e. SQLAlchemy 
+  encodes all Python unicode objects to "string" before passing in as bind parameters.
 
 Synonym/DBLINK Reflection
 -------------------------
         within available DBAPIs (cx_oracle and zxjdbc).
         
         :param day_precision: the day precision value.  this is the number of digits
-        to store for the day field.  Defaults to "2"
+          to store for the day field.  Defaults to "2"
         :param second_precision: the second precision value.  this is the number of digits
-        to store for the fractional seconds field.  Defaults to "6".
+          to store for the fractional seconds field.  Defaults to "6".
         
         """
         self.day_precision = day_precision
         return self.visit_FLOAT(type_)
         
     def visit_unicode(self, type_):
-        return self.visit_NVARCHAR(type_)
+        if self.dialect._supports_nchar:
+            return self.visit_NVARCHAR(type_)
+        else:
+            return self.visit_VARCHAR(type_)
     
     def visit_INTERVAL(self, type_):
         return "INTERVAL DAY%s TO SECOND%s" % (
             return "%(name)s(%(precision)s, %(scale)s)" % {'name':name,'precision': precision, 'scale' : scale}
         
     def visit_VARCHAR(self, type_):
-        if self.dialect.supports_char_length:
+        if self.dialect._supports_char_length:
             return "VARCHAR(%(length)s CHAR)" % {'length' : type_.length}
         else:
             return "VARCHAR(%(length)s)" % {'length' : type_.length}
         return self.visit_CLOB(type_)
 
     def visit_unicode_text(self, type_):
-        return self.visit_NCLOB(type_)
+        if self.dialect._supports_nchar:
+            return self.visit_NCLOB(type_)
+        else:
+            return self.visit_CLOB(type_)
 
     def visit_large_binary(self, type_):
         return self.visit_BLOB(type_)
     
     reflection_options = ('oracle_resolve_synonyms', )
 
-    supports_char_length = True    
-    
     def __init__(self, 
                 use_ansi=True, 
                 optimize_limits=False, 
         self.implicit_returning = self.server_version_info > (10, ) and \
                                         self.__dict__.get('implicit_returning', True)
 
-        self.supports_char_length = self.server_version_info >= (9, )
-
-        if self.server_version_info < (9,):
+        if self._is_oracle_8:
             self.colspecs = self.colspecs.copy()
             self.colspecs.pop(sqltypes.Interval)
+            self.use_ansi = False
 
+    @property
+    def _is_oracle_8(self):
+        return self.server_version_info and \
+                    self.server_version_info < (9, )
+        
+    @util.memoized_property
+    def _supports_char_length(self):
+        return not self._is_oracle_8
+
+    @util.memoized_property
+    def _supports_nchar(self):
+        return not self._is_oracle_8
+        
     def do_release_savepoint(self, connection, name):
         # Oracle does not support RELEASE SAVEPOINT
         pass
                                           resolve_synonyms, dblink,
                                           info_cache=info_cache)
         columns = []
-        if self.supports_char_length:
+        if self._supports_char_length:
             char_length_col = 'char_length'
         else:
             char_length_col = 'data_length'

File lib/sqlalchemy/dialects/oracle/cx_oracle.py

 all unicode strings directly to cx_oracle, and additionally uses an output
 handler so that all string based result values are returned as unicode as well.
 
+Note that this behavior is disabled when Oracle 8 is detected, as it has been 
+observed that issues remain when passing Python unicodes to cx_oracle with Oracle 8.
+
 LOB Objects
 -----------
 
                 self.dbapi.BLOB: oracle.BLOB(),
                 self.dbapi.BINARY: oracle.RAW(),
             }
+
+    def initialize(self, connection):
+        super(OracleDialect_cx_oracle, self).initialize(connection)
+        if self._is_oracle_8:
+            self.supports_unicode_binds = False
     
     @classmethod
     def dbapi(cls):

File test/dialect/test_oracle.py

             except_(t1.select(), t2.select()),
             "SELECT t1.c1, t1.c2, t1.c3 FROM t1 MINUS SELECT t2.c1, t2.c2, t2.c3 FROM t2"
         )
-        
+
+class CompatFlagsTest(TestBase, AssertsCompiledSQL):
+    __only_on__ = 'oracle'
+    
+    def test_ora8_flags(self):
+        def server_version_info(self):
+            return (8, 2, 5)
+            
+        dialect = oracle.dialect()
+        dialect._get_server_version_info = server_version_info
+        dialect.initialize(testing.db.connect())
+        assert not dialect._supports_char_length
+        assert not dialect._supports_nchar
+        assert not dialect.use_ansi
+        self.assert_compile(String(50),"VARCHAR(50)",dialect=dialect)
+        self.assert_compile(Unicode(50),"VARCHAR(50)",dialect=dialect)
+        self.assert_compile(UnicodeText(),"CLOB",dialect=dialect)
+
+    def test_default_flags(self):
+        """test with no initialization or server version info"""
+        dialect = oracle.dialect()
+        assert dialect._supports_char_length
+        assert dialect._supports_nchar
+        assert dialect.use_ansi
+        self.assert_compile(String(50),"VARCHAR(50 CHAR)",dialect=dialect)
+        self.assert_compile(Unicode(50),"NVARCHAR2(50)",dialect=dialect)
+        self.assert_compile(UnicodeText(),"NCLOB",dialect=dialect)
+    
+    def test_ora10_flags(self):
+        def server_version_info(self):
+            return (10, 2, 5)
+        dialect = oracle.dialect()
+        dialect._get_server_version_info = server_version_info
+        dialect.initialize(testing.db.connect())
+        assert dialect._supports_char_length
+        assert dialect._supports_nchar
+        assert dialect.use_ansi
+        self.assert_compile(String(50),"VARCHAR(50 CHAR)",dialect=dialect)
+        self.assert_compile(Unicode(50),"NVARCHAR2(50)",dialect=dialect)
+        self.assert_compile(UnicodeText(),"NCLOB",dialect=dialect)
+    
 class MultiSchemaTest(TestBase, AssertsCompiledSQL):
     __only_on__ = 'oracle'
     
             metadata.drop_all()
        
     def test_char_length(self):
-        self.assert_compile(
-            VARCHAR(50),
-            "VARCHAR(50 CHAR)",
-        )
+        self.assert_compile(VARCHAR(50),"VARCHAR(50 CHAR)")
 
         oracle8dialect = oracle.dialect()
-        oracle8dialect.supports_char_length = False
-        self.assert_compile(
-            VARCHAR(50),
-            "VARCHAR(50)",
-            dialect=oracle8dialect
-        )
+        oracle8dialect._supports_char_length = False
+        self.assert_compile(VARCHAR(50),"VARCHAR(50)",dialect=oracle8dialect)
 
-        self.assert_compile(
-            NVARCHAR(50),
-            "NVARCHAR2(50)",
-        )
-        self.assert_compile(
-            CHAR(50),
-            "CHAR(50)",
-        )
+        self.assert_compile(NVARCHAR(50),"NVARCHAR2(50)")
+        self.assert_compile(CHAR(50),"CHAR(50)")
+        
         metadata = MetaData(testing.db)
         t1 = Table('t1', metadata,
               Column("c1", VARCHAR(50)),
             eq_(t2.c.c3.type.length, 200)
         finally:
             t1.drop()
-
-
  
     def test_longstring(self):
         metadata = MetaData(testing.db)

File test/sql/test_types.py

             eq_(testobj2, l[1]['pickled'])
             eq_(testobj3.moredata, l[0]['mypickle'].moredata)
             eq_(l[0]['mypickle'].stuff, 'this is the right stuff')
-    
+
+    @testing.fails_on('oracle+cx_oracle', 'oracle fairly grumpy about binary '
+                                        'data, not really known how to make this work')
     def test_comparison(self):
         """test that type coercion occurs on comparison for binary"""