You Asked
Hi tom,
I want to know how to declare a cursor which contains the data obtained by dynamically executing a query,where the query was taken from another cursor.
CURSOR c1 IS
SELECT QRY from QUERY_MASTER WHERE Q_ID IN (1,2);
where QRY(an SQL statement) contains data as
1) select stu_id,stu_name,stu_age,stu_city from STUDENT
2) select emp_id,emp_name,emp_company,emp_age,emp_city,emp_zip_code from EMPLOYEE
now I want to create another cursor c2 which gives the data, when each output of c1 is fired individually.
and Connor said...
SQL> drop table T purge;
Table dropped.
SQL>
SQL> create table T ( sql_stmt varchar2(1000));
Table created.
SQL>
SQL> insert into t values ('select * from tab');
1 row created.
SQL>
SQL> create or replace
2 procedure P(rc in out sys_refcursor) is
3 s varchar2(1000);
4 begin
5 select sql_stmt into s from t;
6
7 open rc for s;
8 end;
9 /
Procedure created.
SQL>
SQL> variable rc refcursor
SQL> exec p(:rc)
PL/SQL procedure successfully completed.
SQL> print rc
V_TEST VIEW
V4 VIEW
V3 VIEW
V2 VIEW
V1 VIEW
V VIEW
T_TGT TABLE
T_SOURCE TABLE
T_PAR TABLE
T_B TABLE
T_A TABLE
TEST_VIEW VIEW
TEST_TAB_X TABLE
...
55 rows selected.
SQL>
SQL>
Hope this helps.
Is this answer out of date? If it is, please let us know via a Comment