Oracle: Cursor in a Procedure

Question

Please help me with my Oracle procedure question. In my package I have a procedure and I want to declare a cursor which uses dynamic queries.

type DocList IS REF CURSOR;
curDocList DocList;
.
.
.
OPEN curDocList FOR v_sql;   --v_sql has dynamic sql
...
FOR recDocStatusList IN curDocList 
      LOOP

When I am trying to use - FOR recDocStatusList IN curDocList it is giving me the error: [Error] PLS-00221 (2262: 34): PLS-00221: 'CURDOCLIST' is not a procedure or is undefined.

Please help me correcting the issue.

Answer 1

If you're willing to iterate the cursor, you can perform it without using a FOR loop.

Option 1:

  LOOP
    FETCH curDocList INTO recDocStatusList;
    EXIT WHEN curDocList%NOTFOUND;
    ...
  END LOOP;

Option 2:

FETCH curDocList INTO recDocStatusList;
WHILE (curDocList%FOUND)
LOOP
    ...
    FETCH curDocList INTO recDocStatusList;
END LOOP;

A few annotations:

• Remember to declare recDocStatusList at first.
• Both options are following the OPEN ... FOR line.
• Don't forget to CLOSE the cursor at the end.