[DB-SIG] create procedure / callproc question

Matthew T. Kromer matt@zope.com
Wed, 04 Sep 2002 16:10:58 -0400

Bob Gailer wrote:

> Using cx_Oracle:
> >>> cursor.execute("create or replace procedure pump1(a in number) as 
> begin null; end;")
> >>> cursor.callproc("pump1(1)")
> Results in:
> DatabaseError: ORA-06550: line 1, column 7:
> PLS-00801: internal error [22503]
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
> What do I need to fix?
> My goal is to have an out parameter (or rmake pump1 a function) with 
> data returned to Python. What do I change to do that? I'm assuming the 
> create changes to:
> "create or replace procedure pump1(a out number) as begin a := 
> something; end;"
> I also notice that if I make an error in the create statement, execute 
> returns no error to Python!

I suspect you want something similar to

cursor.callproc("pump1", (1,))

or thereabouts.  According to the DB API, all parameters to the stored 
procedure are returned as a list, so you'd get back (1,) as well.  If 
that was an IN/OUT parameter, what you'd get back could have been 
changed by the DB.