G
Guest
I am attempting to connect to some Oracle 9i databases from ADO.NET. The code
I am using is straightforward and taken from MSDN (partly reproduced below).
I am using the DOTNET Oracle driver.
The problem is that while it initially works, it does not work after the
connection has been removed from the connection pool (approx. 10 minutes
later). Instead, it creates what appears to be a valid connection object, but
which doesn't work. Attempting to call the .Open() method on it sends the ASP
process into 100% CPU. (Sometimes it is necessary to try opening the
connnection again after an exception has been thrown for this to occur. The
exception message is "... not set to instance of an object".)
I am checking inactive connections with the query
SELECT SID, SERIAL#, STATUS FROM v$session WHERE OSUSER = 'ASPNET';
The problem only seems to occur when this query no longer returns results
(after about 10 minutes), i.e. after there are no connections left in the
pool.
It seems to me that the Oracle driver must think there are still pooled
connections, and is not creating a new one even though it needs to.
Interestingly, if I set the connection pool options in the connection string
(e.g. "Min Pool Size=3"), then it fails the first time.
Dim objCom As OracleCommand
Dim objDS As DataSet
'Open connection
Try
_OracConn.Open() 'Connection object already instantiated
'Catch e As InvalidOperationException
' Throw New OracleRecordsetException(e.Message.ToString & _
' " " & e.Source.ToString)
Catch ex As Exception
_OracConn.Close()
End Try
objCom = New OracleCommand(storedProc, _OracConn)
objCom.CommandType = CommandType.StoredProcedure
Dim sdaSQLAdpt As New OracleDataAdapter(objCom)
'Execute query
Try
objDS = New DataSet
sdaSQLAdpt.Fill(objDS)
Catch e As Exception
Throw New OracleRecordsetException(e.Message.ToString & _
" " & e.Source.ToString)
Finally
If _OracConn.State = ConnectionState.Open Then
_OracConn.Close()
End Try
Return objDS
I am using is straightforward and taken from MSDN (partly reproduced below).
I am using the DOTNET Oracle driver.
The problem is that while it initially works, it does not work after the
connection has been removed from the connection pool (approx. 10 minutes
later). Instead, it creates what appears to be a valid connection object, but
which doesn't work. Attempting to call the .Open() method on it sends the ASP
process into 100% CPU. (Sometimes it is necessary to try opening the
connnection again after an exception has been thrown for this to occur. The
exception message is "... not set to instance of an object".)
I am checking inactive connections with the query
SELECT SID, SERIAL#, STATUS FROM v$session WHERE OSUSER = 'ASPNET';
The problem only seems to occur when this query no longer returns results
(after about 10 minutes), i.e. after there are no connections left in the
pool.
It seems to me that the Oracle driver must think there are still pooled
connections, and is not creating a new one even though it needs to.
Interestingly, if I set the connection pool options in the connection string
(e.g. "Min Pool Size=3"), then it fails the first time.
Dim objCom As OracleCommand
Dim objDS As DataSet
'Open connection
Try
_OracConn.Open() 'Connection object already instantiated
'Catch e As InvalidOperationException
' Throw New OracleRecordsetException(e.Message.ToString & _
' " " & e.Source.ToString)
Catch ex As Exception
_OracConn.Close()
End Try
objCom = New OracleCommand(storedProc, _OracConn)
objCom.CommandType = CommandType.StoredProcedure
Dim sdaSQLAdpt As New OracleDataAdapter(objCom)
'Execute query
Try
objDS = New DataSet
sdaSQLAdpt.Fill(objDS)
Catch e As Exception
Throw New OracleRecordsetException(e.Message.ToString & _
" " & e.Source.ToString)
Finally
If _OracConn.State = ConnectionState.Open Then
_OracConn.Close()
End Try
Return objDS