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

[Anthony Tuininga]

On Sat, 2002-08-31 at 10:10, Andy Todd wrote:
> Marcos S=E1nchez Provencio wrote:
> > Hello there.
> >=20
> > 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).
> >=20
> > How can I get it using DBAPI in Oracle? How about PostgreSQL?
> >=20
> > What I want is something like
> >=20
> > --TSQL code--
> >=20
> > create procedure onlyone @id int
> > as
> > select * from mytable where id=3D@id
> >=20
> > --END TSQL code--
> >=20
> > So, for example, I can give permissions to access the data one by one,
> > knowing the id in advance.
> >=20
> >=20
> > _______________________________________________
> > DB-SIG maillist  -  DB-SIG@python.org
> > http://mail.python.org/mailman/listinfo/db-sig
> >=20
>=20
> In Oracle, return a collection from your stored *function*
>=20
> http://download-uk.oracle.com/docs/cd/A87860_01/doc/index.htm
>=20
> In other words return a TABLE or a VARRAY from your stored function and=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.

> The reason I haven't tried it is because if I use stored procedures or=20
> functions I tend to make them pretty self contained and only return a=20
> status field. If you want to get a set of data into your Python program=20
> then just write a select statement and get the set using a cursor.
>=20
> By returning a set of data as a result of a stored function it would=20
> seem you are trying to dome some work in one place and the rest in=20
> another. If you start doing half of a transaction in a stored procedure=20
> and half in Python then only bad things can happen <0.5 wink>

Yes. That can become problematic. In addition, PL/SQL (Oracle's
programming language) has some serious limitations when compared with
Python. I just translated a complex program that I wrote a year or so
ago in PL/SQL to Python and the speedup involved ranges from 40% to
500%! If I had tried some complex combination of PL/SQL and Python I
would have run into some serious problems.

> Of course, YMMV.

Definitely. :-)

> Regards,
> Andy
> --=20
> ----------------------------------------------------------------------
>  From the desk of Andrew J Todd esq - http://www.halfcooked.com
>=20
>=20
> _______________________________________________
> DB-SIG maillist  -  DB-SIG@python.org
> http://mail.python.org/mailman/listinfo/db-sig