Monday, February 21, 2011

cx_Oracle and output variables

I'm trying to do this again an Oracle 10 database:

cursor = connection.cursor()
lOutput = cursor.var(cx_Oracle.STRING)
cursor.execute("""
            BEGIN
                %(out)s := 'N';
            END;""",
            {'out' : lOutput})
print lOutput.value

but I'm getting

DatabaseError: ORA-01036: illegal variable name/number

Is it possible to define PL/SQL blocks in cx_Oracle this way?

From stackoverflow
  • Yes, you can do anonymous PL/SQL blocks. Your bind variable for the output parameter is not in the correct format. It should be :out instead of %(out)s

    cursor = connection.cursor()
    lOutput = cursor.var(cx_Oracle.STRING)
    cursor.execute("""
                BEGIN
                    :out := 'N';
                END;""",
                {'out' : lOutput})
    print lOutput
    

    Which produces the output:

    <cx_Oracle.STRING with value 'N'>
    
    Tim : Many thanks...I thought we'd tried that particular combination at some point, but we must have missed it!

0 comments:

Post a Comment