How to create cursor inside procedure body in plsql

Question

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?

score 6 · Accepted Answer · answered Aug 28 '12 at 11:06

6

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;
/

answered Aug 28 '12 at 11:06

schurik

7,798
2
23
29

i think it is good alternative for the about procedure. it will simply my code – satheesh Aug 28 '12 at 11:35
What if I need to create multiple cursor inside loop? – Deckard Nov 17 '15 at 04:32

score 1 · Answer 2 · answered Aug 28 '12 at 11:03

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.

Yes you are correct, i simplified the code for the question. i understood for loop we can't use in this scenario — satheesh, Aug 28 '12 at 11:33

score 0 · Answer 3 · edited Dec 19 '18 at 11:05

0

As far as I'm aware you can't use a FOR loop with a cursor variable or "ref cursor". FOR loops are only for use with hard-coded SQL statements or cursors.
See the section on cursor variable restrictions here where it explicitly states "...you cannot reference a cursor variable in a cursor FOR loop".

edited Dec 19 '18 at 11:05

giannis christofakis

8,201
4
54
65

answered Aug 28 '12 at 11:01

Bob Jarvis - Слава Україні

48,992
9
77
110

How to create cursor inside procedure body in plsql

3 Answers3

Linked