[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