odbc: how to call a stored function in Oracle

waldekO osuchw at ecn.ab.ca
Sun Nov 11 09:39:53 CET 2001

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.

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

or my favorite the one from Computronix
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.


More information about the Python-list mailing list