NVARCHAR2 (oracle) type in not treated well

[Former user]

I am using sqlalchemy 4.7.1, with Oracle 9.

I have unicode string fields that are defined as NVARCHAR2. A. They are not recognized well. B. When I explicitly define the columns, as unicode strings, and than query those fields, I get a bunch of question marks, where there should be arabic letters.

When using JDBC to query the field, I got all the unicode characters.

[Former user]

Reported by Kobi Perl

[Mike Bayer]

I'm assuming you are using reflection (please attach full test scripts to each ticket).

possible patch. I will not have time to look at this for at least a week, if you feel like testing.

Index: lib/sqlalchemy/databases/oracle.py
===================================================================
--- lib/sqlalchemy/databases/oracle.py  (revision 5330)
+++ lib/sqlalchemy/databases/oracle.py  (working copy)
@@ -193,6 +193,10 @@
     def get_col_spec(self):
         return "VARCHAR(%(length)s)" % {'length' : self.length}

+class OracleUnicode(sqltypes.Unicode):
+    def get_col_spec(self):
+        return "NVARCHAR(%(length)s)" % {'length' : self.length}
+
 class OracleText(sqltypes.Text):
     def get_dbapi_type(self, dbapi):
         return dbapi.CLOB
@@ -288,6 +292,7 @@

 ischema_names = {
     'VARCHAR2' : OracleString,
+    'NVARCHAR2' : OracleUnicode,
     'CHAR' : OracleString,
     'DATE' : OracleDateTime,
     'DATETIME' : OracleDateTime,

[Former user]

I'll check your solution, and bring some code examples tomorrow (I'm not at work right now)

[Mike Bayer]

Note that most of our dialects don't provide reflection which turns on "convert_unicode=True" on the reflected type automatically. The usual route when unicode data is stored in VARCHAR columns is to turn on convert_unicode=True at the engine level, so that reflected VARCHARs don't need a column level setting. That would be a workaround for this specific issue.

[Mike Bayer]

8598780e8d746a85897ab19bd514c9980bfe446e. This is a behavioral change - applications that have been reflecting NVARCHAR2 columns and assuming string behavior will now raise warnings when non-unicode objects are passed.

[Mike Bayer]

Removing milestone: 0.5.0 (automated comment)