Search code examples
pythonoracle-databaseplsqlpython-oracledb

In oracledb How to retrieve the column names of the REF CURSOR output from cursor.execute?


Below is the code I tried which is working fin if I change

column_names by column_names = ['Col1','Col2','Col3']

But I need it to be dynamic because the number and the name of the columns can change depending on the procedure I want to execute.

cursor.execute(GET_Transaction_History, date_value=date_value, cursor=ref_cursor)
column_names = [desc[0] for desc in ref_cursor.description]

df = pd.DataFrame(ref_cursor.getvalue(), columns=column_names)

The below line throw the following error :

column_names = [desc[0] for desc in ref_cursor.description]

AttributeError: 'Var' object has no attribute 'description'

So I wonder how to retrieve column names properly.


Solution

  • You seem to be using a REF CURSOR returned from a PL/SQL procedure.

    The following PL/SQL procedure:

    drop table t purge;
    create table t (IntCol number, StringCol varchar2(4), DateCol date);
    
    insert into t (IntCol, StringCol, DateCol) values (1, 'abcd', sysdate);
    insert into t (IntCol, StringCol, DateCol) values (2, 'efgh', sysdate);
    commit;
    
    create or replace procedure myrefcursorproc2 (rc out sys_refcursor) as
    begin
        open rc for select IntCol, StringCol from t;
    end;
    /
    
    

    can be used like:

    import getpass
    import os
    
    import oracledb
    
    un = os.environ.get('PYTHON_USERNAME')
    cs = os.environ.get('PYTHON_CONNECTSTRING')
    pw = getpass.getpass(f'Enter password for {un}@{cs}: ')
    
    with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
        with connection.cursor() as cursor:
            ref_cursor = connection.cursor()
            cursor.callproc("myrefcursorproc2", [ref_cursor])
            column_names = [desc[0] for desc in ref_cursor.description]
            print(column_names)
    

    Running this gives the column names for the REF CURSOR:

    Enter password for cj@localhost/orclpdb1: 
    ['INTCOL', 'STRINGCOL']