Microsoft KB Archive/183639

= PRB: Oracle Cursors Remain Open After Calling VB rdoQuery =

Article ID: 183639

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Open Database Connectivity 2.0
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Visual Basic 4.0 Enterprise Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q183639



SYMPTOMS
Calling the rdoQuery object in Visual Basic 5.0 or later or a Remote Data Object (RDO) PreparedStatement in Visual Basic 4.0 opens three Oracle cursors; two remain open after closing the rdoQuery or PreparedStatement object.

These cursors should not be confused with cursors that return data. Oracle opens cursors internally, and they are defined as follows:

A cursor is a synonym for context area - a work area in memory where Oracle stores the current SQL statement. For a query, the area in memory also includes column headings and one row retrieved by the Select Statement.



CAUSE
Oracle provides an API to get the stored procedure information. This API is called, in the case of a stored procedure execution, to get the parameter information. The API generates two additional cursors, which are not released until the logoff request happens. These cursors do not increase when you attempt multiple executions.



RESOLUTION
This behavior is by design.



MORE INFORMATION
Oracle opens a single cursor for an RDO Resultset and closes the cursor when the Resultset object is closed. However, the rdoQuery or rdoPreparedStatement leaves two cursors open as illustrated below.

To test in Visual Basic 4.0, substitute the Visual Basic 4.0 PreparedStatement object for the rdoQuery object.

It is assumed the reader knows how to check for open cursors via Oracle Administrator utilities.

This application calls a stored procedure that accepts a numeric input parameter and returns the value divided in half as an output parameter. Calling any stored procedure should produce similar results. The script for the stored procedure called by this project is as follows: Create Or Replace Procedure NoTable (inNum IN NUMBER, outNUM OUT NUMBER) IS  BEGIN outNUM := inNUM/2; END; /

Steps to Reproduce Behavior
After the stored procedure is created, follow these steps:  Open a new project in Visual Basic. Form1 is created by default. Place a command button on the new Form. On the Project menu, click References, and select Microsoft Remote Data Object 2.0.  Place the following code in the General Declarations section of Form1. You will need to alter the database connection information in the strCon String: '        Dim strSql As String Dim strError As String Dim En As rdoEnvironment Dim Qr As rdoQuery Dim Cn As rdoConnection Dim Rs As rdoResultset Dim strCon, UserID As String

Private Sub Command1_Click

strCon = "DSN=MyOracle;UID=MyUID;PWD=MyPassword;" strSql = "{call NoTable(?,?)}"

Set En = rdoEnvironments(0) En.CursorDriver = rdUseClientBatch Set Cn = En.OpenConnection("", rdDriverNoPrompt, _           False, strCon)

'Pause the program to check on number of open cursors. 'Should be 0 (zero) at this point MsgBox "Check Open Cursors"

Set Qr = Cn.CreateQuery("", strSql)

Qr.rdoParameters(0).Value = 10 Qr.Execute

'Pause the program to check on number of open cursors. 'Should be three at this point. MsgBox "Check Open Cursors"

Debug.Print "The Output is " & Qr(1) Qr.Close

'Should be two open cursors at this point. MsgBox "DONE"

End Sub



NOTE: Comments in the above code show at what point in the code you should examine the number of open Oracle cursors.

Keywords: kboracle kbapi kbprb kb3rdparty KB183639

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.