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