FIX: "Not Enough Storage Is Available to Complete This Operation" with Oracle OLE DB Provider (248668)



The information in this article applies to:

  • Microsoft OLE DB Provider for Oracle 2.1
  • Microsoft OLE DB Provider for Oracle 2.5

This article was previously published under Q248668

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.
With a client-side cursor (in other words, when an ActiveX Data Objects (ADO) Recordset's CursorLocation property is set to adUseClient), the following error occurs instead:
80004005 Data provider or other service returned an E_FAIL status
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 that this is a bug in the Microsoft products that are listed at the beginning of this article.

This problem was corrected in MDAC 2.6.

MORE INFORMATION

Steps to Reproduce the Problem

  1. 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))
    						
  2. 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
    						
  3. 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"
    						

Modification Type:MajorLast Reviewed:9/30/2003
Keywords:kbBug kbDatabase kbfix kbMDAC260fix kbMDACNoSweep kbOracle kbProvider KB248668