Re: How to return RESULTSET from Oracle Stored Procedure?

From: Ivars Grinbergs <ivarsg_at_itsystems.lv>
Date: 1997/11/13
Message-ID: <346AB8BA.E0888FB0@itsystems.lv>#1/1

It is possible to execute "SELECT * FROM TABLE" inside stored procedure, but you must use INTO clause ;)

To return result set, this might be useful: create or replace package my_types as

   subtype ty_rec is <my_table>%rowtype;    type ty_tab is table of ty_rec index by binary_integer; end my_types;

create or replace function sel_<may_table>(<some params>)

   return my_types.ty_tab as

      cursor c is select * from <my_table>;
      v_row my_types.ty_rec;
      v_ret my_types.ty_tab;
      v_idx binary_integer := 1;
begin
      open c;
      loop
         fetch c into v_row;
         exit when c%notfound;
         v_ret(v_idx):=v_row;
         v_idx:=v_idx+1;
      end loop;
      close c;
      return v_ret;

end sel_<may_table>;

As you can see, function will return record set. Note, that you need Oracle 7.3 (PL/SQL Release 2.3) :(
Hope this helps.

Sincerely,
Ivars Grinbergs

Vladimir Agajanov wrote:

> Hello,everybody!
>
> I really do not know where to look for the answer that I have about Oracle.
>
> THe question is: Why it is not possible to execute SELECT * FROM TABLE
> inside the body of Sored procedure?
> From Sybase I know that stored procedure accepts IN, OUT, IN OUT parameters
> but it also has a resultset. Then how can I achieve the same resultset on
> the client side from Oracle stored procedure?
>
> Any suggestions will be greately appreciated!
>
> Vladimir
Received on Thu Nov 13 1997 - 00:00:00 CST