[DB-SIG] How to return results from stored procedures in Oracle
Sat, 31 Aug 2002 21:06:25 +0200
* Anthony Tuininga <email@example.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:
sp_cursor = cursor.fetchone() # ref. cursor that stored procedure
for row in sp_cursor.fetchall():
# do stuff
cursorname = cursor.fetchone()
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?
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')))