Home » SQL & PL/SQL » SQL & PL/SQL » Getting record set back from stored proc
Getting record set back from stored proc [message #1881] Fri, 07 June 2002 03:19 Go to next message
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 Go to previous message
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.
Previous Topic: SQL Variables?
Next Topic: Trigger for delete please help!!
Goto Forum:
  


Current Time: Tue May 21 08:49:11 CDT 2024