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!

Proc code throw ORA-01002

bisalSep 4 2017 — edited Sep 5 2017

There is a part code of proc,

...

EXEC SQL DECLARE c1 CURSOR FOR

SELECT A, B

FROM TBL

WHERE X=:x and Y=:y;

EXEC SQL OPEN c1;

while(i<700){

    EXEC SQL FETCH c1 INTO :a,:b;

if (sqlca.sqlcode !=0){

   printf("sql error11: [%d][%s]\n",sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc);

   EXEC SQL CLOSE c1;

}

}

...

When the connection between proc server and Oracle 9i server is down because of Oracle 9i server is down, this code print "ORA-01002: fetch out of sequence". As far as I know, the failure of database connection will throw ORA-03113 and ORA-03114. Others proc code like this code, they will throw ORA-03113 and ORA-03114. I doesn't watch there is any difference between them.

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.

Could you give me some advice? Thanks.

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 3 2017
Added on Sep 4 2017
8 comments
419 views