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

Originally posted by Mark
> 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
Your code is trying to treat vr_service as a variable of type
RECORD(col1,col2) which it is not.

I think this is what you meant to do:

/*
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 (..., ..., ...)'
*/
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;

--
Posted via http://dbforums.com

> 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

Mark wrote:

> 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

Assuming 8i try this:

CREATE OR REPLACE PROCEDURE child (
p_NumRecs IN PLS_INTEGER,
p_return_cur OUT uw_type.t_ref_cursor)
IS

BEGIN
OPEN p_return_cur FOR
'SELECT * FROM all_tables WHERE rownum <= ' || p_NumRecs ;

END child;
/

Assuming 9i try this:

CREATE OR REPLACE PROCEDURE child (
p_NumRecs IN PLS_INTEGER,
p_return_cur OUT SYS_REFCURSOR)
IS

BEGIN
OPEN p_return_cur FOR
'SELECT * FROM all_tables WHERE rownum <= ' || p_NumRecs ;

END child;
/

I think you've overcomplicated things a bit.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

Originally posted by Daniel Morgan
> Assuming 8i try this:
>
> CREATE OR REPLACE PROCEDURE child (
> p_NumRecs IN PLS_INTEGER,
> p_return_cur OUT uw_type.t_ref_cursor)
> IS
>
> BEGIN
> OPEN p_return_cur FOR
> 'SELECT * FROM all_tables WHERE rownum <= ' || p_NumRecs ;
>
> END child;
> /
>
> Assuming 9i try this:
>
> CREATE OR REPLACE PROCEDURE child (
> p_NumRecs IN PLS_INTEGER,
> p_return_cur OUT SYS_REFCURSOR)
> IS
>
> BEGIN
> OPEN p_return_cur FOR
> 'SELECT * FROM all_tables WHERE rownum <= ' || p_NumRecs ;
>
> END child;
> /
>
>
Surely you meant to use bind variables?

OPEN p_return_cur FOR
'SELECT * FROM all_tables WHERE rownum <= :n' USING p_NumRecs;

--
Posted via http://dbforums.com

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>


- Jusung Yang


"Mark" <mark.harris.spam.begone@ukonline.co.uk.spam.begon e> wrote in message news:<3f02e5c1$0$13729$afc38c87@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

> 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;

I was getting an "invalid relational operator" error message.

I had to do the following:


Not ideal, but there you go. Any suggestions on improvements?

Mark

> 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;

I was getting an "invalid relational operator" error message.

I had to do the following:

OPEN vr_service FOR
'SELECT ss.col1'||
', ss.col2'||
'FROM my_view ss '||
'WHERE ss.col1 '||pv_param1||' '||
'AND ss.col2 = :b2 '||
'ORDER BY ss.col1'
USING IN pv_param2
;

This is because pv_param1 holds text like this: IN ('EL', 'FO')

And by using a bind variable, it was trying to put quotes around this (i.e.
'IN ('EL', 'FO')')

Not ideal, but there you go. Any suggestions on improvements?

Mark