FIX: Oracle Cursor Limit Exceeded with Create Methods

Zavrnitev odgovornosti za umaknjeno vsebino KB

Ta članek govori o izdelkih, za katere Microsoft ne ponuja več podpore. Zato je ta članek na voljo »tak, kot je«, in ne bo več posodobljen.

Symptoms

When a Microsoft Visual Basic program uses the CreateDynaset or CreateSnapshot methods repeatedly, Oracle eventually runs out of cursors, and returns this error:
ODBC--call failed. [PageAhead][ODBC Oracle Driver][Oracle OCI]
ORA-01000: maximum open cursors exceeded. (#1000)

Cause

Each SQL statement issued to an ODBC Data Source has an associated statement handle (hstmt) used to identify the statement. In Oracle, each hstmt uses a cursor. Cursors are a limited resource in Oracle and if the cursors are not dropped, the database will eventually run out.

Visual Basic and the Microsoft Jet version 1.1 database engine allocate a new hstmt for each action SQL statement (INSERT, DELETE, UPDATE) executed by using the CreateDynaset and CreateSnapshot methods. However, neither Visual Basic nor the Jet database engine free the statement handle when closing the Dynaset or Snapshot. Instead, both rely on the freeing of the connection to perform these tasks.

The Jet version 2.0 database engine is more aggressive about allocating and dropping statement handles. Each hstmt allocated is dropped when the Dynaset or Snapshot is closed. As a result, Oracle cursors are dropped along with the hstmt.

The problem is evident when using the CreateDynaset or CreateSnapshot methods with the DB_SQLPassthrough flag (DB_SQLPassThrough=64) to issue action SQL statements to an Oracle database. Most developers use this method to avoid creating the additional connection to the server that the ExecuteSQL statement creates. The problem does not arise when using the CreateDynaset or CreateSnapshot methods to execute row returning queries, with or without the Passthrough option.

Resolution

The CreateDynaset and CreateSnapshot methods were not designed to run action queries. The Execute and ExecuteSQL methods are provided for those tasks. If a developer uses Execute or ExecuteSQL, VB uses the same hstmt for each action query and Oracle will not run out of cursors.

To avoid exceeding the maximum number of cursors:

  1. With Visual Basic and Jet version 1.1, increase the Oracle server option that increases the number of available cursors per connection.
  2. With Visual Basic and Jet version 1.1, use the Execute or ExecuteSQL methods to issue SQL action queries (INSERT, DELETE, UPDATE). Visual Basic will reuse the same hstmt repeatedly and cursors will not be depleted in Oracle.
  3. Purchase Microsoft Access version 2.0 to get the Jet version 2.0 database engine. You can then install the Compatibility Layer (Comlyr.exe), which enables Visual Basic to use the Jet version 2.0 database engine.
The following file is available for download from the Microsoft Download Center:
For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:
119591 How to Obtain Microsoft Support Files from Online Services
Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file.
Comlyr.exe is a self-extracting compressed file. Place the file in an empty directory and execute it. The file will expand and will produce the readme file (ACC2COMP.TXT) and SETUP.EXE. Run SETUP.EXE from File Manager to install the Compatibility Layer.

Status

This problem has been fixed in Visual Basic 5.0.

More Information

Steps to Reproduce Behavior

  1. Create a new project in Visual Basic (Alt, F, N). Form1 is created by default.
  2. Add a CommandButton (Command1) and a label (Label1) to Form1.
  3. Add the following code to the click event of Command1:
       Sub Command1_Click ()
    Dim db As Database 'database object
    Dim ds As Dynaset 'dynaset object
    Dim I As Integer 'counter
    Dim sql As String 'string to store sql stmt

    Set db = OpenDatabase("", False, False, "ODBC;")
    For I = 1 To 100
    sql$ = " INSERT INTO table (field1) "
    sql$ = sql$ & " VALUES ('" & CStr(I) & "')"
    Set ds = db.CreateDynaset(sql$, 64)
    ds.Close
    Label1.Caption = CStr(I)
    Label1.Refresh
    Next I
    db.Close
    End Sub
  4. Save the project and press the F5 key to run it. The code will generate an error on the 51st iteration.
Lastnosti

ID članka: 125227 – Zadnji pregled: 5. avg. 2004 – Revizija: 1

Povratne informacije