ANY SOLUTIONS WELCOME!!!!
This 'C' program demonstrates a difference in behaviour between ORACLE 7
and ORACLE 8 when turning on autocommit and fetching from a cursor that
is doing a select for update.
This behaviour was demonstrated on Windows 2000 running ORACLE
version 188.8.131.52.0 and shown to not be present on Windows NT4.0 running
ORACLE 184.108.40.206.1 The problem also exists on various UNIX platforms.
connects to a database
turns on autocommit
creates a cursor
parses a statement that does a 'select for update'
binds a variable
executes the statement
attempts to fetch on the cursor
The end result on ORACLE 8 is that an ORA-01002 error is produced on
The output is:
Connecting to ORACLE as scott/tiger ...OK
Turning on autocommit... OK
Creating cursor... OK
Parsing statement ...OK
Defining output variable...OK
ORA-01002: fetch out of sequence
Processing OCI function OFETCH, OFEN
When run on ORACLE 7 the fetch succeeds
To run this example you will need to:
1. make the binary
On ORACLE 8 issue the following -
cl -I%ORACLE_HOME%\oci\include -I. -D_DLL -D_MT eg.c /link /LIBPATH:%ORACLE_HOME%\oci\lib\msvc oci.lib kernel32.lib msvcrt.lib /nod:libc
On ORACLE 7 issue the following -
cl /c -I%ORACLE_HOME%/oci73/include /I. eg.c
link eg.obj libc.lib kernel32.lib %ORACLE_HOME%\oci73\lib\msvc\ociw32.lib /out:eg.exe
2. create the ORACLE table and populate it with one row
CREATE TABLE TEST (FIELD1 CHAR(10));
INSERT INTO TEST VALUES ('ABCDEFGHIJ');
3. run the program
Not being conversant with Oracle 7, I cannot comment on why you would not get a fecth out of sequence error there. As to O8.1.6, when you select for update, Oracle obtains locks on all rows returned in the result set. When you issue a commit (BTW I'd like to know more about autocommit...can't find it in the documentation) the aforementioned locks are released and your place in the cursor is lost. Any subsequent attempts to fetch from the cursor will bomb.
See further: Oracle PL/SQL Programming by S. Feuerstein, published by O'Reilly