ODP.NET

Hello,
array binding with "UPDATE...RETURNING...INTO :outArray" does not work for me as expected:

1. When the UPDATE does not update any row, then command.executeNonQuery() throws exception ORA-22060 (argument [{0}] is an invalid or uninitialized number).

2. When the number of rows updated does not equal the length of the input arrays (fewer or more rows), then executeNonQuery() throws exception ORA-24369 (required callbacks not registered for one or more bind handles).

Has anybody used the RETURNING...INTO clause successfully with array binding?

Thanks,
Armin


Public Function BulkUpdateNoteReturnArray() As Integer
Dim rowCount As Integer
Dim sqlStmt As String
Dim command As New OracleCommand
command.BindByName = True

'Arrays
Dim versions() As Integer = {1, 1, 1}
Dim userIds() As Decimal = {101, 102, 103}
Dim noteTexts() As String = {"Updated Note1 " & Now(), _
"Updated Note2 " & Now(), _
"Updated Note3 " & Now()}

'Returned array
Dim outArrayName As String = "noteId"

sqlStmt = "UPDATE SGNote " _
+ " SET NoteText = :noteText " _
+ " WHERE UserIdentity = :userId " _
+ " AND Version = :version " _
+ " RETURNING Identity INTO :noteId "

'Add parameter arrays
Dim noteTextsParam As New OracleParameter("noteText", OracleDbType.Varchar2)
noteTextsParam.Direction = ParameterDirection.Input
noteTextsParam.Value = noteTexts
command.Parameters.Add(noteTextsParam)

Dim userIdsParam As New OracleParameter("userId", OracleDbType.Decimal)
userIdsParam.Direction = ParameterDirection.Input
userIdsParam.Value = userIds
command.Parameters.Add(userIdsParam)

Dim versionsParam As New OracleParameter("version", OracleDbType.Int32)
versionsParam.Direction = ParameterDirection.Input
versionsParam.Value = versions
command.Parameters.Add(versionsParam)

Dim noteIdsParam As New OracleParameter(outArrayName, OracleDbType.Decimal)
noteIdsParam.Direction = ParameterDirection.Output
command.Parameters.Add(noteIdsParam)

command.ArrayBindCount = versions.Length
command.CommandText = sqlStmt
command.CommandType = CommandType.Text

Dim connection As OracleConnection
connection = New OracleConnection("Pooling=False;User ID=ARSI;Password=ARSI;Data Source=EXIGO1;")
command.Connection = connection
command.Connection.Open()
command.ExecuteNonQuery()

'"ORA-24369: required callbacks not registered for one or more bind handles"
Dim outParameter As OracleParameter
'OracleDecimal[] newSalary = (OracleDecimal[])newSal.Value;
' Dim valuesPtr As Decimal()
' Dim valuesArray() As Decimal
' Dim objectPtr As Object()
outParameter = command.Parameters(outArrayName)
' 'valuesPtr = CType(outParameter.Value, Decimal())
' 'valuesArray = CType(outParameter.Value, Decimal())
' objectPtr = CType(outParameter.Value, Object())
rowCount = outParameter.ArrayBindStatus.Length 'valuesArray.Length

command.Connection.Close()
command.Connection.Dispose()
command.Dispose()

Return rowCount
End Function