BUG: "Not Enough Storage Is Available to Complete This Operation" with Oracle OLE DB Provider

ID: Q248668

The information in this article applies to:

Microsoft OLE DB Provider for Oracle, versions 2.1, 2.5

SYMPTOMS

The following error message may appear when 5000 records or more are retrieved, and when each record contains 4 bytes of data:

8007000e Not enough storage is available to complete this operation.

Note that the computer is not really out of memory. Microsoft OLE DB Provider for Oracle and its internal algorithm, which attempts to allocate a buffer to hold the rows, fails if the rowset size is 4 bytes or less.

RESOLUTION

To work around this problem, return recordsets larger than 4 bytes.

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce the Problem

Create a table in Oracle with the following statement, by using SQL*Plus or some other database utility, and by using the DEMO UserID and DEMO password:
```
  CREATE TABLE TABLE1 (FIELD1 NUMERIC (4,0)) 
```

Create a Visual Basic application and put the following code in the Form's Load section (you need to create a DSN named "ORACONN" or modify the code to reflect a DSN you have already created):


cnn.Open "dsn=ORACONN;uid=demo;pwd=demo", , , -1
  cmd.ActiveConnection = cnn
 
  For i = 1 To 7000
    cmd.CommandText = "insert into DEMO.TABLE1 (FIELD1) Values(" & Str(i) & ")"
    cmd.Execute
  Next i

Create a Visual Basic form with a list box and a button. In the handler for the button, paste the following code:


Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rstRecordIDs As ADODB.Recordset
    Dim strCnn As String

    Set cnn = New ADODB.Connection
    Set cmd = New ADODB.Command
   
   On Error GoTo Err_cmdDBTest_Click
   
   'Open recordset from table.
   
   'Connection string #1 using OLEDB provider for oracle fails when attempting to move to
   'rows around 5000.
   strCnn = "Provider=MSDAORA.1;Password=demo;User ID=demo;Data Source=dseoracle8"
   
   'Connection string #2 using MS ODBC for Oracle works fine
   'strCnn = "DSN=ORACONN;pwd=demo;uid=demo"
   
   Set rstRecordIDs = New ADODB.Recordset
   rstRecordIDs.CursorType = adOpenForwardOnly
   rstRecordIDs.LockType = adLockReadOnly
   rstRecordIDs.CursorLocation = adUseServer
   rstRecordIDs.MaxRecords = 100000
   rstRecordIDs.CacheSize = 100
   Debug.Print cnn.Version
   rstRecordIDs.Open "SELECT FIELD1 FROM DEMO.TABLE1 ORDER BY FIELD1", strCnn, , , adCmdText
    
    If rstRecordIDs.EOF Then
      MsgBox "No records!"
      Exit Sub
    End If
   
   rstRecordIDs.MoveFirst
   If rstRecordIDs.EOF Then
     MsgBox "No Rows!"
     Exit Sub
   End If
   
   Do While True
     lstData.AddItem "Record ID: " & rstRecordIDs!FIELD1
     rstRecordIDs.MoveNext
     
     If rstRecordIDs.EOF Then
       MsgBox "At end of recordset!"
       Exit Do
     End If
   Loop
   
   rstRecordIDs.Close
   Exit Sub
Err_cmdDBTest_Click:
    Debug.Print "Error Description : " + Err.Description
End Sub"

Additional query words:

Keywords : kbDatabase kbOLEDB kbOracle kbProvider kbGrpVCDB kbGrpMDAC kbDSupport kbMDAC210SP2bug kbMDAC250bug
Version : WINDOWS:2.1,2.5
Platform : WINDOWS
Issue type : kbbug