ODP.NET Oracle.DataAccess ArrayBindCount OUT Array parameters exception handling

my scenario:

        helper = new OracleHelper();
        helper.CreateAndOpenConnection();
        //cmd = new OracleCommand("PCK_JOBS.ARRAY_INSERT", helper.OracleHelperConnection);
        cmd = new OracleCommand("PCK_JOBS.SCALAR_INSERT", helper.OracleHelperConnection);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.BindByName = true;
        cmd.ArrayBindCount = 3;

        var paramNames = new OracleParameter();
        paramNames.ParameterName = "P_JOB_TITLE";
        paramNames.Size = 3;
        paramNames.OracleDbType = OracleDbType.Varchar2;
        paramNames.Value = new string[3] { "1", "2", "3" };
        cmd.Parameters.Add(paramNames);

        paramNames = new OracleParameter();
        paramNames.ParameterName = "O_JOB_ID";
        paramNames.Size = 3开发者_如何学Python;
        paramNames.Direction = ParameterDirection.Output;
        paramNames.OracleDbType = OracleDbType.Int32;
        paramNames.Value = new int[3] { 0, 0, 0 };

        cmd.Parameters.Add(paramNames);

        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (Exception exc)
        {
        }
        var outParams = cmd.Parameters[1];

when PL-SQL procedure fails for example inserting row 2, and consequently ExecuteNonQuery catch, my output parameters (outParams) are all set to 0 (init value), also for rows correctly processed by procedure.

Is there a way to handle correctly this? so having the partial OUT array parameters correctly filled?

This is what I was referring to in the other thread; handle the exception in the pl/sql:

create or replace
PROCEDURE TESTDATA(myArrayNo in number , myOutputArray out varchar2)
is
    customException EXCEPTION;
    PRAGMA EXCEPTION_INIT( customException, - 20101 );
begin
    myOutputArray := chr( myArrayNo );
    if mod( myArrayNo, 2 ) = 0 then
        raise customException;
    end if;

exception
    when customException then
         myOutputArray := 'I am invalid';
end TESTDATA;

but only capture expected errors here, ones that you don't want to stop the progression, if you put in a when others... then things may get hairy and hide bugs and allow bulk updates/inserts that you don't want to allow.

c# code:

...
      int[]    myArrayNo   = new int[3]{65, 66, 67};
      String[] myOutputArray ;


      OracleConnection connection = new OracleConnection(connectStr);
      OracleCommand    command    = new OracleCommand (
        "TESTDATA", connection);
          //insert into dept values (:deptno, :deptname, :loc)
      // Set the Array Size to 3. This applied to all the parameter in 
      // associated with this command
      command.ArrayBindCount = 3;
        command.CommandType = CommandType.StoredProcedure  ;
      command.BindByName = true;

      // deptno parameter
      OracleParameter arrayNoParam = new OracleParameter("myArrayNo",OracleDbType.Int32);
      arrayNoParam.Direction       = ParameterDirection.Input;
      arrayNoParam.Value           = myArrayNo;
      command.Parameters.Add(arrayNoParam);


      OracleParameter arrayOutParam = new OracleParameter();
      arrayOutParam.ParameterName = "myOutputArray" ;
      arrayOutParam.Direction       = ParameterDirection.Output ;
      arrayOutParam.ArrayBindSize   = new int[3] { 50, 50, 50 };
      arrayOutParam.OracleDbTypeEx  = OracleDbType.Varchar2  ;
      command.Parameters.Add(arrayOutParam);

      try 
      {
        connection.Open();
        command.ExecuteNonQuery();
        myOutputArray = (String[])command.Parameters["myOutputArray"].Value ;

                for (int i = 0; i < 3; i++){
                  Console.WriteLine("myOutputArray{0} = {1}", 
                              i, myOutputArray[i]);
              }                 
      }
        catch (OracleException e)
            {
              Console.WriteLine("OracleException {0} occured", e.Message);
              if (e.Number == 24381)
                for (int i = 0; i < e.Errors.Count; i++){
                  Console.WriteLine("Array Bind Error {0} occured at Row Number {1}", 
                    e.Errors[i].Message, e.Errors[i].ArrayBindIndex);
              }
            }

and outputs

 myOutputArray0 = A 
 myOutputArray1 = I am invalid 
 myOutputArray2 = C

You can also use this

cmd.ArrayBindCount = length;

                    Oracle.DataAccess.Client.OracleParameter P_FinVslCd = new Oracle.DataAccess.Client.OracleParameter("ic_fin_vsl_code", Oracle.DataAccess.Client.OracleDbType.Varchar2);
                    P_FinVslCd.Direction = ParameterDirection.Input;
                    P_FinVslCd.Value = ArrFinVslCd;
                    cmd.Parameters.Add(P_FinVslCd);