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

[Marcos Sánchez Provencio]

El sáb, 31-08-2002 a las 18:10, Andy Todd escribió:
> Marcos Sánchez Provencio wrote:
> > 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/V=
B
> > using a rather contrived mechanism which involved putting the procedure
> > in a package and returning some type (cursor, I think).
> > 
> > How can I get it using DBAPI in Oracle? How about PostgreSQL?
> > 
> > What I want is something like
> > 
> > --TSQL code--
> > 
> > create procedure onlyone @id int
> > as
> > select * from mytable where id=3D@id
> > 
> > --END TSQL code--
> > 
> > So, for example, I can give permissions to access the data one by one,
> > knowing the id in advance.
> > 
> > 
> > _______________________________________________
> > DB-SIG maillist  -  DB-SIG@python.org
> > http://mail.python.org/mailman/listinfo/db-sig
> > 
> 
> 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.
> 
> 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.
> 
> The reason I haven't tried it is because if I use stored procedures or 
> functions I tend to make them pretty self contained and only return a 
> status field. If you want to get a set of data into your Python program 
> then just write a select statement and get the set using a cursor.
> 
> By returning a set of data as a result of a stored function it would 
> seem you are trying to dome some work in one place and the rest in 
> another. If you start doing half of a transaction in a stored procedure 
> and half in Python then only bad things can happen <0.5 wink>
> 
> Of course, YMMV.
> 
I mean the case in which the objective of the procedure (the name
function would be more correct, I agree) is to return the data, not to
do any data processing at all. It is very useful to return data to
Crystal Reports (client/server) and save bandwidth by doing _all_ the
data processing in the server, for example. I also have two versions of
an 'application explorer', in VB and Python, and I try to move all the
functions into the data server to make them completely compatible.

I will try the various options offered in the list and tell you about
the results. Thank you.

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