Return variable from cx_Oracle PL/SQL call in Python

Question

I want to execute an Oracle PL/SQL statement via cx_oracle in Python. Code looks like this:

db = cx_Oracle.connect(user, pass, dsn_tns)
cursor = db.cursor()

... 

sel = """
DECLARE
  c   NUMBER := 0.2;
  mn  NUMBER := 1.5;
  res NUMBER;
BEGIN
  res := c+mn/6.;
END;
"""
try:
  cursor.execute(sel) 
  print "PL/SQL successful executed ..."
except cx_Oracle.DatabaseError as e:
  err, = e.args
  print "\n".join([str(err.code),err.message,err.context])

The code is running without problems, but is there any chance to get the result back to Python?

Answer 1

You can bind input and output variables to the block like so.

import cx_Oracle

SQL_BLOCK = '''
DECLARE
  v_first   NUMBER;
  v_second  NUMBER;
  v_result  NUMBER;
BEGIN
  v_first  := :i_first;   -- (1)
  v_second := :i_second;  -- (1)

  v_result := (v_first + v_second) / 2;

  :o_result := v_result;  -- (1)
END;
'''

with cx_Oracle.connect('hr/hr@xe') as db:
    cur = db.cursor()
    o_result = cur.var(cx_Oracle.NUMBER) # (2)
    cur.execute(SQL_BLOCK, i_first=23, i_second=55, o_result=o_result) # (3)
    res = o_result.getvalue()  # (4)
    print('Average of 23 and 55 is: {}'.format(res))

1. Use the regular bind notation (:) in the PL/SQL block for both input and output variables
2. For output variables obtain a variable from the cursor (of the appropriate type)
3. In the execute call provide values for the input variables and the variable from (2) as parameters
4. Retrieve the value from the output variables

The script should print

Average of 23 and 55 is: 39.0