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?

Raze

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

Tanyatanzania

Answer 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?

Razor

Is it possible to create the function temporarily "on the fly" in a way that the data base forget it after calling db.close()? – Raze

Has the table "dual" any meaning or is it some kind of place holder? – Raze

It's just a kind of place-holder @user1946052; you can't select from nothing in Oracle: see docs.oracle.com/cd/E11882_01/server.112/e25789/… – Razor

I needed the same thing and actually found out the solution below. There is no need to define a function just to execute some PL/SQL and retrieve a result. – Hornbill

Answer 3

Agree with M. Wymann, and i had need of returning a string by passing number of elements in list. here is my code.

cursor = con.cursor()    
    for a,b,c in data:
    statement='''DECLARE
     t_name     VARCHAR2 (50);
     owner      VARCHAR2 (50);
     c_name     VARCHAR2 (50);
     O_type     VARCHAR2 (50);
     nullable   VARCHAR2 (20);
BEGIN
   SELECT t1.table_name,
          t1.owner,
          t1.column_name,
          CASE
             WHEN t1.data_type = 'NUMBER' AND t1.data_precision IS NULL
             THEN
                'NUMBER'
             WHEN t1.data_type = 'DATE'
             THEN
                t1.data_type
             WHEN REGEXP_REPLACE (t1.data_type, '(\d)|VAR', '') = 'CHAR'
             THEN
                t1.data_type || '(' || t1.DATA_LENGTH || ')'
             WHEN     t1.data_type = 'NUMBER'
                  AND t1.data_precision IS NOT NULL
                  AND t1.data_scale = 0
             THEN
                'NUMBER(' || t1.data_precision || ')'
             WHEN     t1.data_type = 'NUMBER'
                  AND t1.data_precision IS NOT NULL
                  AND t1.data_scale <> 0
             THEN
                'NUMBER(' || t1.data_precision || ',' || t1.data_scale || ')'
             ELSE
                'Not Handled'
          END
             "Oracle data type",
          t1.nullable
     INTO t_name,
          owner,
          c_name,
          O_type,
          nullable
     FROM all_tab_columns t1
    WHERE     t1.table_name = :tname
          AND t1.owner = :towner
          AND t1.column_name = :tcolname;

   :o_result :=
         t_name
      || '|'
      || owner
      || '|'
      || c_name
      || '|'
      || O_type
      || '|'
      || nullable;
EXCEPTION
   WHEN OTHERS
   THEN
      t_name := :tname;
      c_name := 'NOT FOUND ';
      owner := :towner;
      O_type := 'NOT FOUND ';
      nullable := 'NOT FOUND ';
      :o_result :=
            t_name
         || '|'
         || owner
         || '|'
         || c_name
         || '|'
         || O_type
         || '|'
         || nullable;
END;'''
    o_result = cursor.var(cx_Oracle.STRING)`enter code here`
    cursor.execute(statement, tname=a, towner=b, tcolname=c, o_result=o_result)
    ObLst = o_result.getvalue().split('|')

Personify