René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Returning a resultset with PL/SQL

create or replace package dummy as
  type rec_services is ref cursor;
end dummy;
/

create or replace package body dummy as
 
end;
/

create table my_view (
  col1 number,
  col2 varchar2(10)
);

insert into my_view values (1,'one');
insert into my_view values (2,'two');
insert into my_view values (3,'three');
insert into my_view values (1,'un');
insert into my_view values (2,'deux');
insert into my_view values (3,'trois');
insert into my_view values (1,'eins');
insert into my_view values (2,'zwei');
insert into my_view values (3,'drei');


create or replace    
FUNCTION my_function (pv_param1  IN number ,pv_param2  IN VARCHAR2)
    RETURN dummy.rec_services
    IS
        vr_service  dummy.rec_services;
    BEGIN

    open 
      vr_service for
            'SELECT    ss.col1 '      ||
            ',         ss.col2 '      ||
            'FROM      my_view ss '   ||
            'WHERE     ss.col1 = :b1 '||
            'AND       ss.col2 > :b2 '||
            'ORDER BY  ss.col1'       
        USING IN pv_param1
        ,     IN pv_param2;

        RETURN vr_service;

END my_function;
/

var c refcursor

begin
  select my_function(1,'deer') into :c from dual;
end;
/

print c