Calling a stored function from oracle DB with cx_Oracle in Python

Question

I am not familiar with PL/SQL and just can't transform a working raw query into python code.

Here is the raw query:

DECLARE
  returntype MY_PKG_1.MY_DATA_TYPE;
BEGIN
  SELECT *
   BULK COLLECT INTO returntype
  FROM TABLE(MY_PKG_2.MY_FUNC(
    param_1 => 'some data',
    param_2 => 'some more data'
  ));
END;

It is a valid query that works just fine, I can run it with cursor.execute() with no errors. But how to get the returned rows? I've tried the following:

returntype = connection.gettype('MY_PKG_1.MY_DATA_TYPE')
cursor = connection.cursor()
results = cursor.callfunc('MY_PKG_2.MY_FUNC', returntype, ['some data', 'some more data'])
cursor.commit()

But I get an error "ORA-04043: object MY_PKG_1.MY_DATA_TYPE does not exist".

Your PL/SQL block doesn't return anything to the caller so there are no rows to return. Is there some reason that you wouldn't just run the select statement, sans bulk collect, from your Python code? I don't see what the collection type, local variable, and PL/SQL block are accomplishing. — Justin Cave
– Justin Cave, Commented Nov 9, 2021 at 22:37

Christopher Jones · Accepted Answer · 2021-11-10 04:17:13Z

1

If the PL/SQL block just does a single query, then execute that query directly without using PL/SQL:

with connection.cursor() as cursor:
    try:
        for r in cursor.execute('SELECT * FROM TABLE(MY_PKG_2.MY_FUNC(param_1 => :p1, param_2 => :p2))', p1='some more data', p2='some data')
            print(r)
    except cx_Oracle.Error as e:
        error, = e.args
        print(error.message)
        print(sql)
        if (error.offset):
            print('^'.rjust(error.offset+1, ' '))

Otherwise you will have to bind a REF CURSOR, or use Implicit Results. See the cx_Oracle examples ref_cursor.py and implicit_results.py.

If you are returning collections etc, then this gives some insights into generically traversing them. But check the doc for simpler examples.

Also see the cx_Oracle doc on tuning fetch performance.

answered Nov 10, 2021 at 4:17

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Calling a stored function from oracle DB with cx_Oracle in Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related