How to return an Oracle SYS_REFCURSOR from PL/SQL to Python Oracledb / cx_Oracle

Using oracledb (cx_Oracle), we can execute PL/SQL that returns a SYS_REFCURSOR or CURSOR back to Python. A slightly easier way is by using implicit results.

To do so, we simply pass in a bind variable to the PL/SQL and set it to the cursor, like the following:

DECLARE
    l_cursor SYS_REFCURSOR;
BEGIN
    OPEN l_cursor FOR 
        SELECT 1 foo
        FROM dual
    ;

    :cursor := l_cursor;
END;

Don't forget to close this cursor out in Python.

Here is the full code:

import oracledb

conn = oracledb.connect('MYDATABASE', user='foo', password='bar')

cur = conn.cursor()

cursor_var = cur.var(oracledb.CURSOR)

cur.execute(
    '''
        DECLARE
            l_cursor SYS_REFCURSOR;
        BEGIN
            OPEN l_cursor FOR 
                SELECT 1 foo
                FROM dual
            ;

            :cursor := l_cursor;
        END;
    ''',
    dict(
        cursor=cursor_var
    )
)

cursor = cursor_var.getvalue()

rows = cursor.fetchall()

assert rows == [(1,)]

cursor.close()

cur.close()

conn.close()

Note that this won't work with CURSOR and that you'll have to use SYS_REFCURSOR instead:

cur.execute(
    '''
        DECLARE
            CURSOR l_cursor IS
                SELECT 1 foo
                FROM dual
            ;
        BEGIN
            OPEN l_cursor;

            :cursor := l_cursor;
        END;
    ''',
    dict(
        cursor=cursor_var
    )
)



Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/python3.12/site-packages/oracledb/cursor.py", line 701, in execute
    impl.execute(self)
  File "src/oracledb/impl/thin/cursor.pyx", line 178, in oracledb.thin_impl.ThinCursorImpl.execute
  File "src/oracledb/impl/thin/protocol.pyx", line 438, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 439, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 432, in oracledb.thin_impl.Protocol._process_message
oracledb.exceptions.DatabaseError: ORA-06550: line 9, column 24:
PLS-00382: expression is of wrong type
ORA-06550: line 9, column 13:
PL/SQL: Statement ignored
Help: https://docs.oracle.com/error-help/db/ora-06550/

How to return an Oracle SYS_REFCURSOR from PL/SQL to Python Oracledb / cx_Oracle

Comments

Add Comment