Return variable from cx_Oracle PL/SQL call in Pyth

问题:

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?

回答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

回答2:

You need a function to return a result. An anonymous block will not.

You need to create a function in the database, for instance:

create or replace function calculation return number is
  c   number := 0.2;
  mn  number := 1.5;
  res number;
begin
  return c + mn / 6.;
end;
/

Then change your Python code to call the function, using, callfunc()

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

try:
  result = cursor.callfunc('calculation', float)
  print result
except cx_Oracle.DatabaseError as e:
  err, = e.args
  print "\n".join([str(err.code),err.message,err.context])

---

It's not possible to create a function on the fly but your function is simple enough that you can do it in a select statement and use fetchall() as described in the linked documentation to return the result to Python. fetchall() returns a list of tuples so if you're only after a single row and column you can immediately select the 0^th index of both.

>>> import cx_Oracle
>>> db = cx_Oracle.connect('****','****','****')
>>> cursor = db.cursor()
>>> SQL = """select 0.2 + 1.5 / 6. from dual"""
>>> try:
...     cursor.execute(SQL)
...     result = cursor.fetchall()[0][0]
... except cx_Oracle.DataBaseError, e:
...     pass
...
<__builtin__.OracleCursor on <cx_Oracle.Connection to ****@****>>
>>> result
0.45000000000000001
>>>

You can also pass the variables into your execute() call using bind variables and therefore instantiate them in Python if necessary:

>>> c = 0.2
>>> mn = 1.5
>>> SQL = """select :c + :mn / 6. from dual"""
>>> bind_vars = { 'c' : c, 'mn' : mn }
>>> cursor.execute(SQL, bind_vars)
<__builtin__.OracleCursor on <cx_Oracle.Connection to history@monitor>>
>>> result = cursor.fetchall()[0][0]
>>> result
0.45000000000000001
>>>

Though it might be simpler to do all this in Python... I assume your actual situation is more complicated?