Mailing List Archive: Python: Python

odbc: how to call a stored function in Oracle

Index | Next | Previous | View Threaded

tom.hines at usa

Nov 7, 2001, 1:16 PM

Post #1 of 4 (288 views)
Permalink

odbc: how to call a stored function in Oracle

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?

--
Tom Hines

timr at probo

Nov 7, 2001, 10:36 PM

Post #2 of 4 (274 views)
Permalink

odbc: how to call a stored function in Oracle [In reply to]

tom.hines [at] usa (Tom Hines) wrote:
>
>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.

I don't think ODBC supports stored procedures returning a value directly.
Only as part of a cursor.

I also don't know Oracle; it is possible for you to restate that as a
SELECT statement with a constant, one-row result, as in:

SELECT Call MYPKG.MYFUNC (?) AS result;

--
- Tim Roberts, timr [at] probo
Providenza & Boekelheide, Inc.

tatebll at aol

Nov 8, 2001, 6:42 AM

Post #3 of 4 (276 views)
Permalink

odbc: how to call a stored function in Oracle [In reply to]

Tim Roberts <timr [at] probo> wrote in message news:<j66kutg7m8s1q311igl6jmndtgdjkgu2jm [at] 4ax>...
> tom.hines [at] usa (Tom Hines) wrote:
> >
> >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.
>
> I don't think ODBC supports stored procedures returning a value directly.
> Only as part of a cursor.
>
> I also don't know Oracle; it is possible for you to restate that as a
> SELECT statement with a constant, one-row result, as in:
>
> SELECT Call MYPKG.MYFUNC (?) AS result;

Tom,
I've run into the identical problem in the past. DCOracle is
frequently used to accessing stored procedures in Oracle using ZOPE -
while I've never used it for this particular problem, it appears to
support essentially what amounts to a function call. Someone else
perhaps can speak to this directly. In any event, suggest checking
out http://www.zope.org/Products/DCOracle/DCOracle.txt -

Now if there one for postgre as well - time for google.

osuchw at ecn

Nov 11, 2001, 1:39 AM

Post #4 of 4 (281 views)
Permalink

odbc: how to call a stored function in Oracle [In reply to]

tom.hines [at] usa (Tom Hines) wrote in message news:<25c120d1.0111071216.1947ca09 [at] posting>...
> 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] orac')
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

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads