How to create a cursor inside a procedure body in plsql

Question

I want to create dynamic inside the body of the procedure dynamically , and I have to use for the loop instead of the code below. I made a dynamic cursor, but I cannot use a for loop.

PROCEDURE myprocedure AS LV_TEST_CUR SYS_REFCURSOR; LV_QUERY VARCHAR2(200); LV_DATE DATE; BEGIN LV_QUERY:='select sysdate as mydate from dual'; OPEN LV_TEST_CUR FOR LV_QUERY; /*FOR CUR_VAR IN LV_TEST_CUR LOOP dbms_output.put_line(CUR_VAR.mydate); end LOOP; */ LOOP FETCH LV_TEST_CUR INTO LV_DATE; EXIT WHEN LV_TEST_CUR%NOTFOUND; DBMS_OUTPUT.PUT_LINE(LV_DATE); END LOOP; CLOSE LV_TEST_CUR; END myprocedure; 

if i use commented code (for loop) i get error

PLS-00221: not a procedure or undefined.

Is it possible to use for a loop in a dynamic cursor?

Answer 1

you cannot reference a cursor variable in a FOR loop cursor

but you can directly use the select function:

 create or replace PROCEDURE myprocedure AS LV_TEST_CUR SYS_REFCURSOR; LV_QUERY VARCHAR2(200); LV_DATE DATE; BEGIN FOR CUR_VAR IN (select sysdate as mydate from dual) LOOP dbms_output.put_line(CUR_VAR.mydate); end LOOP; END myprocedure; / 

Answer 2

Syntax FOR <row> IN <cursor> LOOP cannot be used with dynamic SQL; see the example in the documentation , which shows the method you use when this code is commented out anyway.

Your example does not have to be dynamic, but I assume that you just simplified it for this question. If he had a placeholder, then there would be no way to establish its value. If you have:

 LV_QUERY:='select sysdate - :days as mydate from dual'; FOR CUR_VAR IN LV_TEST_CUR LOOP dbms_output.put_line(CUR_VAR.mydate); END LOOP; 

... then the version of FOR ... IN ... does not give you a place to assign a value to the days placeholder. You must use dynamic OPEN for this:

 LV_QUERY:='select sysdate - :days as mydate from dual'; -- pass '1' as the bind variable OPEN LV_TEST_CUR FOR LV_QUERY USING 1; LOOP FETCH LV_TEST_CUR INTO LV_DATE; EXIT WHEN LV_TEST_CUR%NOTFOUND; DBMS_OUTPUT.PUT_LINE(LV_DATE); END LOOP; CLOSE LV_TEST_CUR; 

Of course, you may not need a placeholder and just builds the query string dynamically, but the restriction still applies.

Answer 3

As far as I know, you cannot use a FOR loop with a cursor variable or a "ref cursor". FOR loops are for use only with hard-coded SQL statements or cursors.
See the section on the limitations of the cursor variable here, where it explicitly states: “you cannot reference the cursor variable in the FOR cursor loop”.