Calling Oracle Stored Procedures Having Out Cursor Parameter

[cbaron]

Hey all,

I have to call some pre-existing Oracle stored procedures that have
cursor out parameters. I've had no luck doing this short of using
"raw" cursors. Is there a better way to do this?

Here is a simple stored procedure modelled after what I have to call:

create or replace package TEST_PKG as
type refcur is ref cursor;
procedure simple_read(p_param out refcur);
end TEST_PKG;
/

create or replace package body TEST_PKG as
procedure simple_read(p_param out refcur) as
begin
open p_param for
select name from some_table;
end simple_read;
end TEST_PKG;
/


Here is the only code I was able to find that worked:

import sqlalchemy

engine = sqlalchemy.create_engine('oracle://<user>:<pwd>@<db>')
oracle_conn = engine.raw_connection()

in_cursor = oracle_conn.connection.cursor()
out_cursor = oracle_conn.connection.cursor()
in_cursor.execute('begin TEST_PKG.simple_read(:x); end;', x =
out_cursor)

results = out_cursor.fetchall()
print repr(results)


Any suggestions in how I can better do this with sqlalchemy?

Thanks!

[Michael Bayer]

On Apr 7, 2009, at 4:11 PM, cbaron wrote:

>
> Hey all,
>
> I have to call some pre-existing Oracle stored procedures that have
> cursor out parameters. I've had no luck doing this short of using
> "raw" cursors. Is there a better way to do this?

we have an outparam construct which makes use of cx_oracle's built in
API for this:

result = testing.db.execute(text("begin
foo(:x_in, :x_out, :y_out, :z_out); end;",
bindparams=[bindparam('x_in', Numeric), outparam('x_out', Numeric),
outparam('y_out',
Numeric), outparam('z_out', String)]), x_in=5)

assert result.out_parameters == {'x_out':10, 'y_out':75, 'z_out':None}

[cbaron]

On Apr 7, 10:49 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> we have an outparam construct which makes use of cx_oracle's built in  
> API for this:
>
> result = testing.db.execute(text("begin  
> foo(:x_in, :x_out, :y_out, :z_out); end;",  
> bindparams=[bindparam('x_in', Numeric), outparam('x_out', Numeric),  
> outparam('y_out',
> Numeric), outparam('z_out', String)]), x_in=5)
>
> assert result.out_parameters == {'x_out':10, 'y_out':75, 'z_out':None}

Thanks Michael. I tried something like this, but I could not find any
generic or vendor specific type to specify for the cursor parameter
(e.g. something like .. outparam(':p_out', OracleCursor)). Does this
make sense?

It's not likely I'll be able to change these stored procedures
unfortunately, so I may be stuck with a cursor out parameter... :(

[Michael Bayer]

cbaron wrote:
>
> On Apr 7, 10:49 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
>> we have an outparam construct which makes use of cx_oracle's built in  
>> API for this:
>>
>> result = testing.db.execute(text("begin  
>> foo(:x_in, :x_out, :y_out, :z_out); end;",  
>> bindparams=[bindparam('x_in', Numeric), outparam('x_out', Numeric),  
>> outparam('y_out',
>> Numeric), outparam('z_out', String)]), x_in=5)
>>
>> assert result.out_parameters == {'x_out':10, 'y_out':75, 'z_out':None}
>
> Thanks Michael. I tried something like this, but I could not find any
> generic or vendor specific type to specify for the cursor parameter
> (e.g. something like .. outparam(':p_out', OracleCursor)). Does this
> make sense?

im missing that part. the out parameter receives a cursor ? strange.
you should at least modify your code to use cx_oracle's API, check out
their site for information on that. I didn't realize a cursor was a
datatype (seems very strange);