Cursor for a loop with dynamic SQL expression

Question

Is there a way to execute a cursor for a loop with a dynamic SQL statement?

If I do not want to declare a record, I can do something like this (only if I declared a cursor ..):

For I in cuSelect Loop dbms_output.put_line(I.NAME); End Loop; 

And I can open the cursor for the dynamic SQL statement:

 Open cuSelect For 'Select * From TAB_X'; Fetch ceSelect Into recSelect; Close cuSelect; 

But for this I must first declare a record.

Now my problem is that I have to open the Cursor for a very large and complex dynamic SQL statement. The structure of the record is unknown. Is there a way to open a variable cursor and iterate over it using an “undeclared” record?

Thank you for your help.

Answer 1

I think you can do what you want with the DBMS_SQL package. You can also check them out:

http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sql.htm#i1025449

For instance:

 declare TYPE curtype IS REF CURSOR; src_cur curtype; curid NUMBER; namevar VARCHAR2(50); numvar NUMBER; datevar DATE; desctab DBMS_SQL.DESC_TAB; colcnt NUMBER; dsql varchar2(1000) := 'select card_no from card_table where rownum = 1'; begin OPEN src_cur FOR dsql; -- Switch from native dynamic SQL to DBMS_SQL package. curid := DBMS_SQL.TO_CURSOR_NUMBER(src_cur); DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab); -- Define columns. FOR i IN 1 .. colcnt LOOP IF desctab(i).col_type = 2 THEN DBMS_SQL.DEFINE_COLUMN(curid, i, numvar); ELSIF desctab(i).col_type = 12 THEN DBMS_SQL.DEFINE_COLUMN(curid, i, datevar); ELSE DBMS_SQL.DEFINE_COLUMN(curid, i, namevar, 50); END IF; END LOOP; -- Fetch rows with DBMS_SQL package. WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP FOR i IN 1 .. colcnt LOOP IF (desctab(i).col_type = 1) THEN DBMS_SQL.COLUMN_VALUE(curid, i, namevar); dbms_output.put_line(namevar); ELSIF (desctab(i).col_type = 2) THEN DBMS_SQL.COLUMN_VALUE(curid, i, numvar); dbms_output.put_line(numvar); ELSIF (desctab(i).col_type = 12) THEN DBMS_SQL.COLUMN_VALUE(curid, i, datevar); dbms_output.put_line(datevar); END IF; END LOOP; END LOOP; DBMS_SQL.CLOSE_CURSOR(curid); end;