Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Array Binding Error ORA-01485 when i try to pass in an associative array

770853May 6 2010 — edited May 10 2010
Hello,

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
This post has been answered by gdarling - oracle on May 6 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 7 2010
Added on May 6 2010
3 comments
1,977 views