Re: Creating a stored procedure or function to return multiple records

If you are returning the data to another PLSQL piece of code you can use either a REF CURSOR or PLSQL table types (even tables of RECORDS). If you are returing it to another application written in Java, C, etc. then you need to use the REF CURSOR option.

create or replace package types as
  type sqlcur is REF cursor;
end;
/

create or replace function test return types.sqlcur as   c1 types.sqlcur;
begin
  open c1 for select loc from dept;
  return c1;
end;
/

create or replace procedure calltest as   c1 types.sqlcur;
  val varchar2(20);
begin
  c1 := test;
  loop
    fetch c1 into val;
    exit when c1%NOTFOUND;
    dbms_output.put_line(val);
  end loop;
end;
/
Received on Fri May 21 1999 - 09:31:15 CDT