Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Invalid cursor after 100 rollback

EarthlessJan 20 2020 — edited Jan 20 2020

Hi, I know that is not a good idea to commit/rollback inside a PL/SQL cursor but I need to use some centralized procedure that internally could commit or rollback.

I've found a somewhat strange behavior, it seems that if you open a cursor on a table with an ongoing transaction (even for the session itselfs) the cursor invalidates after exactly 100 rollback.

See the example (I know this example hasn't any logic, my real world case is more complicated than this, it's just to show the error ):

create table app_adr_test (campo number, valore varchar2(100));

begin

delete from app_adr_test;

for t in 1..200 loop

  insert into app_adr_test values(t,'ciao');

end loop;

commit;

end;

/

select *

from app_adr_test;

UPDATE APP_ADR_TEST

SET valore = 'PLUTO'

WHERE ROWNUM < 2;

SET SERVEROUTPUT ON;

declare

cursor cur is

  select * from app_adr_test ORDER BY CAMPO;

begin

for t in cur loop

  DBMS_OUTPUT.PUT_LINE(T.CAMPO);

  --update app_adr_test set valore = 'ppp'||t.campo where campo = t.campo;

  rollback;

end loop;

end;

/

Last block output is:

Errore con inizio alla riga : 21 nel comando -

declare

cursor cur is

  select * from app_adr_test ORDER BY CAMPO;

begin

for t in cur loop

  DBMS_OUTPUT.PUT_LINE(T.CAMPO);

  --update app_adr_test set valore = 'ppp'||t.campo where campo = t.campo;

  rollback;

end loop;

end;

Report error -

ORA-01002: cursore non valido

ORA-06512: a line 5

ORA-06512: a line 5

01002. 00000 -  "fetch out of sequence"

*Cause:    This error means that a fetch has been attempted from a cursor

           which is no longer valid.  Note that a PL/SQL cursor loop

           implicitly does fetches, and thus may also cause this error.

           There are a number of possible causes for this error, including:

           1) Fetching from a cursor after the last row has been retrieved

           and the ORA-1403 error returned.

           2) If the cursor has been opened with the FOR UPDATE clause,

           fetching after a COMMIT has been issued will return the error.

           3) Rebinding any placeholders in the SQL statement, then issuing

           a fetch before reexecuting the statement.

*Action:   1) Do not issue a fetch statement after the last row has been

           retrieved - there are no more rows to fetch.

           2) Do not issue a COMMIT inside a fetch loop for a cursor

           that has been opened FOR UPDATE.

           3) Reexecute the statement after rebinding, then attempt to

           fetch again.

1

2

3

[...]

100

I don't have the error if I open the cursor for only 99 rows

cursor cur is

  select * from app_adr_test WHERE ROWNNUM < 100 ORDER BY CAMPO;

Please note that is not even necessary that the cursor does something on the table.

Why this happens? Why 99 rows are ok and 100 not? There is written limit?

Tested on different environments on different version (10.2,11.2,12.2) all gives the same results.

Please also note that is not the rollback itself inside the cursor but the fact that a trasaction is open before the cursor is open.

If I put a commit (or rollback) just before opening the cursor it works..

[...]

begin

rollback;

for t in cur loop

  DBMS_OUTPUT.PUT_LINE(T.C [...]

thanks

Comments
Post Details
Added on Jan 20 2020
6 comments
915 views