ODP.NET

Running ODP.NET beta2 (can't move up yet but will do that soon)

I don't think it is related with ODP itself but probably on how .Net works with cursors. We have a for/next loop that executes INSERT INTO xxx VALUES (:a,:b,:c)
statements. Apparently, when monitoring v$sysstat (current open cursors) we see these raising with 1 INSERT = 1 cursor. If subsequently we try to perform another action, we get max cursors exceeded. We allready set open_cursor = 1000, but the number of inserts can be very high. Is there a way to release these cursors (already wrote oDataAdaptor.dispose, oCmd.dispose but this does not help.

Is it normal that each INSERT has it's own cursor ? they all have the same hashvalue in v$open_cursor. They seem to be released after a while, especially when moving to another asp.net page, but it's not clear when that happens and if it is possible to force the release of the (implicit?) cursors faster.

Below is a snippet of the code, I unrolled a couple of function-calls into the code so this is just an example, not sure it will run without errors like this, but the idea should be clear (the code looks rather complex for what it does but the unrolled functions make the code more generic and we have a database-independend datalayer):

Try
' Set the Base Delete statement
lBaseSql = _
"INSERT INTO atable(col1,col2,col3) " & _
"VALUES(:col1,:col2,:col3)"

' Initialize a transaction
lTransaction = oConnection.BeginTransaction()

' Create the parameter collection, containing for each
' row in the list the arguments
For Each lDataRow In aList.Rows

lOracleParamters = New OracleParameterCollection()

lOracleParameter = New OracleParameter("luserid", OracleDbType.Varchar2,
_ CType(aCol1, Object))
lOracleParamters.Add(lOracleParameter)

lOracleParameter = New OracleParameter("part_no", OracleDbType.Varchar2, _
CType(lDataRow.Item("col2"), Object))
lOracleParamters.Add(lOracleParameter)

lOracleParameter = New OracleParameter("revision", OracleDbType.Int32, _
CType(lDataRow.Item("col3"), Object))
lOracleParamters.Add(lOracleParameter)

' Execute the Statement;
' If the execution fails because the row already exists,
' then the insert should be considered as succesfull.
Try
Dim aCommand As New OracleCommand()
Dim retval As Integer

'associate the aConnection with the aCommand
aCommand.Connection = oConnection

'set the aCommand text (stored procedure name or SQL statement)
aCommand.CommandText = lBaseSQL

'set the aCommand type
aCommand.CommandType = CommandType.Text

'attach the aCommand parameters if they are provided
If Not (lOracleParameters Is Nothing) Then
Dim lParameter As OracleParameter
For Each lParameter In lOracleParameters
'check for derived output value with no value assigned
If lParameter.Direction = ParameterDirection.InputOutput _
And lParameter.Value Is Nothing Then
lParameter.Value = Nothing
End If
aCommand.Parameters.Add(lParameter)
Next lParameter
End If

Return

' finally, execute the aCommand.
retval = cmd.ExecuteNonQuery()

' detach the OracleParameters from the aCommand object,
' so they can be used again
cmd.Parameters.Clear()


Catch ex As Exception
Dim lErrorMsg As String
lErrorMsg = ex.ToString
If Not lTransaction Is Nothing Then
lTransaction.Rollback()
End If
End Try
Next
lTransaction.Commit()

Catch ex As Exception
lTransaction.Rollback()
Throw New DLDataException(aConnection, ex)
End Try