Skip to Main Content
  • Questions
  • how to store output of dynamic sql into a cursor.

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: June 29, 2016 - 10:55 am UTC

Last updated: June 30, 2016 - 4:06 am UTC

Version: 11g

Viewed 1000+ times

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

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library