sqlalchemy Oracle REF CURSOR

Question

Hi

I am using sqlalchemy for connection pooling only (need to call existing procs) and want to return a REF CURSOR which is an out parameter.

There seems to be no cursor in sqlalchemy to do this.

Any advice greatly appreciated.

Answer 1

Gut feel - you may have to dive down a lower level than SQLAlchemy, perhaps to the underlying cx_oracle classes.

From an answer provided by Gerhard Häring on another forum :

import cx_Oracle 

con = cx_Oracle.connect("me/secret@tns") 
cur = con.cursor() 
outcur = con.cursor() 
cur.execute(""" 
BEGIN 
   MyPkg.MyProc(:cur); 
END;""", cur=outcur) 

for row in out_cur: 
print row 

I would presume that as SQLAlchemy uses cx_oracle under the hood there should be some way to use the same pooled connections.

Is there any option to wrap your function returning the REF CURSOR in a view on the Oracle side?? (Provided the shape of the REF CURSOR doesn't change, and you can somehow get the right parameters into your function - possibly by sticking them in as session variables, or in a temp table, this should work - I've used this approach to retrieve data from a REF CURSOR function to a language that only supports a limited subset of Oracle features).