Blog / 2010 / April / 23

Output Parameters with cx_Oracle and PL/SQL

April 23, 2010

We were struggling to return an output parameter from a PL/SQL block into a Django application using cx_Oracle. It was very difficult to work out which particular style of bind variables needed to be used.

>>> import cx_Oracle
>>> lDsn = cx_Oracle.makedsn(lDatabaseHost, int(lDatabasePort), lDatabaseName)
>>> lConnectString = "%s/%s@%s" % (lDatabaseUsername, lDatabasePassword, lDsn)
>>> lConnection = cx_Oracle.connect(lConnectString)
>>> cursor = lConnection.cursor()
>>> lOutput = cursor.var(cx_Oracle.STRING)
>>> cursor.execute("BEGIN :out := 'N'; END;", {'out' : lOutput})
>>> print lOutput
<cx_Oracle.STRING with value 'N'>
>>> print lOutput.getvalue()
N

Interestingly, this is fine standalone, but if run through the django provided database connection it fails with TypeError: not all arguments converted during string formatting

>>> from django.db import connection
>>> cursor = connection.cursor()
>>> import cx_Oracle
>>> lOutput = cursor.var(cx_Oracle.STRING)
>>> cursor.execute("BEGIN :out := 'N'; END;", {'out' : lOutput})
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/dev/HEAD/INTERNAL/websites/Wam3\django\db\backends\util.py", line 19, in execute
    return self.cursor.execute(sql, params)
  File "/web/djangocourse\django\db\backends\oracle\base.py", line 435, in execute
    query = convert_unicode(query % tuple(args), self.charset)
TypeError: not all arguments converted during string formatting

The simple solution is to set up a "normal" cx_Oracle connection and use that instead, but that's not ideal.

UPDATE

This can be fixed by using %s for the bind variables, passing in the variable values in a list, and by adding a space after the final semicolon in the SQL block. Django strips the final semicolon off, and adding the space prevents this happening.

>>> from django.db import connection
>>> cursor = connection.cursor()
>>> import cx_Oracle
>>> lOutput = cursor.var(cx_Oracle.STRING)
>>> cursor.execute("BEGIN %s := 'N'; END; ", [lOutput])
>>> print lOutput
<django.db.backends.oracle.base.VariableWrapper object at 0x02785550>
>>> print lOutput.getvalue()
N

References

Tags: cx_oracle plsql output python django oracle bind