Re: Using bind vars, ref cursors and dynamic SQL

> Hello,
>
> I'm trying to return values from my database using ref cursors, dynamic SQL
> and bind variables, but I cannot seem to get the syntax right.
>
> It looks like this currently:
>
> /*
> rec_services is declared thusly: TYPE rec_services IS REF CURSOR;
> */
> FUNCTION my_function
> (pv_param1 IN VARCHAR2
> ,pv_param2 IN VARCHAR2
> )
> RETURN rec_services
> IS
> vr_service rec_services;
> BEGIN
>
> /*
> pv_param1 contains 'LIKE (..., ..., ...)'
> */
> EXECUTE IMMEDIATE
> 'DECLARE '||
> 'CURSOR cur_records IS '||
> 'SELECT ss.col1'||
> ', ss.col2'||
> 'FROM my_view ss '||
> 'WHERE ss.col1 :b1 '||
> 'AND ss.col2 = :b2 '||
> 'ORDER BY ss.col1;'||
> 'BEGIN '||
> 'OPEN cur_records;'||
> 'FETCH cur_records '||
> 'INTO :b3'||
> ', :b4;'||
> 'CLOSE cur_records;'||
> 'END;'
> USING IN pv_param1
> , IN pv_param2
> , OUT vr_service.col1
> , OUT vr_service.col2
> ;
>
> RETURN vr_service;
>
> END my_function;
>
> The error I am getting is:
>
> PLS-00487: Invalid reference to variable 'VR_SERVICE'
>
> Thanks for any assistance,
 

I am not really sure what you're after, but maybe this helps:

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

Rene Nyffenegger

-- 
  Projektleitung und Entwicklung in Oracle/C++/C# Projekten
  http://www.adp-gmbh.ch/cv.html