Unable to cast object of type 'System.Int32[]' to type 'System.IConvertible when calling Oracle stored procedure from ASP.NET

Question

I have used integer array to Pass values to Oracle stored procedure

Data type in Oracle => Number

stored procedure :

 create or replace PROCEDURE SP_ACCESS
    (
           UserID IN CHECKINOUT.USERID%TYPE  
    )
    IS
    BEGIN
      INSERT INTO CHECKINOUT ("USERID")
      VALUES (UserID);
      COMMIT;
    END;

ASP.NET Code :

            int[] arrUID = UID.ToArray();
            OracleConnection connection = new OracleConnection();
            connection.ConnectionString = Obj.GetOraConnectionString();
            OracleCommand command = new OracleCommand();
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "SP_ACCESS";
            command.Parameters.Add("@USERID", OracleDbType.Int32);
            command.Parameters[0].Value = arrUID;
            connection.Open();
            command.ExecuteNonQuery();

While executing i got the following error:

Unable to cast object of type 'System.Int32[]' to type 'System.IConvertible'.

`command.Parameters[0].Value` is expecting a `System.Int32` type, you're assigning a `System.Int32[]` (**array of int's**) — Ivan García Topete, May 14 '18 at 19:56

score 2 · Accepted Answer · answered May 15 '18 at 01:02

These lines below causing exception because OracleCommand parameter expects data type of int, while you're passing value of int[] array (also array binding is not used by default).

command.Parameters.Add("@USERID", OracleDbType.Int32);
command.Parameters[0].Value = arrUID;

If you just want to pass single element, use array index number to assign value.

command.Parameters[0].Value = arrUID[0];

However if you want to pass entire array contents into stored procedure parameter, try setting OracleCollectionType.PLSQLAssociativeArray to CollectionType property before assigning value (side note: possible requires schema level type to be declared first before using in stored procedure, see reference (1)):

command.Parameters.Add("@USERID", OracleDbType.Int32);
command.Parameters[0].CollectionType = OracleCollectionType.PLSQLAssociativeArray;
command.Parameters[0].Value = arrUID;

// ExecuteNonQuery afterwards

Or simply setting ArrayBindCount property before assigning parameter value:

command.BindByName = true;
command.ArrayBindCount = UID.Count; // assumed UID is a List<int>
command.Parameters.Add("@USERID", OracleDbType.Int32);
command.Parameters[0].Value = arrUID;

// ExecuteNonQuery afterwards

Additional references:

(1) C# 2010, ODP.net, call stored procedure passing array

(2) Oracle stored procedure using array as parameter for table insert

(3) Pass a list of integers from C# into Oracle stored procedure

Unable to cast object of type 'System.Int32[]' to type 'System.IConvertible when calling Oracle stored procedure from ASP.NET

1 Answers1