PRB: No Records Returned from SELECT w/FOR UPDATE Against Oracle8 Server

• ActiveX Data Objects (ADO), versions 2.0, 2.01, 2.1

SYMPTOMS

The following error occurs when interacting with a recordset from an Oracle 8 server created with a SELECT statement containing the FOR UPDATE clause:

Error ORA-01002, "Fetch out of sequence"

RESOLUTION

To work around this problem, execute the ADO Connection object's BeginTrans method before executing the SELECT statement. Use the CommitTrans method when finished with the recordset.

MORE INFORMATION

The below example will reproduce the behavior described above. Uncomment the BeginTrans and CommitTrans calls to see the workaround.

Note that this behavior occurs with both the OLE DB Provider and the ODBC driver. Also note that the error does not occur when executed against an Oracle 7.3 server.

Steps to Reproduce Behavior

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection
cnn.CursorLocation = adUseClient
cnn.Open "DSN=myoracle8dsn;UID=myuid;PWD=mypwd"

Set rst = New ADODB.Recordset
'cnn.BeginTrans
rst.Open "select * from mytable for update", cnn, adOpenDynamic
rst.MoveFirst
'cnn.CommitTrans 

Additional query words:

Keywords          : kberrmsg kbADO kbDatabase kbOracle kbGrpVBDB kbGrpMDAC 
Version           : WINDOWS:2.0,2.01,2.1
Platform          : WINDOWS 
Issue type        : kbprb 

Last Reviewed: May 17, 1999