今天发现一个PLSQL脚本报ORA-01002和ORA-06512： ERROR at line 1:ORA-01002: fetch out of sequenceORA-06512: at line 8     原来，脚本里包含两个表的两个cursor，然后分别对每个表打开cursor然后对此表做一些dml，每隔若干行rollback（这是因为此PLSQL脚本还在测试阶段，所以需要rollback）。

今天发现一个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.

delete in ST05 trace - deletion will also lead to many DB access first 修改PostgreSQL字段长度导致cached plan must not change result type错误 导入数据时遇到IMP-00032，看了错误信息[oracle@test ~]$ oerr imp 0003200032, 00000, "SQL statement exceeded buffer length"// *Cause:  The buffer was too small for the SQL statement being read.