Oracle doesn't stand for unicode bind parameter names (ORA-01036: illegal variable name/number)

[Former user]

When a column name begins with an underscore, the bind parameter name itself is a unicode object which finds its way all the way to Oracle (cxOracle doesn't convert, either I guess), and Oracle complains:

This script causes ORA-01036: illegal variable name/number

from sqlalchemy import *
from sqlalchemy.orm import *

engine = create_engine('oracle://arc:arc@localhost:1521/xe',echo=True)
metadata = MetaData(engine)
session = sessionmaker(bind=engine)()

class SaClass(object):
    pass

mapper(SaClass, Table("atable", metadata, 
    Column("_underscorecolumn", Unicode(255), primary_key=True)
))
metadata.create_all()

#engine.dialect.supports_unicode_binds = False

try:
    session.query(SaClass).filter(SaClass._underscorecolumn==u'value').all()
finally:
    metadata.drop_all()

Error:

2011-03-22 14:27:52,688 INFO sqlalchemy.engine.base.Engine.0x...5490 SELECT USER FROM DUAL
2011-03-22 14:27:52,692 INFO sqlalchemy.engine.base.Engine.0x...5490 {}
2011-03-22 14:27:52,697 INFO sqlalchemy.engine.base.Engine.0x...5490 SELECT table_name FROM all_tables WHERE table_name = :name AND owner = :schema_name
2011-03-22 14:27:52,697 INFO sqlalchemy.engine.base.Engine.0x...5490 {'name': u'ATABLE', 'schema_name': u'ARC'}
2011-03-22 14:27:52,701 INFO sqlalchemy.engine.base.Engine.0x...5490
CREATE TABLE atable (
        "_underscorecolumn" NVARCHAR2(255) NOT NULL,
        PRIMARY KEY ("_underscorecolumn")
)


2011-03-22 14:27:52,702 INFO sqlalchemy.engine.base.Engine.0x...5490 {}
2011-03-22 14:27:52,757 INFO sqlalchemy.engine.base.Engine.0x...5490 COMMIT
2011-03-22 14:27:52,759 INFO sqlalchemy.engine.base.Engine.0x...5490 BEGIN
2011-03-22 14:27:52,759 INFO sqlalchemy.engine.base.Engine.0x...5490 SELECT atable."_underscorecolumn" AS atable__underscorecolumn
FROM atable
WHERE atable."_underscorecolumn" = :"_underscorecolumn_1"
2011-03-22 14:27:52,760 INFO sqlalchemy.engine.base.Engine.0x...5490 {u'"_underscorecolumn_1"': u'value'}
2011-03-22 14:27:52,801 INFO sqlalchemy.engine.base.Engine.0x...5490 SELECT table_name FROM all_tables WHERE table_name = :name AND owner = :schema_name
2011-03-22 14:27:52,801 INFO sqlalchemy.engine.base.Engine.0x...5490 {'name': u'ATABLE', 'schema_name': u'ARC'}
2011-03-22 14:27:52,815 INFO sqlalchemy.engine.base.Engine.0x...5490
DROP TABLE atable
2011-03-22 14:27:52,815 INFO sqlalchemy.engine.base.Engine.0x...5490 {}
2011-03-22 14:27:52,858 INFO sqlalchemy.engine.base.Engine.0x...5490 COMMIT
Traceback (most recent call last):
  File "unicodebinds.py", line 18, in <module>
    session.query(SaClass).filter(SaClass._underscorecolumn==u'value').all()
  File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.4.2kbdev-py2.6-linux-x86_64.egg/sqlalchemy/orm/query.py", line 1490, in all
    return list(self)
  File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.4.2kbdev-py2.6-linux-x86_64.egg/sqlalchemy/orm/query.py", line 1602, in __iter__
    return self._execute_and_instances(context)
  File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.4.2kbdev-py2.6-linux-x86_64.egg/sqlalchemy/orm/query.py", line 1607, in _execute_and_instances
    mapper=self._mapper_zero_or_none())
  File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.4.2kbdev-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py", line 701, in execute
    clause, params or {})
  File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.4.2kbdev-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1191, in execute
    params)
  File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.4.2kbdev-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1271, in _execute_clauseelement
    return self.__execute_context(context)
  File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.4.2kbdev-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1302, in __execute_context
    context.parameters[0](0), context=context)
  File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.4.2kbdev-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1401, in _cursor_execute
    context)
  File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.4.2kbdev-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1394, in _cursor_execute
    context)
  File "/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.4.2kbdev-py2.6-linux-x86_64.egg/sqlalchemy/engine/default.py", line 299, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-01036: illegal variable name/number
 'SELECT atable."_underscorecolumn" AS atable__underscorecolumn \nFROM atable \nWHERE atable."_underscorecolumn" = :"_underscorecolumn_1"' {u'"_underscorecolumn_1"': u'value'}

Note: Oracle is fine with the underscore itself, which is proven by uncommenting the line:

engine.dialect.supports_unicode_binds = False

And rerunning the script, which then works fine.

[Former user]

If you'd kindly point me in the right direction, I assume I can fix this, and if I do I'll be more happy to provide a patch.

[Former user]

P.S. Oracle version 10.2

Thanks again.

[Former user]

Replying to guest:

I'll be more happy to provide a patch.

Boy, that almost sounds rude. Replace that with "I'll be more ''than'' happy to provide a patch."

[Mike Bayer]

Can I just make the comment that using underscores in column names in general, and especially in a rigid database like Oracle, is a practice that would make any DBA cry. I'd strongly recommend using "key='_somename'", and sticking with traditional names for the columns themselves (but I know you know that already...so....OK).

As for the behavior demonstrated, its not clear yet if this is a cx_oracle or SQLA issue. It may be that the quoting of the bind name is throwing off cx_oracle, meaning its not just underscores but any character that would trigger quoting. cx_oracle has elaborate needs in the area of bind parameter naming.

[Former user]

Replying to zzzeek:

Can I just make the comment that using underscores in column names in general, and especially in a rigid database like Oracle, is a practice that would make any DBA cry. I'd strongly recommend using "key='_somename'", and sticking with traditional names for the columns themselves (but I know you know that already...so....OK).

Yeah, of course, these aren't typical columns... and there are unrelated reasons for the underscore...

As for the behavior demonstrated, its not clear yet if this is a cx_oracle or SQLA issue. It may be that the quoting of the bind name is throwing off cx_oracle, meaning its not just underscores but any character that would trigger quoting. cx_oracle has elaborate needs in the area of bind parameter naming.

Nope, it isn't the quoting of the bind name. Oracle is fine with that. Here is same script from above with this additional line:

engine.dialect.supports_unicode_binds = False



2011-03-22 15:53:53,440 INFO sqlalchemy.engine.base.Engine.0x...0490 SELECT USER FROM DUAL
2011-03-22 15:53:53,445 INFO sqlalchemy.engine.base.Engine.0x...0490 {}
2011-03-22 15:53:53,454 INFO sqlalchemy.engine.base.Engine.0x...0490 SELECT table_name FROM all_tables WHERE table_name = :name AND owner = :schema_name
2011-03-22 15:53:53,454 INFO sqlalchemy.engine.base.Engine.0x...0490 {'name': u'ATABLE', 'schema_name': u'ARC'}
2011-03-22 15:53:53,550 INFO sqlalchemy.engine.base.Engine.0x...0490
CREATE TABLE atable (
        "_underscorecolumn" NVARCHAR2(255) NOT NULL,
        PRIMARY KEY ("_underscorecolumn")
)


2011-03-22 15:53:53,551 INFO sqlalchemy.engine.base.Engine.0x...0490 {}
2011-03-22 15:53:53,647 INFO sqlalchemy.engine.base.Engine.0x...0490 COMMIT
2011-03-22 15:53:53,649 INFO sqlalchemy.engine.base.Engine.0x...0490 BEGIN
2011-03-22 15:53:53,650 INFO sqlalchemy.engine.base.Engine.0x...0490 SELECT atable."_underscorecolumn" AS atable__underscorecolumn
FROM atable
WHERE atable."_underscorecolumn" = :"_underscorecolumn_1"
2011-03-22 15:53:53,650 INFO sqlalchemy.engine.base.Engine.0x...0490 {'"_underscorecolumn_1"': 'value'}
2011-03-22 15:53:53,697 INFO sqlalchemy.engine.base.Engine.0x...0490 SELECT table_name FROM all_tables WHERE table_name = :name AND owner = :schema_name
2011-03-22 15:53:53,697 INFO sqlalchemy.engine.base.Engine.0x...0490 {'name': 'ATABLE', 'schema_name': 'ARC'}
2011-03-22 15:53:53,742 INFO sqlalchemy.engine.base.Engine.0x...0490
DROP TABLE atable
2011-03-22 15:53:53,743 INFO sqlalchemy.engine.base.Engine.0x...0490 {}
2011-03-22 15:53:54,272 INFO sqlalchemy.engine.base.Engine.0x...0490 COMMIT

The only difference is the unicode object instead of the string.

[Former user]

Replying to zzzeek:

It may be that the quoting of the bind name is throwing off cx_oracle, meaning its not just underscores but any character that would trigger quoting.

This was my first suspicion and disproved by making the column name "group" which, for Oracle, requires quoting. It worked completely fine (and the parameter name ''was'' quoted, but was ''not'' unicode).

[Mike Bayer]

there's some awkward stuff going on here, since I'm looking at unicode names in general. the patch so far is like:

