Hi:

Never used oracle but in SQL Server you close cursorname and then deallocate cursorname. You should have similar functionality in oracle.


>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