RE: [cx-oracle-users] calling Oracle functions and getting result s back

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

You can certainly vote for it. But do you have any idea of the interface
that would make sense?

May I propose the following?

value = cursor.callfunc(funcName, parameters)

The value that would be returned would be the return value of the
function. If you needed out variables in addition, you could do the
following:

outVar = cursor.var(cx_Oracle.NUMBER)
value = cursor.callfunc(funcName, (1, outVar))
print "Value is:", value
print "OutVar is:", outVar.getvalue()

How does that sound? I realize this is different from callproc() but
since this isn't covered by the DB API it is not necessarily bad. It is
certainly better than the anonymous PL/SQL block method for the common
case, wouldn't you agree? Any comments?

On Fri, 2003-07-25 at 11:22, McCafferty, Chris wrote:
> Hi,
> 
> I see - you do it through execute. Thanks.
> May i vote for a callFunc extension? It feels more 'pythonic'. But I see
> your point about having to set up the out var before hand - that kind of
> messes it up.
> 
> Cheers,
> 
> Chris
> 
> -----Original Message-----
> From: Anthony Tuininga [mailto:an...@co...]
> Sent: 24 July 2003 14:42
> To: McCafferty, Chris
> Cc: 'cx-...@li...'
> Subject: Re: [cx-oracle-users] calling Oracle functions and getting
> results back
> 
> 
> 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