Oracle.DataAccess (ODP.NET) Array Binding "Value does not fall within the expected range"

Question

my scenario

i'm using ODP.NET oracle provider with c# 3.5, and i am trying to pass an array as parameter for a procedure...like this:

var paramNames = new OracleParameter();
paramNames.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
paramNames.ParameterName = "P_JOB_TITLE";
paramNames.Size = 2;
paramNames.Value =  new string[2]{ "name1", "name1" };
cmd.Parameters.Add(paramNames);

when runtime code goes to paramNames.Value = new string[2]{ "name1", "name1" }; it catch with this error

"Value does not fall within the expected range"

Can anyone fix it?

ADDITIONAL INFO

Specifying OracleDbType the error is fixed, but executing give me some errors

paramNames.OracleDbType = OracleDbType.Varchar2;

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

my goal is to do something like this

http://aspalliance.com/621_Using_ODPNET_to_Insert_Multiple_Rows_within_a_Single_Round_Trip.3

ANOTHER PROBLEM WITH OUT PARAMETER

Inserting an out parameter like this

            paramNames = new OracleParameter();
            paramNames.ParameterName = "O_JOB_ID";
            paramNames.Size = 3;
            paramNames.Direction = ParameterDirection.Output;
            paramNames.OracleDbType = OracleDbType.Int32;
            paramNames.Value = new int[3] { 0, 0, 0 }; ;
            cmd.Parameters.Add(paramNames);

it is correctly filled when ExecuteNonQuery finished. For example the pls-sql procedure performs 3 inserts and i return the row-id of each array record.

But i something goes wrong, for example isnerting the 2nd row, the entire OUT parameters (array) are always set on 0. I expected at least the params[0].value was enhanced

Thanks

Harrison Harrison · Accepted Answer · 2011-06-20T13:58:23

I think you are trying to merge an Array Bind {simply binding an array to a param to have it execute multi times -- this is how the example in the link you provided did it} with an Associative Array {re: PLSQLAssociativeArray with an INPUT param of TABLE OF}.

Since you didn't post your package/proc that you are running, I am assuming you are doing something like this (just putting this down to validate the assumption)

procedure insertdata(P_JOB_TITLE IN VARCHAR2) as
begin
insert into myTable(x) value (P_JOB_TITLE);
end  insertdata;

To execute this like the author of the article you need to use ArrayBindCount (check out this link, it also has an example). This also indicates, if you have multiple parameters, it will expect an ARRAY for each one.

Now to have this executed for all the P_JOB_TITLE() that you pass in

//this was missing in your example and MUST be there to tell ODP how many array elements to expect
cmd.ArrayBindCount = 2;

 string[] jobTitleArray = {"name1", "name1"};

OracleParameter paramNames= new OracleParameter("P_JOB_TITLE", OracleDbType.Varchar2);

   //paramNames.CollectionType = OracleCollectionType.PLSQLAssociativeArray;/*once again, you are passing in an array of values to be executed and not a pl-sql table*/

    //paramNames.Size = 2; /* this is unnecessary since it is for a plsql-associative array*/
    paramNames.Value =  jobTitleArray ;
    cmd.Parameters.Add(paramNames);

For a plSQLAssociativeArray example have a look at the samples provided when you installed ODP @ %ORA_HOME%\odp.net\samples\2.x\AssocArray

and for array bind examples (as from the link you provided) @ %ORA_HOME%\odp.net\samples\2.x\ArrayBind

Oracle.DataAccess (ODP.NET) Array Binding "Value does not fall within the expected range"

1 Answers