Re: OO40 session remains active until workbook closed

"Mikez" <mzullo_at_na2.us.ml.com> wrote in message news:27debcba.0111230821.5ca9cc94_at_posting.google.com...
> 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:
<SNIP>
> 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&)
> 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)
<SNIP>
> 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.

From the Documentation for oo4o Database object. (OpenDatabase method)

" In the Oracle8i release, invocation of this method results in implicit creation of an OraServer object. "

The OraServer object represents a poolable database connection.

As you never destroy your db object then the connection should - on my reading - remain active as you describe. It would seem that you would have two options.

1. Replace DB.close with Set DB = Nothing
2. Investigate connection pooling which IIRC requires that the same username and password are passed to the API.

HTH

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************