Retrieving column names from ref cursor with cx_Oracle

Question

In using cx_Oracle to invoke a simple stored procedure, I can easily get data back from the procedure as so:

db = db_class() #Sets up stuff, etc.
conn = db.connect() #Returns a connection to the database

cursor = conn.cursor()
l_results = cursor.var(cx_Oracle.CURSOR)

res = cursor.callproc("PROG.DATA.GET_EVENTS", [1,2,l_results]) 
#params = siteID, userID, ref cursor

res[2] ends up being some sort of enumerable I can easily iterate through like so:

data = [row for row in res[2]]

I end up with a list of lists/tuples which gives me the values, but I also need the column names. I've tried the following:

cols = cursor.description if cursor.description is not None else [] #Returns None
cols = res[2].description if res[2].description is not None else [] 
#Returns an error. Same if l_results is used instead

How do I get the column names from l_results? I've determined that l_results is a Variable object and not a cursor, so it won't work. Same with res[2]. But I just can't get the columns from curs.

curs.description after this appears to be a None value, when it should be a list of 7-item tuples

What in the world am I missing?

---

EDIT 1: I've tried changing it from callproc to the following. Same issue.

res = cursor.execute("BEGIN PROG.DATA.GET_EVENTS(:1,:2,:3); END;", {"1": 1,"2": 2,"3":, l_results})
cols = cursor.description #Returns None, again.

The call does return data, so I'm not sure why description isn't being set.

Noelianoell

From the official docs: "Cursor.description - This read-only attribute is a sequence of 7-item sequences. Each of these sequences contains information describing one result column: (name, type, display_size, internal_size, precision, scale, null_ok). This attribute will be None for operations that do not return rows or if the cursor has not h̲a̲d̲ ̲a̲n̲ ̲o̲p̲e̲r̲a̲t̲i̲o̲n̲ ̲i̲n̲v̲o̲k̲e̲d̲ ̲v̲i̲a̲ ̲t̲h̲e̲ ̲e̲x̲e̲c̲u̲t̲e̲(̲)̲ ̲m̲e̲t̲h̲o̲d̲ yet." – Zarla

Tried that. Added an edit with what I tried. No data in the results cursors even though the other result returned data. – Noelianoell

Answer 1

The columns for the ref cursor can be determined from the ref cursor itself. Given a procedure like the following:

create or replace procedure so50399550 (
    a_Input1            number,
    a_Input2            number,
    a_Output            out sys_refcursor
) is
begin
    open a_Output for
    select a_Input1 as num, a_Input1 * a_Input1 as square from dual
    union all
    select a_Input2, a_Input2 * a_Input2 from dual;
end;
/

The Python code can be as follows:

refCursorVar = cursor.var(cx_Oracle.CURSOR)
cursor.callproc("so50399550", [1, 2, refCursorVar])
refCursor = refCursorVar.getvalue()
print("Rows:")
for row in refCursor:
    print(row)
print()
print("Column Info:")
for column in refCursor.description:
    print(column)
print()

BUT, you can do it much more simply using the following code. You don't have to actually create a variable. You can bind a cursor directly.

refCursor = conn.cursor()
cursor.callproc("so50399550", [1, 2, refCursor])
print("Rows:")
for row in refCursor:
    print(row)
print()
print("Column Info:")
for column in refCursor.description:
    print(column)
print()

A sample can be found here.

Orgiastic

Bloody hell dude, that actually solved my problem. Documentation on cx_Oracle's site really doesn't make this clear. Thank you. – Noelianoell

Glad to hear it. And yes, the documentation for cx_Oracle could be improved. :-) And since I am the author I'll take this as some constructive criticism... :-) – Orgiastic

Ah, fair. Still a useful module regardless, so it's no biggie. Have a good day. – Noelianoell

@AnthonyTuininga thanks a lot for the example. Also, the link in the comment seems not to work (404). Cheers! – Publius

I've removed that comment and added the sample link in the answer instead. – Orgiastic