CODEWITHSUNDEEP
oracle-databasestored-procedurescursorpackage
Justin Samuel
Justin Samuel

Reputation: 1083

Oracle: Cursor in a Procedure

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.

Upvotes: 1

Views: 140

Answers (1)

Neria Nachum
Neria Nachum

Reputation: 1529

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.

Upvotes: 1

Related Questions