Hello everybody, I have a database question. I connect to a symposium database. Query the data and return a bunch of records into a dataset. I loop through the data one record at a time. I have to do some data validation. When I do this I query an oracle database using a stored procedure, this creates an open ref_cursor in oracle. The problem is, I don't know how to close the cursor. So as I go through the loop, I end up reaching the maximum number of open cursors for oracle. Could someone please tell me how to close the cursor. I have tried the following:
'ado2 is my own connection class
ado2 = New clsoracle("commondev")
Try
ado2.strSQL = "Return_Data.ReturnResult"
ado2.CreateCommand()
ado2.objCmd.CommandType = CommandType.StoredProcedure
spTelsetLoginID = ado2.objCmd.Parameters.Add("strTelsetLoginID",
OracleClient.OracleType.VarChar)
spTelsetLoginID.Direction = ParameterDirection.Input
spTelsetLoginID.Value = strTelsetLoginID
spSiteID = ado2.objCmd.Parameters.Add("intSite_ID",
OracleClient.OracleType.Number)
spSiteID.Direction = ParameterDirection.Input
spSiteID.Value = intSiteID
objCursor = ado2.objCmd.Parameters.Add(New
OracleClient.OracleParameter("p_cursor", _
OracleClient.OracleType.Cursor))
objCursor.Direction = ParameterDirection.Output
ado2.OpenConnection()
ado2.CreateDataSet()
ado2.CreateSPAdapter()
ado2.FillDataSet(ado2.objDS, "SymposiumEmployee")
' Dispose the dataadapter
ado2.objDA.Dispose()
' Dispose the command object
ado2.objCmd.Dispose()
' I even tried disposing of the dataset
ado2.objDS.Dispose()
' Closing the connection does not get rid of the cursor
ado2.CloseConnection()
' Nor does disposing of the connection object
ado2.objConn.Dispose()
' Even if I set my class = nothing, the ref_cursor still exists in oracle
ado2 = nothing
Thanks for the help.
Randy
Randy Belcher
AFG Industries, Inc.