Re: [cx-oracle-users] calling Oracle functions and getting results back

[Anthony T. <an...@co...>]

You can do this with cx_Oracle but unfortunately the DB API does not
cover the situation with functions. And Oracle provides less than
stellar diagnostics when you attempt to call a function as a procedure.
:-)

The (simplest) way you can do this is as follows:

import cx_Oracle

connection = cx_Oracle.connect("user/pw@tns")
cursor = connection.cursor()
resultVar = cursor.var(cx_Oracle.NUMBER)
cursor.execute("""
    begin
        :result := test_func(:param);
    end;""",
    param = 1,
    result = resultVar)
print "Result:", resultVar.getvalue()

I have considered extending the DB API to have a "callfunc" or something
similar that would return the return value and the tuple of (possibly
modified) values but that hasn't seemed all that useful to me. The only
other possibility is to modify the tuple that is returned to include the
return value at the beginning (or end) of the values. That doesn't
appeal to me either.

The DB API does not really handle the possibility of "out" values very
well either and has no understanding of anonymous PL/SQL blocks. Thus
the reason for the "var" call above.

On Thu, 2003-07-24 at 03:32, McCafferty, Chris wrote:
> Hi,
> 
> Looking through the Aspn archives, there is discussion of returning results
> from stored procedures, including Oracle procedures, but no clear-cut
> example of calling a function. Is this possible using cx_oracle?
> 
> For example, if I have this function, which simply increments the number
> passed in:
> 
> CREATE OR REPLACE FUNCTION TEST_FUNC(v_int in INTEGER)
>     RETURN NUMBER
> AS
>     j INTEGER := NULL;
> BEGIN
>     j := v_int + 1;
>     RETURN j;
> END;
> /
> 
> If I try to invoke it with cx_oracle, it claims the _procedure_ doesn't
> exist (where mTo below is a cursor created in the normal way):
> >>> mTo.callproc('TEST_FUNC', [1])
> Traceback (most recent call last):
>   File "<stdin>", line 1, in ?
> cx_Oracle.DatabaseError: ORA-06550: line 1, column 7:
> PLS-00221: 'TEST_FUNC' is not a procedure or is undefined
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
> 
> Do I have to use cursor.execute() instead? In which case what format should
> the sql string passed to execute take?
> 
> Note that if I have a procedure that does the same thing (but doesn't
> obviously return anything), then I can call it successfully.
> 
> Can anyone help?
> 
> Regards,
> 
> Chris
> 
> 
> ----------------------------------------------------------------------
> If you have received this e-mail in error or wish to read our e-mail 
> disclaimer statement and monitoring policy, please refer to 
> http://www.drkw.com/disc/email/ or contact the sender.
> ----------------------------------------------------------------------
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email sponsored by: Free pre-built ASP.NET sites including
> Data Reports, E-commerce, Portals, and Forums are available now.
> Download today and enter to win an XBOX or Visual Studio .NET.
> http://aspnet.click-url.com/go/psa00100003ave/direct;at.aspnet_072303_01/01
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
-- 
Anthony Tuininga
an...@co...
 
Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com