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

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

It does seem simple and elegant but as soon as I started looking into
coding it, I ran into one little problem. Specifically, the return type
is unknown and needs to be specified. On a standalone function, this
could fairly reasonably be determined by either using the
OCIDescribeAny() functionality in OCI or by reading the data dictionary.
The problem comes with packaged functions because Oracle then allows
overloads. And duplicating Oracle's method of deciding which overloaded
function to execute doesn't seem worthwhile. So I am now considering the
following:

value = cursor.callfunc(funcName, returnType, parameters = [])

where returnType is one of the values accepted by setinputsizes() or is
a variable already created by the cursor.var() or cursor.arrayvar()
methods. How does that sound?

On Fri, 2003-08-01 at 04:06, McCafferty, Chris wrote:
> Hi,
> 
> >>value = cursor.callfunc(funcName, parameters)
> 
> This seems simple and elegant, and mirrors calling a procedure. After all,
> to the caller, a function is just a procedure that returns a value. I know
> that there are more subtleties than that in Oracle, but that really is for
> the function writer, not the API caller.
> 
> Let me know if you add this and I'll be more than happy to run some tests
> against it.
> 
> Cheers,
> 
> Chris
> 
> -----Original Message-----
> From: Anthony Tuininga [mailto:an...@co...]
> Sent: 25 July 2003 18:31
> To: McCafferty, Chris; cx_Oracle Mailing List
> Subject: RE: [cx-oracle-users] calling Oracle functions and getting
> result s back
> 
> 
> 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...>