ORA

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

    然而，问题就出在，对表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 100201002, 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:

DECLAREcursor c1 is select rowid               from test1              where ROWNUM @test1

PL/SQL procedure successfully completed.

SQL> @test1DECLARE*ERROR at line 1:ORA-01002: fetch out of sequenceORA-06512: at line 8

SQL> @test1

PL/SQL procedure successfully completed.

SQL> @test1DECLARE*ERROR at line 1:ORA-01002: fetch out of sequenceORA-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.

出处：http://www.hzhcontrols.com/原文：http://www.hzhcontrols.com/new-322405.html本文版权归原作者所有欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利