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

Gerhard Häring haering_postgresql@gmx.de
Sat, 31 Aug 2002 13:54:41 +0200

* Marcos Sánchez Provencio <msanchez@grupoburke.com> [2002-08-31 12:40 +0200]:
> Hello there.
> In TSQL (the procedural language used in Sybase and MSSQL) it is very
> simple to return rows from a procedure and use it as a query issued by
> the user dinamically. I have managed to do get the same effect in
> ADO/VB using a rather contrived mechanism which involved putting the
> procedure in a package and returning some type (cursor, I think).

Cool - I have to keep in mind that this is possible for Oracle, too. I'm
in a project where we just write in a table (not even temporary) that
has the necessary fields, plus a SESSIONID field. I do the following,

1) call stored procedure
2) select from table where sessionid = userenv('sessionid')
3) delete from table where sessionid = userenv('sessionid')

> How can I get it using DBAPI in Oracle? How about PostgreSQL?

http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html - Returning Cursors.

It's currently not so easy in pyPgSQL, but I plan to introduce a little
magic for pyPgSQL 3.0:


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