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

[Bob Kline]

Marcos S=E1nchez Provencio wrote:

> 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. [...] How can I get it using DBAPI
> in Oracle?

$ cat toraproc.sql
CREATE OR REPLACE PACKAGE tprocpkg AS
    TYPE t_curs IS REF CURSOR;
    PROCEDURE tproc(id_p INT, rs IN OUT t_curs);
END tprocpkg;
/
CREATE OR REPLACE PACKAGE BODY tprocpkg AS
    PROCEDURE tproc(id_p INT, rs IN OUT t_curs) AS
    BEGIN
        OPEN rs FOR SELECT name, account_status
                      FROM tt
                     WHERE id =3D id_p;
    END;
END;
/
$ cat toraproc.py
#!/usr/bin/python

import DCOracle2, mydb

conn =3D DCOracle2.connect(mydb.getConnectionString())
curs =3D conn.cursor()
curs.callproc("tprocpkg.tproc", 42, curs)
name, status =3D curs.fetchone()
print "name is %s; status id %s" % (name, status)
$ ./toraproc.py
name is Doug Adams; status is alive

One caveat: the DCOracle2 implementation deviates from the DBAPI spec,
which calls for the parameters to the procedure to be passed as a single
argument containing a sequence, whereas DCOracle2 expects a separate
argument (as given above) for each procedure parameter.

--=20
Bob Kline
mailto:bkline@rksystems.com
http://www.rksystems.com