Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Using bind vars, ref cursors and dynamic SQL
You may be going about it the wrong way. Is this what you want?
SQL> select * from test1 where c1 like 'a' and c2=1;
C1 C2 SUMC2
-- ---------- ----------
a 1 100 a 1 135 a 1 200 a 1 360
SQL> create or replace function test1_func_(p1 varchar2, p2 number)
return sys_refcursor as
2 x sys_refcursor;
3 begin
4 open x for select * from test1 where c1 like p1 and c2=p2 order
by sumc2;
5 return x;
6 end;
7 /
Function created.
SQL> set autoprint on SQL> variable x refcursor; SQL> exec :x:=test1_func_('a',1);
PL/SQL procedure successfully completed.
C1 C2 SUMC2
-- ---------- ----------
a 1 100 a 1 135 a 1 200 a 1 360
SQL>
"Mark" <mark.harris.spam.begone_at_ukonline.co.uk.spam.begone> wrote in message news:<3f02e5c1$0$13729$afc38c87_at_news.easynet.co.uk>...
> 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,
>
> Mark
Received on Wed Jul 02 2003 - 14:00:02 CDT