Getting record set back from stored proc [message #1881] |
Fri, 07 June 2002 03:19 |
Mike Nibeck
Messages: 49 Registered: May 2002
|
Member |
|
|
OK, just learning PL/SQL and stored procedures. I have written a simple sp that returns 3 atomic values, using the 'SELECT INTO' syntax.
Now, I want to be able to return an entire record set back to the caling procedure. What's the best way of doing that?
Oracle 8i
- Mike
|
|
|
Re: Getting record set back from stored proc [message #1884 is a reply to message #1881] |
Fri, 07 June 2002 05:35 |
|
Mahesh Rajendran
Messages: 10707 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
in order to get the resultset back, you need to make use of out parameters as shown.
<b>here a is input and b is output parameters.</b>
SQL> create or replace procedure p1
2 (a in number,
3 b out number)
4 as
5 begin
6 select sal into b from emp where empno=a;
7 end;
8 /
Procedure created.
<b> out parameters are meant for front end apps.
but inorder to get these values in sql*plus u need to write another unit to read this. something like this.</b>
SQL> ed
Wrote file afiedt.buf
1 create or replace procedure p2
2 as
3 out number;
4 begin
5 p1(7788,out);
6 dbms_output.put_line(out);
7* end;
SQL> /
Procedure created.
SQL> set serveroutput on
SQL> exec p2
3000
PL/SQL procedure successfully completed.
|
|
|