[egenix-users] Passing a value from stored procedure to python?

[Griff McClellan]

Hello Mr. Lemburg,

Thank you for the reply.  I have been unable to return a result set from a 
stored procedure.  I appreciate any suggestions.

Here's a distillation of the problem.  I can generate a result set by 
executing a select statement.  For example,


>>> cur.execute('select 66 from dual')
>>> cur.fetchall()
[(66.0,)]



But if I create a stored procedure to generate the same result set, the 
procedure returns 1 and no result set.



CREATE PROCEDURE "CRAIG"."RESULTSET_TEST_SP" is
n number := -1;
begin
select 66 into n from dual;
end;


>>> cur.execute('call RESULTSET_TEST_SP()')
1
>>> cur.fetchall()
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
mxODBC.ProgrammingError: missing result set



----- Original Message ----- 
From: "M.-A. Lemburg" <mal at egenix.com>
To: "Griff McClellan" <griffmcc at comcast.net>
Cc: <egenix-users at egenix.com>
Sent: Monday, January 22, 2007 2:12 AM
Subject: Re: [egenix-users] Passing a value from stored procedure to python?


> On 2007-01-22 02:13, Griff McClellan wrote:
>> Hello,
>>
>> I need to pass a value from a stored procedure or function to the calling
>> python script.  Nothing I have tried has worked.  I am using 
>> mx.ODBC.Windows
>> with Oracle 10g on Windows XP.
>>
>> Can someone show me how a value, say 66, can be passed from a stored
>> procedure/function to a python variable?
>
> You have to create a result using SELECT. mxODBC doesn't support
> output parameters from stored procedures, but it does support
> multiple result sets, so this is well possible, even if you have
> more than one output parameter that you wrap into result sets.
>
> Regards,
> -- 
> Marc-Andre Lemburg
> eGenix.com
>
> Professional Python Services directly from the Source  (#1, Jan 22 2007)
>>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
> ________________________________________________________________________
>
> ::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::