How to create cursor inside procedure body in plsql
Solution 1
you cannot reference a cursor variable in a cursor FOR loop
but you can use the select statment direct:
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;
/
Solution 2
You can't use the FOR <row> IN <cursor> LOOP
syntax with dynamic SQL; see the example in the documentation, which shows the method you're using when that code is commented out anyway.
Your example doesn't need to be dynamic at all, but I assume you've just simplified it for this question. If it had a placeholder then there would be nowhere to set its value. If you had:
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 FOR ... IN ...
version doesn't give you anywhere to assign a value to the days
placeholder. You have to use the dynamic OPEN
to do that:
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 are just building the query string dynamically, but the restriction still applies.
Comments
-
satheesh over 3 years
I want create cursor inside procedure body dynamically also i have to use for loop instead of below code. i did the dynamic cursor but i cannot use the 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 am using commented code(for loop), i getting error
PLS-00221: is not a procedure or is undefined.
Is it possible using for loop in dynamic cursor?
-
satheesh over 11 yearsYes you are correct, i simplified the code for the question. i understood for loop we can't use in this scenario
-
satheesh over 11 yearsi think it is good alternative for the about procedure. it will simply my code
-
Deckard over 8 yearsWhat if I need to create multiple cursor inside loop?