[DB-SIG] How to return results from stored procedures in Oracle

[M.-A. Lemburg]

Gerhard H=E4ring wrote:
> * Anthony Tuininga <anthony@computronix.com> [2002-08-31 12:33 -0600]:
>=20
>>On Sat, 2002-08-31 at 10:10, Andy Todd wrote:
>>
>>>In Oracle, return a collection from your stored *function*
>>>
>>>http://download-uk.oracle.com/docs/cd/A87860_01/doc/index.htm
>>>
>>>In other words return a TABLE or a VARRAY from your stored function an=
d=20
>>>see what happens.
>>
>>Right. Or you can return a ref cursor.
>>
>>
>>>I haven't tried this using either of the DB-API modules for Oracle=20
>>>(DCOracle2 and cx_Oracle) so I can't confirm whether it works or not.
>>
>>Both of these work in cx_Oracle. You do have to use non DB-API
>>constructs because the DB-API doesn't cover this very well. It seems to
>>cover returning a result set (and thus using fetchxxx() methods) but
>>does not handle arrays or complex data types at all.
>=20
>=20
> I plan to implement that for the next major release of pyPgSQL. As I
> know the type that a PostgreSQL function returned I can wrap it into an
> appropriate class, if it is a REFCURSOR. So that I can then do:
>=20
>     cursor.callproc("myfunc", 2)
>     sp_cursor =3D cursor.fetchone()[0] # ref. cursor that stored proced=
ure
>                                      # returns
>     for row in sp_cursor.fetchall():
>         # do stuff
>=20
> instead of:
>=20
>     cursor.callproc("myfunc", 2)
>     cursorname =3D cursor.fetchone()[0]
>     cursor.execute('fetch all in "%s"' % cursorname)
>=20
>     for row in cursor.fetchall():
>         # do stuff
>=20
> IOW wrap the returned Cursor object with a special class, that has
> fetchone(), fetchmany(), fetchall(), scroll(), __iter__, next(), close(=
)
> and perhaps something I forgot about :)
>=20
> Any comments? Would this be against the intention of the DB-API?

I think that your second approach is cleaner and also
clearer since it makes the interaction between your Python
program and the database explicit.

BTW, how is the indirection of using a ref cursor different
from setting up the result set within the stored procedure ?
(or doesn't Oracle support this in stored procedures ?)

--=20
Marc-Andre Lemburg
CEO eGenix.com Software GmbH
_______________________________________________________________________
eGenix.com -- Makers of the Python mx Extensions: mxDateTime,mxODBC,...
Python Consulting:                               http://www.egenix.com/
Python Software:                    http://www.egenix.com/files/python/