diff -r 39c465ecf3f45bbebb37c5d5ed7e1a0fccf5641c lib/sqlalchemy/dialects/oracle/cx_oracle.py
--- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py   Mon Mar 21 11:49:43 2011 -0400
+++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py   Tue Mar 22 16:17:32 2011 -0400
@@ -297,7 +297,7 @@
             if not self.dialect.supports_unicode_binds:
                 quoted_bind_names = \
                                 dict(
-                                    (fromname, toname.encode(self.dialect.encoding)) 
+                                    (fromname.encode(self.dialect.encoding), toname.encode(self.dialect.encoding)) 
                                     for fromname, toname in 
                                     quoted_bind_names.items()
                                 )
@@ -483,7 +483,7 @@
         self._cx_oracle_string_types = types("STRING", "UNICODE", "NCLOB", "CLOB")
         self._cx_oracle_unicode_types = types("UNICODE", "NCLOB")
         self._cx_oracle_binary_types = types("BFILE", "CLOB", "NCLOB", "BLOB") 
-        self.supports_unicode_binds = self.cx_oracle_ver >= (5, 0)
+        self.supports_unicode_binds = False
         self.supports_native_decimal = self.cx_oracle_ver >= (5, 0)
         self._cx_oracle_native_nvarchar = self.cx_oracle_ver >= (5, 0)

diff -r 39c465ecf3f45bbebb37c5d5ed7e1a0fccf5641c lib/sqlalchemy/engine/default.py
--- a/lib/sqlalchemy/engine/default.py  Mon Mar 21 11:49:43 2011 -0400
+++ b/lib/sqlalchemy/engine/default.py  Tue Mar 22 16:17:32 2011 -0400
@@ -450,7 +450,7 @@
                         param.append(compiled_params[key](key))
                 parameters.append(dialect.execute_sequence_format(param))
         else:
-            encode = not dialect.supports_unicode_statements
+            encode = not dialect.supports_unicode_binds
             for compiled_params in self.compiled_parameters:
                 param = {}
                 if encode:
diff -r 39c465ecf3f45bbebb37c5d5ed7e1a0fccf5641c test/sql/test_unicode.py

but the change in default.py I'm not ready to do in 0.6. It might not be needed here.

[Mike Bayer]

here's the fun. unicode bind name accepted when compared to a string value, rejected when compared to an int. this one is on cx_oracle/OCI's side.

# coding: utf-8

from sqlalchemy import *
from sqlalchemy.orm import *

engine = create_engine('oracle://scott:tiger@localhost:1521/xe',echo=True)
metadata = MetaData(engine)

t1 = Table("t1", metadata, 
    Column(u'méil', Integer, primary_key=True)
)
t2 = Table("t2", metadata, 
    Column(u'méil', String(50), primary_key=True)
)

metadata.create_all()

try:
    # passes
    engine.execute(
        t2.select().where(t2.c[u'méil'](u'méil')=='value')
    )
    # fails: ORA-01036: illegal variable name/number
    engine.execute(
        t1.select().where(t1.c[u'méil'](u'méil')==5)
    )
finally:
    metadata.drop_all()

[Former user]

Doesn't this change:

self.supports_unicode_binds = False

Shut off all unicode? I thought we still want to allow unicode in the param ''value'', just not the param ''name''??

[Former user]

This solves my problem and still allows unicode bind parameter ''values'', just not the ''name'':

--- cx_oracle.orig.py   2011-03-22 16:36:36.000000000 -0400
+++ cx_oracle.py        2011-03-22 16:36:49.000000000 -0400
@@ -242,21 +242,21 @@
         if quoted_bind_names:
             if not self.dialect.supports_unicode_binds:
                 quoted_bind_names = \
                                 dict(
                                     (fromname, toname.encode(self.dialect.encoding))
                                     for fromname, toname in
                                     quoted_bind_names.items()
                                 )
             for param in self.parameters:
                 for fromname, toname in quoted_bind_names.items():
-                    param[toname](toname) = param[fromname](fromname)
+                    param[str(toname)](str(toname)) = param[fromname](fromname)
                     del param[fromname](fromname)

         if self.dialect.auto_setinputsizes:
             # cx_oracle really has issues when you setinputsizes
             # on String, including that outparams/RETURNING
             # breaks for varchars
             self.set_input_sizes(quoted_bind_names,
                                  exclude_types=self.dialect._cx_oracle_string_types
                                 )

[Former user]

2011-03-22 16:41:59,438 INFO sqlalchemy.engine.base.Engine.0x...0490 SELECT atable."_underscorecolumn" AS atable__underscorecolumn
FROM atable
WHERE atable."_underscorecolumn" = :"_underscorecolumn_1"
2011-03-22 16:41:59,438 INFO sqlalchemy.engine.base.Engine.0x...0490 {'"_underscorecolumn_1"': u'value'}

