Re: Returning rows from a stored procedure - how?

> In article <Ey6a8BAa8QMzEwWG_at_dataworkshop.demon.co.uk>,
> Andy Noble <> wrote:
> >We are using Oracle Workgroup Server on an NT box,
> >and developing a front end application in VB v4 16-bit
> >(no Remote Data Objects (RDO) available).
> >
> >I would like to store all SQL used by the application
> >in PL/SQL stored procedures. This works fine for UPDATES,
> >DELETES and INSERTS.
> >
> >However I cannot find ANY documentation telling me how to
> >return a result set from a SELECT statement back to the
> >front end application. I can only find comments that it is
> >possible.
> >
> >I would be grateful if someone could supply me with an example
> >of a stored procedure that returns rows to my front end app.
>

We have run into the exactly problem you are having. Comparing to Sybase or MS SQL Server's stored procedure, Oracle's can not work the same way. Based on our research, there are two ways to do this. One is to use Cursor Variable, which has been intriduced since Oracle 7.2. However, I don't think VB4 supports this. Another way is a workaround. We basically output all the results from all the procedures to a common table with an unique identifier, then do a select out of that table and delete the result set from the common table. It works like this:

1. call stored procedure (stored proc inserts result set to a table)
2. select col1,col2,col3.. from table where sessionid = SESSIONID
3. delete from table where sessionid = SESSIONID;

I know this is very UGLY and involves quite a bit of overhead but it is the easiest to implement and it works.

Althogh I don't know the downside of this feature, I do think it is very convenient and should be implemented in Oracle.

Hope this helps.

-- 
L. Phil Tsao <ptsao_at_sequeltech.com>
Sequel Technology Corp.
http://www.sequeltech.com