FIX: Oracle Cursor Limit Exceeded with Create Methods
ID: Q125227
|
The information in this article applies to:
-
Microsoft Visual Basic Professional Edition for Windows, version 3.0
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:
- With Visual Basic and Jet version 1.1, increase the Oracle server option
that increases the number of available cursors per connection.
- 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.
- 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 Software
Library:
comlyr.exe
For more information about downloading files from the Microsoft Software
Library, please see the following article in the Microsoft Knowledge Base:
Q119591 How to Obtain Microsoft Support Files from Online Services
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
- Create a new project in Visual Basic (Alt, F, N). Form1 is created by
default.
- Add a CommandButton (Command1) and a label (Label1) to Form1.
- 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
- Save the project and press the F5 key to run it. The code will generate
an error on the 51st iteration.
Additional query words:
ODBC Oracle Cursor
Keywords : kb3rdparty kbsample kbDatabase kbODBC
Version : 3.00
Platform : WINDOWS
Issue type : kbprb