OO40 session remains active until workbook closed

From: Mikez <mzullo_at_na2.us.ml.com>
Date: 23 Nov 2001 08:21:48 -0800
Message-ID: <27debcba.0111230821.5ca9cc94_at_posting.google.com>

[Quoted] Using OO40 to connect EXCEL Vba to Oracle on UNIX I find the Oracle session remains active until you close the workbook. Closing the database object and setting the session object to "Nothing" does not end the session.

Example:

---

Option Explicit
Dim OraSessiOn As Object
Dim DB As Object
Dim oradynaset As Object

Sub db_connect()
Dim sql As String
Dim rc As Integer
Dim sUser As String
Dim sPswd As String
Dim sInstance As String

On Error GoTo ErrProc1
sUser = "xxupload"
sPswd = "xxupload"
sInstance = "xxxprod"

[Quoted] Set OraSessiOn = CreateObject("OracleInProcServer.XOraSession") Set DB = OraSessiOn.DbOpenDatabase(sInstance, sUser & "/" & sPswd, 0&)

' Count Active Sessions

sql = "select count(*) from v$session where upper(username) = '" & UCase(sUser) & "'"
Set oradynaset = DB.DbCreateDynaset(sql, 0&) [Quoted] MsgBox ("Active Sessions # 1 = " & oradynaset.fields(0).Value)

' End the current session

Set oradynaset = Nothing
DB.Close
Set OraSessiOn = Nothing

' This count should fail but it doesnt

sql = "select count(*) from v$session where upper(username) = 'APUPLOAD'"
Set oradynaset = DB.DbCreateDynaset(sql, 0&)

MsgBox ("Active Sessions # 2 = " & oradynaset.fields(0).Value)

Exit Sub
ErrProc1:
MsgBox ("Error in 0040 " & Error(Err))
End Sub

---

2 Things I have noticed.

1. If I define the objects locally the session will end when it hits the "Exit Sub".
2. If I run this once both message boxes show 1 active session, when I run it a second time the first one shows 2 sessions the second shows only 1.

[Quoted] Is there any way I can force Oracle to end the session ? We have many Vba apps talking to oracle via 0040 and wind up with a high number of inactive Oracle sessions. Received on Fri Nov 23 2001 - 17:21:48 CET