Hi Tech Gurus,
I have one requirement in which i have to fetch the select query from a table which i'll use to generate a report using pl/sql stored procedure.
i tried doing it using ref cursor as follows:( please note that the below code is just a sample code of my actual code)
Create or replace PROCEDURE PROC1
AS
TYPE T_CURSOR IS REF CURSOR;
v_query patent.query%type;
Proc_nm patent.procedure_name%type;
VID patent.ID%type;
a varchar:= '';
b varchar:='';
CURSOR C
IS
SELECT Query,
FROM patent where flag=1;
cur_ref T_CURSOR;
BEGIN
open C;
loop
fetch c into V_query;
exit when c%notfound;
end loop;
OPEN cur_ref FOR v_query ; --------- error statement of executable code
LOOP
FETCH cur_ref bulk INTO a,b,c;
EXIT
WHEN cur_ref%NOTFOUND;
END LOOP;
close c;
END PROC1;
-- query is the column of the patent table and will contain a generic query like select x,y,z from sample;
The above code compiled successfully. but while executing the code i got an error ::ora-00900 invalid sql statement" .
my question is :can we use v_query for fetching the rows as mentioned above ? as i can't directly hardcode the query in place of v_query as it should come from the table itself.
Please provide your suggestions.