Return variable from cx_Oracle PL/SQL call in Python

cx-oracle, plsql, python

Solution

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))

- Use the regular bind notation (:) in the PL/SQL block for both input and output variables

- For output variables obtain a variable from the cursor (of the appropriate type)

- In the execute call provide values for the input variables and the variable from (2) as parameters

- Retrieve the value from the output variables

The script should print

Average of 23 and 55 is: 39.0

Problem

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?

Original source