How to Use an Oracle SYS_REFCURSOR from PL/SQL to Python Oracledb / cx_Oracle via Implicit Results

As an alternative to returning a SYS_REFCUSOR variable to python, using Implicit Results is a bit easier and requires less code.

import oracledb

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

cur = conn.cursor()

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

            dbms_sql.return_result(l_cursor);

        END;
    '''
)

rows = cur.getimplicitresults()[0].fetchall()

assert rows == [(1,)]

Note that this cur.getimplicitresults() returns an array of cursors, one for each call to dbms_sql.return_result(...). So if you wanted to, you could actually open multiple cursors and return them this way.


Comments

Add Comment

Name

Email

Comment

Are you human? * nine = 36