DanBrother的部落格

Cause:
OraParameters are connected objects, and will cause sessions to remain in the database if not cleaned up properly.


Solution:  (apply to Oracle Objects for OLE)
Removing the OraParameters before closing the OraDatabase resolves this issue.

Example:

'create or replace procedure abc(str1 varchar2) as
'begin
'null;
'end;
'/

Private Sub Command1_Click()
Dim OraSession As OraSession
Dim OraDatabase As OraDatabase
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase("orcl", "scott/tiger", 0&)
OraDatabase.Parameters.Add "v1", 0, ORAPARM_INPUT
OraDatabase.Parameters(0).Value = "hello world"
OraDatabase.ExecuteSQL "begin abc(:v1);end;"

' omitting cleanup causes session to remain
For i = 1 To OraDatabase.Parameters.Count
OraDatabase.Parameters.Remove (0)
Next

OraDatabase.Close
Set OraDatabase = Nothing
Set OraSession = Nothing
MsgBox "check v$session"
End Sub


[Note]
The information is partially derived from My Oracle Support [Doc ID 1273163.1]