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

Andy Todd andy47@halfcooked.com
Sat, 31 Aug 2002 17:10:00 +0100


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/VB
> 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=@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.

Regards,
Andy
-- 
----------------------------------------------------------------------
 From the desk of Andrew J Todd esq - http://www.halfcooked.com