ORA-1002 From Pro*C/C++ With A "FOR UPDATE CURSOR" (With Samples) (Doc ID 92404.1)

Last updated on MAY 13, 2013

Applies to:

Precompilers - Version: 8.0.4.0 to 11.1.0.6 - Release: to 11.1
Information in this document applies to any platform.
***Checked for relevance on 04-May-2010***

Symptoms

Running a Pro*C application using a FOR UPDATE CURSOR results in an ORA-1002 on FETCH after a COMMIT/ROLLBACK.

 


Example:
--------
ora1002.sql
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CREATE TABLE ora1002tab AS SELECT empno, ename FROM emp;

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

ora1002.pc
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
/****************************************************************
* Sample Program ora1002.pc: *
* *
* This program demonstrates getting an ORA-1002 while fetching *
* from a FOR UPDATE cursor after a COMMIT *
* *
* To build: *
* make -f demo_proc.mk build EXE=ora1002 OBJS=ora1002.o *
****************************************************************/

#include <stdio.h> /* stdio header */
#include <stdlib.h> /* stdlib header */
#include <sqlca.h> /* sqlca header */

void sqlerr( void ); /* handles unrecoverable errors */

void main()
{
EXEC SQL BEGIN DECLARE SECTION;
char *uid= "SCOTT/TIGER";
int empnum;
EXEC SQL END DECLARE SECTION;

EXEC SQL WHENEVER SQLERROR DO sqlerr( );
EXEC SQL CONNECT :uid;
printf( "\nConnected to ORACLE\n" );

EXEC SQL DECLARE c1 CURSOR FOR
SELECT empno FROM ora1002tab FOR UPDATE;
EXEC SQL OPEN c1;

EXEC SQL WHENEVER NOT FOUND DO break;
while( 1 == 1 )
{
EXEC SQL FETCH c1 INTO :empnum;
EXEC SQL UPDATE ora1002tab SET empno = :empnum + 50
WHERE CURRENT OF c1;
EXEC SQL COMMIT;
} /* end while */

EXEC SQL CLOSE c1;
EXEC SQL COMMIT WORK RELEASE; /* logoff database */
exit(0);
} /* end main( ) */

void sqlerr( void )
{
EXEC SQL WHENEVER SQLERROR CONTINUE;

printf("\nORACLE error detected:\n");
printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);

EXEC SQL ROLLBACK RELEASE;
exit(1);
} /* end sqlerr( ) */

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Results:
--------

Connected to ORACLE

ORACLE error detected:

ORA-01002: fetch out of sequence

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms