Re: Returning rows from a stored procedure

From: Steve Cosner <stevec_at_zimmer.csufresno.edu>
Date: 1997/03/26
Message-ID: <5h9qoe$c5h@info.csufresno.edu>#1/1

In article <Ey6a8BAa8QMzEwWG_at_dataworkshop.demon.co.uk>, Andy Noble <andy_at_dataworkshop.demon.co.uk> 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.

You do it in a package procedure, which then opens a cursor, and on repeated calls, returns multiple rows. Your package needs to do a minimum of 3 things, (which means 3 different procedures): Open the cursor, fetch and return rows, and close the cursor.

Typing from memory here, so beware of errors:

Create or replace
Package Get_Data is
Procedure Topen(VarI in varchar2,Result in out varchar2); Procedure Tclose(Result in out varchar2); Procedure Tfetch(C1 out varchar2,C2 out varchar2

                  Result in out varchar2);

End;

   . <--put the period in col 1 for SQL Plus, delete this comment /
Show errors package Get_data
Create or replace
Package Body Get_data is
  Var1 Varchar2(10);
  Cursor C is Select Col1,Col1 from Tbl1 Where Col1=Var1;

---
 Procedure Topen(VarI in varchar2,Result in out varchar2) is begin
   Var1 := VarI;
   Open C;
 Exception when others then
   Result := 'Topen failed';
 End Topen;
---
 Procedure Tclose(Result in out varchar2) is begin
   Close C;
 Exception when others then
   Result := 'Tclose failed';
 End Tclose;
---
 Procedure Tfetch(C1 out varchar2,C2 out varchar2
                  Result in out varchar2) is
 begin
  Fetch C into C1,C2;
  If C%notfound then Result := 'End of data'; end if;
 exception when others then
    Result := 'Tfetch failed'; End Tfetch;
End;
   . <--put the period in col 1 for SQL Plus, delete this comment
/
Show errors package body Get_data
-----------------------------------------------------------
 
I haven't actually tried a package like this, but there is nothing
unusual in it, so I think it should work.
 
If you wanted to be more dynamic with your own select and where
clause,
you would need to use the DBMS_SQL package, but I think using your own
package might be faster.
 
I hope this gets you started in the right direction.
 
Regards,
Steve Cosner

Received on Wed Mar 26 1997 - 00:00:00 CST

Re: Returning rows from a stored procedure - how?