Skip to Main Content
  • Questions

  • how to store output of dynamic sql into a cursor.

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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