Invalid cursor c1 when close for-in loop

Question

FOR i in c1 LOOP
  UPDATE....
  COMMIT;
END LOOP;
CLOSE c1;

Above block hit error: Invalid c1 when CLOSE c1, updates are running well.Is it possible the cursor closed after loop is ended?

You are asking the same question again and again: https://stackoverflow.com/questions/49869208/fetch-subquery-value-from-cursor-to-parameter-in-pl-sql https://stackoverflow.com/questions/49882662/dynamic-sql-syntax-using-execute-immediate https://stackoverflow.com/questions/49890243/retrieve-number-of-rows-updated https://stackoverflow.com/questions/49871112/oracle-execute-immediate-not-executing-without-any-error Why do you not simply take the answer of https://stackoverflow.com/questions/49871112/oracle-execute-immediate-not-executing-without-any-error ? — Wernfried Domscheit, Apr 18 '18 at 08:16

score 0 · Accepted Answer · answered Apr 18 '18 at 08:13

0

When you use syntax FOR xxx in c1 LOOP then the cursor is closed automatically at the end. You cannot close it twice.

Just skip CLOSE c1;

answered Apr 18 '18 at 08:13

Wernfried Domscheit

54,457
9
76
110

Good, it tell why the next cursor `c2` doesn't execute if not skip `CLOSE c1;` – user2102665 Apr 18 '18 at 08:18
You don't mention any `c2` cursor in your question. – Wernfried Domscheit Apr 18 '18 at 08:19
i was just added and found that `c2` doesn't execute.Previously `c1` is executed but display error as invalid. Once skip `CLOSE c1` both cursors are working fine..many thanks! – user2102665 Apr 18 '18 at 08:21

score 0 · Answer 2 · answered Apr 18 '18 at 08:17

You have two options:

Option 1:

SQL> declare
  2    cursor c1 is select * from dept;
  3    c1r c1%rowtype;
  4  begin
  5    open c1;
  6    loop
  7      fetch c1 into c1r;
  8      exit when c1%notfound;
  9      dbms_output.put_line(c1r.dname);
 10    end loop;
 11    close c1;
 12  end;
 13  /
ACCOUNTING
RESEARCH
SALES
OPERATIONS

PL/SQL procedure successfully completed.

Option 2:

SQL> begin
  2    for cur_r in (select * from dept) loop
  3      dbms_output.put_line(cur_r.dname);
  4    end loop;
  5  end;
  6  /
ACCOUNTING
RESEARCH
SALES
OPERATIONS

PL/SQL procedure successfully completed.

SQL>

As you can see, both produce the same result, but the 2nd one is much easier to maintain.

In Option 1 you have to:

declare cursor
declare cursor variable
open cursor
fetch
take care about exiting the loop
close cursor

In Option 2, you have to:

declare a cursor
Oracle does everything else for you

Pick one you find more appropriate (I'd pick #2, most of the time).

As of the error you got: there's nothing to be closed; as I've said, Oracle does it for you. Remove the CLOSE command.

Invalid cursor c1 when close for-in loop

2 Answers2

Option 1:

Option 2: