[DB-SIG] cx_Oracle - passing unnecessary bind variables

Paul Moore pf_moore at yahoo.co.uk
Thu Nov 20 16:01:42 EST 2003

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.


    class Collector:
        def __init__(self, cn, **kws):
            self.vars = kws
        def execute(sql):
            c = cn.cursor()
            c.execute(sql, self.vars)

This is a simplified example, but the basic usage is something like

    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
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:
            bindvars[var] = self.vars[var]
        except KeyError:

but (a) that's very messy, and (b) it's fragile - consider
sql = "select a || ':b' from t where a = :a"...

Can anyone suggest anything better?

This signature intentionally left blank

More information about the DB-SIG mailing list