odbc: how to call a stored function in Oracle

[waldekO]

tom.hines at usa.net (Tom Hines) 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))
> 
> 
> # this doesn't work
> retparam = 0
> ret = cur.execute("{? = Call MYPKG.MYFUNC (?)}",
>     (retparam, 5341562))
> 
> 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
> 
> 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?

I see two ways you could do that.
First is to use ADO instead of ODBC.  See the page below for example.
The page is in Thai I belive but code is code.  You will figure that out.
http://www.exzilla.net/exDocs/oraado/ado_sp_singel_row.html

Other is to forget about ODBC or ADO and connect to Oracle directly.
You could use DCOracle module from zope.org site
http://www.zope.org/Products/DCOracle

or my favorite the one from Computronix
http://www.computronix.com/utilities/
for example:
import cx_Oracle as cx
conn = cx.connect('demo/demo at oracl')
cur = conn.cursor()
cur.execute("begin MYPKG.MYPROC(:retparam, 5341562);end;", retparam='emptynow')
resultdict = cur.fetchbinds()

resultdict would be dictionary holding result of the procedure.
You may have to set input size before calling execute.

waldekO