Array Binding Error ORA-01485 when i try to pass in an associative array
770853May 6 2010 — edited May 10 2010Hello,
I'm testing to pass in a associative array through odp.net
So i created a simple testpackage:
CREATE TABLE jwetesttab (CLIENTNR NUMBER(10) NOT NULL);
CREATE OR REPLACE PACKAGE JWETEST_PK AS
TYPE t_CLIENTNRS IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
PROCEDURE TestArrayIn (p_CLIENTNRS IN t_CLIENTNRS, p_NbOfRowsInserted OUT NUMBER);
END JWETEST_PK;
/
CREATE OR REPLACE PACKAGE BODY JWETEST_PK AS
PROCEDURE TestArrayIn (p_CLIENTNRS IN t_CLIENTNRS, p_NbOfRowsInserted OUT NUMBER)
IS
BEGIN
FORALL i IN p_CLIENTNRS.first..p_CLIENTNRS.last
INSERT INTO jwetesttab(CLIENTNR) VALUES(p_CLIENTNRS(i));
p_NbOfRowsInserted := SQL%ROWCOUNT;
COMMIT;
END TestArrayIn;
end JWETEST_PK;
/
Then i made as simple testapp as follows:
Dim Int As Integer = 0
Dim arrNums() As Integer = {1, 2, 3}
Dim cmd As New OracleCommand
Dim cnn As New OracleConnection(s_conn)
Try
With cmd
.ArrayBindCount = 3
.Connection = cnn
.CommandText = "JWETEST_PK.TestArrayIn"
.CommandType = CommandType.StoredProcedure
Dim p_Clientnrs As OracleParameter = New OracleParameter
With p_Clientnrs
.ParameterName = "p_CLIENTNRS"
.DbType = DbType.Int32
.CollectionType = OracleCollectionType.PLSQLAssociativeArray
.Value = arrNums
.Size = 3
End With
.Parameters.Add(p_Clientnrs)
.Parameters.Add(New OracleParameter("p_NbOfRowsInserted", OracleDbType.Int32, ParameterDirection.Output))
cnn.Open()
cmd.ExecuteNonQuery()
Int = CInt(.Parameters("p_NbOfRowsInserted").Value)
End With
Catch ex As Exception
MsgBox(ex.Message)
Finally
If (cnn Is Nothing) = False Then
cnn.Close()
cnn.Dispose()
End If
End Try
When i execute my testapp, the rows are inseterd into the database, but it gives me an error: Array Binding Error : ORA-01485