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

Gerhard Häring haering_postgresql@gmx.de
Sat, 31 Aug 2002 21:06:25 +0200


* Anthony Tuininga <anthony@computronix.com> [2002-08-31 12:33 -0600]:
> 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 and 
> > 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 
> > (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.

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:

    cursor.callproc("myfunc", 2)
    sp_cursor = cursor.fetchone()[0] # ref. cursor that stored procedure
                                     # returns
    for row in sp_cursor.fetchall():
        # do stuff

instead of:

    cursor.callproc("myfunc", 2)
    cursorname = cursor.fetchone()[0]
    cursor.execute('fetch all in "%s"' % cursorname)

    for row in cursor.fetchall():
        # do stuff

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 :)

Any comments? Would this be against the intention of the DB-API?

Gerhard
-- 
mail:   gerhard <at> bigfoot <dot> de       registered Linux user #64239
web:    http://www.cs.fhm.edu/~ifw00065/    OpenPGP public key id AD24C930
public key fingerprint: 3FCC 8700 3012 0A9E B0C9  3667 814B 9CAA AD24 C930
reduce(lambda x,y:x+y,map(lambda x:chr(ord(x)^42),tuple('zS^BED\nX_FOY\x0b')))