[DB-SIG] cx_Oracle - passing unnecessary bind variables

Anthony Tuininga anthony at computronix.com
Thu Nov 20 16:27:37 EST 2003

On Thu, 2003-11-20 at 14:01, Paul Moore wrote:
> I'm trying to write a function, using cx_Oracle, which allows the user
> to specify a SQL statement, to be executed with some bind variables
> out of a common set.
> Example:
>     class Collector:
>         def __init__(self, cn, **kws):
>             self.vars = kws
>         def execute(sql):
>             c = cn.cursor()
>             c.execute(sql, self.vars)
>             c.close()
> This is a simplified example, but the basic usage is something like
> this:
>     cn = cx_Oracle.connect(...)
>     coll = Collector(cn, a=12, b=24, c=11, d=13)
>     coll.execute("insert into t1 (a,b) values(:a, :b)")
>     coll.execute("insert into t2 (a,c,d) values(:a, :c, :d)")
> The problem is that this doesn't work - it returns an Oracle error,
> cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number.
> OK, this is to some extent a limitation of Oracle (or maybe of
> cx_Oracle, I don't know how Oracle OCI works at this low a level) -
> I'm passing bind variables which aren't used by the SQL statement. But

This is definitely a limitation of Oracle. Oracle does not allow you to
specify variables that are not part of the statement. As far as I know
there is no way around this. Oracle does provide a way of returning to
you the bind variable names once the statement is prepared but cx_Oracle
does not (currently) provide that information. I have another workaround
for now but if you really think this would be helpful, feel free to ask
me for it -- this is definitely beyond the scope of the DB API but I
have long since stopped feeling restricted by the limitations of the API
(which has to accommodate each of the different database management
systems out there).

> is there any way of doing something like this? One option is to scan
> the SQL manually - something like
>     rx = re.compile(":\w+")
>     vars = [m.group()[1:] for m in rx.finditer(sql)]
>     bindvars = {}
>     for var in vars:
>         try:
>             bindvars[var] = self.vars[var]
>         except KeyError:
>             pass
> but (a) that's very messy, and (b) it's fragile - consider
> sql = "select a || ':b' from t where a = :a"...

Oracle does make this information available once the statement is
prepared but if you want something quicker (but less "nice") than I can
suggest the following:

class Collector:

    def __init__(self, connection, **args):
        self.connection = connection
        self.args = args

    def execute(self, sql, *args):
        realArgs = {}
        for arg in args:
            realArgs[arg] = self.args[arg]
        cursor = self.connection.cursor()
        cursor.execute(sql, **realArgs)

with the examples above being translated to:

cn = cx_Oracle.connect(...)
coll = Collector(cn, a=12, b=24, c=11, d=13)
coll.execute("insert into t1 (a, b) values (:a, :b)", "a", "b")
coll.execute("insert into t1 (a, c, d) values (:a, :c, :d)", "a", "c",

> Can anyone suggest anything better?

Not sure if this is "better" or not. Let me know... :-)

> Thanks,
> Paul.
Anthony Tuininga
anthony at computronix.com
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838

More information about the DB-SIG mailing list