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.
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']