今天发现一个PLSQL脚本报ORA-01002和ORA-06512：
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 8
原来，脚本里包含两个表的两个cursor，然后分别对每个表打开cursor然后对此表做一些dml，每隔若干行rollback（这是因为此PLSQL脚本还在测试阶段，所以需要rollback）。
然而，问题就出在，对表A作为dml后，在循环完成的最后没有写“rollback”，这就导致了对表A的一些dml在脚本打开表B的cursor前，并没有被rollback，于是报此错。
举个简单的例子，例如cursor取出表A的1004行，然后每隔100行rollback一次，那么最后还有对最后4行的dml没有被rollback，接着打开另一个cursor时报错。
这个错，跟Bug 3039457: PL/SQL: FETCH FAILS WITH ORA-1002 AFTER ROLLBACK有一定联系。
之所以，我提出这个错误号01002，是因为OERR ORA 1002的三个解释中都不包含这个bug。
oerr ora 1002
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.
下面做一个很简单的实验解释这个bug。
准备脚本test1.sql:
DECLARE
cursor c1 is select rowid
from test1
where ROWNUM <= 50000;
cnt number(38);
begin
cnt := 0;
for c1rec in c1 loop
delete from test1
where rowid = c1rec.rowid;
cnt := cnt + 1;
if mod(cnt, 77) = 0 then
rollback;
end if;
end loop;
rollback;
end;
/
如上，我每隔77行rollback一次，对于一共50000行数据，肯定最后会剩下一些delete没有被rollback。如果我接着再run test1.sql，就会触发这个bug：
SQL> @test1
PL/SQL procedure successfully completed.
SQL> @test1
DECLARE
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 8
SQL> @test1
PL/SQL procedure successfully completed.
SQL> @test1
DECLARE
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 8
解决方法很简单，就是在test1.sql的最后加上一句rollback就行了。
SQL> @test1
PL/SQL procedure successfully completed.
SQL> rollback;
Rollback complete.
SQL> @test1
PL/SQL procedure successfully completed.
