DOCUMENT:Q183639 13-APR-1998 [vbwin] TITLE :PRB: Oracle Cursors Remain Open After Calling VB5 rdoQuery PRODUCT :Microsoft Visual Basic for Windows PROD/VER:WINDOWS:1.0,2.0,4.0,5.0; WINNT: OPER/SYS:WINDOWS winnt KEYWORDS:VBKBRDO kbcode ====================================================================== --------------------------------------------------------------------- The information in this article applies to: - The Development Environment - Microsoft ODBC Driver for Oracle, version 2.0, versions 1.0, 2.0 - Microsoft Visual Basic Enterprise Edition for Windows, versions 4.0, 5.0 --------------------------------------------------------------------- SYMPTOMS ======== Calling the rdoQuery object in Visual Basic 5.0 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: 1. Open a new project in Visual Basic 5.0. Form1 is created by default. 2. Place a command button on the new Form. 3. On the Project menu, click References, and select Microsoft Remote Data Object 2.0. 4. 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. (c) Microsoft Corporation 1998, All Rights Reserved. Contributions by Ron Nelson, Microsoft Corporation ====================================================================== Keywords : VBKBRDO kbcode Technology : kbrdo Version : WINDOWS:1.0,2.0,4.0,5.0; WINNT: Platform : WINDOWS winnt Issue type : kbprb ============================================================================= THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Copyright Microsoft Corporation 1998.