[Mike Bayer]

OK but the better fix (the one that I'd commit) is the one that does fromname.encode() up above. and from my testing, cx_oracle really does not accept a "unicode" bind name (except in "everything unicode" mode) so I think that flag can be set to false. self.supports_unicode_binds=False just establishes that the string name of a bind parameter is to be encoded, nothing else.

[Former user]

It seems Oracle is fine with a ''column name'' being unicode:

SQL> create table kent("méil" integer)
  2  ;

Table created.

But unhappy perhaps with variable names (including bind parameters) being unicode??:

SQL>
SQL> var normal varchar2(10)
SQL> var méil varchar2(10)
SP2-0553: Illegal variable name "méil".

''Admittedly, that is an sqlplus error.'' If we really wanted to support unicode column names, that is fine, but perhaps the bind parameter names can't be unicode it seems to me??

P.S. Even I am not crazy enough to name a column name with unicode characters... (I don't think.)

[Mike Bayer]

yes my test above illustrates that it sort of works but not really. the foreign characters in the schema is commonplace with sqlite and others though (databases in China, etc.). to get all of that to work would require more escaping/translation in the cx_oracle backend. its not of high priority since with Oracle nobody really uses that kind of behavior.

[Former user]

Replying to zzzeek:

OK but the better fix (the one that I'd commit) is the one that does fromname.encode() up above. and from my testing, cx_oracle really does not accept a "unicode" bind name (except in "everything unicode" mode) so I think that flag can be set to false. self.supports_unicode_binds=False just establishes that the string name of a bind parameter is to be encoded, nothing else.

The second I include the change:

-        self.supports_unicode_binds = self.cx_oracle_ver >= (5, 0)
+        self.supports_unicode_binds = False

I get bind parameters like this:

2011-03-22 16:57:48,919 INFO sqlalchemy.engine.base.Engine.0x...4490 {'"_underscorecolumn_1"': 'value'}

But I think what we want is this:

2011-03-22 16:41:59,438 INFO sqlalchemy.engine.base.Engine.0x...0490 {'"_underscorecolumn_1"': u'value'}

So, I think the self.supports_unicode_binds = False is stopping that.

[Mike Bayer]

Replying to guest:

So, I think the self.supports_unicode_binds = False is stopping that.

totally right, I had it backwards. The fix is the other way around, closing #2101:

diff -r 39c465ecf3f45bbebb37c5d5ed7e1a0fccf5641c lib/sqlalchemy/dialects/oracle/cx_oracle.py
--- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py   Mon Mar 21 11:49:43 2011 -0400
+++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py   Tue Mar 22 17:08:24 2011 -0400
@@ -294,7 +294,7 @@
         quoted_bind_names = \
             getattr(self.compiled, '_quoted_bind_names', None)
         if quoted_bind_names:
-            if not self.dialect.supports_unicode_binds:
+            if not self.dialect.supports_unicode_statements:
                 quoted_bind_names = \
                                 dict(
                                     (fromname, toname.encode(self.dialect.encoding))

need some unit tests in test/dialect/test_oracle.py and its good for a commit.

[Former user]

This should work (but I haven't worked out actually running tests), raises DatabaseError before change, not after.

Let me know if it works.

--- test/dialect/test_oracle.py 2011-03-22 18:46:19.000000000 -0400
+++ test/dialect/test_oracle.py 2011-03-22 18:47:21.000000000 -0400
@@ -1310,10 +1310,33 @@

         # here, its impossible.  But we'd prefer it to raise ORA-02014
         # instead of issuing a syntax error.
         assert_raises_message(
             exc.DatabaseError,
             "ORA-02014",
             t.select(for_update=True).limit(2).offset(3).execute
         )


+class UnicodeParamsTest(TestBase):
+    __only_on__ = 'oracle'
+
+    def test_basic(self):
+        engine = testing.db
+        metadata = MetaData(engine)
+
+        table=Table("atable", metadata,
+            Column("_underscorecolumn", Unicode(255), primary_key=True),
+        )
+        metadata.create_all()
+
+        try:
+            table.insert().execute(
+                {'_underscorecolumn': u'’é'},
+            )
+            result = engine.execute(
+                table.select().where(table.c._underscorecolumn==u'’é')
+            ).fetchall()
+            assert result[0](0)[0](0) == u'’é'
+        finally:
+            metadata.drop_all()
+

[Mike Bayer]

fixed the underscore and the unicode case. needed the flag and the encode on all items of that "bind names" dict.

45cf9be645824aafddfe36c8a6112561cc876aea 545b70e8a2bcc3803f0a72cc4a50c1b40a4a3eba

[Mike Bayer]

Removing milestone: 0.6.7 (automated comment)