- The Oracle dialect will issue VARCHAR type definitions

using character counts, i.e. VARCHAR2(50 CHAR), so that
the column is sized in terms of characters and not bytes.
Column reflection of character types will also use
ALL_TAB_COLUMNS.CHAR_LENGTH instead of
ALL_TAB_COLUMNS.DATA_LENGTH.  Both of these behaviors take
effect when the server version is 9 or higher - for
version 8, the old behaviors are used.  [ticket:1744]

4 files changed, 68 insertions, 7 deletions

diff --git a/CHANGES b/CHANGES
index a14e39dbd..110d4a601 100644
--- a/CHANGES
+++ b/CHANGES
@@ -72,6 +72,16 @@ CHANGES
      "sqlalchemy.dialects.postgresql" logger name. 
      [ticket:877]
 
+- oracle
+   - The Oracle dialect will issue VARCHAR type definitions
+     using character counts, i.e. VARCHAR2(50 CHAR), so that
+     the column is sized in terms of characters and not bytes.
+     Column reflection of character types will also use
+     ALL_TAB_COLUMNS.CHAR_LENGTH instead of 
+     ALL_TAB_COLUMNS.DATA_LENGTH.  Both of these behaviors take
+     effect when the server version is 9 or higher - for 
+     version 8, the old behaviors are used.  [ticket:1744]
+
 - declarative
    - Using a mixin won't break if the mixin implements an 
      unpredictable __getattribute__(), i.e. Zope interfaces.
diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index 2af5bdd7d..332fa805d 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -286,7 +286,10 @@ class OracleTypeCompiler(compiler.GenericTypeCompiler):
             return "%(name)s(%(precision)s, %(scale)s)" % {'name':name,'precision': precision, 'scale' : scale}
         
     def visit_VARCHAR(self, type_):
-        return "VARCHAR(%(length)s)" % {'length' : type_.length}
+        if self.dialect.supports_char_length:
+            return "VARCHAR(%(length)s CHAR)" % {'length' : type_.length}
+        else:
+            return "VARCHAR(%(length)s)" % {'length' : type_.length}
 
     def visit_NVARCHAR(self, type_):
         return "NVARCHAR2(%(length)s)" % {'length' : type_.length}
@@ -569,7 +572,8 @@ class OracleDialect(default.DefaultDialect):
     execution_ctx_cls = OracleExecutionContext
     
     reflection_options = ('oracle_resolve_synonyms', )
-    
+
+    supports_char_length = True    
     
     def __init__(self, 
                 use_ansi=True, 
@@ -584,6 +588,8 @@ class OracleDialect(default.DefaultDialect):
         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,):
             self.colspecs = self.colspecs.copy()
             self.colspecs.pop(sqltypes.Interval)
@@ -749,11 +755,16 @@ class OracleDialect(default.DefaultDialect):
                                           resolve_synonyms, dblink,
                                           info_cache=info_cache)
         columns = []
+        if self.supports_char_length:
+            char_length_col = 'char_length'
+        else:
+            char_length_col = 'data_length'
+ 
         c = connection.execute(sql.text(
-                "SELECT column_name, data_type, data_length, data_precision, data_scale, "
+                "SELECT column_name, data_type, %(char_length_col)s, data_precision, data_scale, "
                 "nullable, data_default FROM ALL_TAB_COLUMNS%(dblink)s "
                 "WHERE table_name = :table_name AND owner = :owner " 
-                "ORDER BY column_id" % {'dblink': dblink}),
+                "ORDER BY column_id" % {'dblink': dblink, 'char_length_col':char_length_col}),
                                table_name=table_name, owner=schema)
 
         for row in c:
@@ -762,7 +773,7 @@ class OracleDialect(default.DefaultDialect):
 
             if coltype == 'NUMBER' :
                 coltype = NUMBER(precision, scale)
-            elif coltype=='CHAR' or coltype=='VARCHAR2':
+            elif coltype in ('VARCHAR2', 'NVARCHAR2', 'CHAR'):
                 coltype = self.ischema_names.get(coltype)(length)
             elif 'WITH TIME ZONE' in coltype: 
                 coltype = TIMESTAMP(timezone=True)
diff --git a/test/dialect/test_oracle.py b/test/dialect/test_oracle.py
index 566a182ed..bcb34b05c 100644
--- a/test/dialect/test_oracle.py
+++ b/test/dialect/test_oracle.py
@@ -735,7 +735,47 @@ class TypesTest(TestBase, AssertsCompiledSQL):
             assert isinstance(res, unicode)
         finally:
             metadata.drop_all()
-        
+       
+    def test_char_length(self):
+        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
+        )
+
+        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)),
+              Column("c2", NVARCHAR(250)),
+              Column("c3", CHAR(200))
+        )
+        t1.create()
+        try:
+            m2 = MetaData(testing.db)
+            t2 = Table('t1', m2, autoload=True)
+            eq_(t2.c.c1.type.length, 50)
+            eq_(t2.c.c2.type.length, 250)
+            eq_(t2.c.c3.type.length, 200)
+        finally:
+            t1.drop()
+
+
+ 
     def test_longstring(self):
         metadata = MetaData(testing.db)
         testing.db.execute("""
diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index f567d4a5a..fb32c29bb 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -1673,7 +1673,7 @@ sq.myothertable_othername AS sq_myothertable_othername FROM (" + sqstring + ") A
         check_results(postgresql.dialect(), ['NUMERIC', 'NUMERIC(12, 9)', 'DATE', 'TEXT', 'VARCHAR(20)'], '%(param_1)s')
 
         # then the Oracle engine
-        check_results(oracle.dialect(), ['NUMERIC', 'NUMERIC(12, 9)', 'DATE', 'CLOB', 'VARCHAR(20)'], ':param_1')
+        check_results(oracle.dialect(), ['NUMERIC', 'NUMERIC(12, 9)', 'DATE', 'CLOB', 'VARCHAR(20 CHAR)'], ':param_1')
 
         # then the sqlite engine
         check_results(sqlite.dialect(), ['NUMERIC', 'NUMERIC(12, 9)', 'DATE', 'TEXT', 'VARCHAR(20)'], '?')