how to call a stored function in Oracle

[Steve Holden]

"Tom Hines" <tom.hines at usa.net> wrote in message
news:25c120d1.0111071216.1947ca09 at posting.google.com...
> Hi.  I'm using ActivePython 2.1.212 and MS ODBC driver for Oracle.  I
> am able to call a stored procedure that returns a cursor, but I
> haven't been able to figure out how to call a stored function or a
> procedure that has an output parameter.
>
> # this works
> schools = ""
> ret = cur.execute(
>     "{Call MYPKG.MYPROC (?, {resultset 50, outSchools})}",
>     ('myparam', schools))
>
As you would expect it to, because the module can replace the parameter
marker with the *value* of the provided parameter.
>
> # this doesn't work
> retparam = 0
> ret = cur.execute("{? = Call MYPKG.MYFUNC (?)}",
>     (retparam, 5341562))
>
This won't work because here you are trying to provide a Python *name* and
somehow have the driver automagically have the output from the stored
procedure pushed through into the Python namespace.

I don't have examples of stored procedures to show me how to do what you
want. You *might* find useful information in or near

    http://aspn.activestate.com/ASPN/Mail/Message/db-sig/775892


> I've tried a million combinations trying to get it to work, but I
> either get
>
> dbi.internal-error: [Microsoft][ODBC driver for Oracle]Invalid
> parameter type in EXEC
>
... because your call is becoming something like

0 = Call MYPKG.MYFUNC(5341562)

> or
>
> dbi.program-error: [Microsoft][ODBC driver for
> Oracle][Oracle]ORA-06550: line 1, column 7:
> PLS-00306: wrong number or types of arguments in call to 'MYFUNC'
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored in EXEC
>
> Anybody know how to do this?
>
Sorry, best I could to was try to explain. You probably need the .callproc()
DB API call, but not all modules implement it, and you need to remember that
output parameters are intended to return values directly into variables of
an embedded programming environment such as PL/SQL.

I'm not sure you can do what you want vie the DB API.

regards
 Steve
--
http://www.holdenweb.com/