[DB-SIG] create procedure / callproc question

Andy Todd andy47@halfcooked.com
Thu, 05 Sep 2002 13:50:42 +0100


Matthew T. Kromer wrote:
> 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.
> 
> 

I just tried this, and note that you must have cx_Oracle v2.5 (or 2.5a) 
for this to work;

 >>> cursor.execute("CREATE OR REPLACE PROCEDURE pump1(a IN OUT NUMBER) 
IS BEGIN a := a+1; END;")
 >>>
 >>> cursor.callproc("PUMP1", (1, ))
Traceback (most recent call last):
   File "<interactive input>", line 1, in ?
TypeError: argument 2 must be list, not tuple
 >>> cursor.callproc("PUMP1", [1])
[2]
 >>>

If you have cx_Oracle 2.4 or less then the way to call stored code 
objects is with anonymous PL/SQL blocks. Or you could just upgrade ;-)

HTH,
Andy
-- 
----------------------------------------------------------------------
 From the desk of Andrew J Todd esq - http://www.halfcooked.com