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