Oracle ArrayBound Stored Procedure fails only with specific field

Question

I have a stored procedure that works and has been working for months for inserting data.

Here's the "short" version:

procedure saveApplication(  in_confirmation_number  varchar2 := null                           
                            ,in_nonstandard_address varchar2 := null      
                            ,in_absentee_type varchar2 := null) AS
  BEGIN
    insert into vr_application (
            confirmation_number
            ,nonstandard_address
            ,absentee_type )
    values ( in_confirmation_number
            ,in_nonstandard_address
            ,in_absentee_type 
            );
END;

I'm working in bulk so I stuff the data in as arrays after pulling the value from a datatable. Again, below is the "shortened" version.

private static void loadFiles(DataTable dt, string connString, ErrorLogger log)
    {

        OracleConnection orclconn = null;
        OracleCommand cmd = null;

        using (orclconn = new OracleConnection(connString))
        {
            orclconn.Open();
            using (cmd = BuildCommand(dt))
            {
                cmd.Connection = orclconn;
                cmd.ExecuteNonQuery();
            }
        }

    }

 private static OracleCommand BuildCommand(DataTable dt)
    {
        OracleCommand cmd = new OracleCommand();
        cmd.CommandText = "Applications.saveApplication";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.ArrayBindCount = dt.Rows.Count;
        cmd.BindByName = true;

        string[] CONFIRMATION_NUMBER = dt
           .AsEnumerable()
           .Select(row => row.Field<string>("CONFIRMATION_NUMBER"))
           .ToArray();
        OracleParameter in_confirmation_number = new OracleParameter();
        in_confirmation_number.OracleDbType = OracleDbType.Varchar2;
        in_confirmation_number.Value = CONFIRMATION_NUMBER;
        in_confirmation_number.ParameterName = "in_confirmation_number";
        cmd.Parameters.Add(in_confirmation_number);

         string[] ABSENTEE_TYPE = dt
            .AsEnumerable()
            .Select(row => row.Field<string>("ABSENTEE_TYPE"))
            .ToArray();
        OracleParameter in_absentee_type = new OracleParameter();
        in_absentee_type.OracleDbType = OracleDbType.Varchar2;
        in_absentee_type.Value = ABSENTEE_TYPE;
        in_absentee_type.ParameterName = "in_absentee_type";
        cmd.Parameters.Add(in_absentee_type);

        string[] NONSTANDARD_ADDRESS = dt
         .AsEnumerable()
         .Select(row => row.Field<string>("NONSTANDARD_ADDRESS"))
         .ToArray();
        OracleParameter in_nonstandard_address = new OracleParameter();
        in_absentee_type.OracleDbType = OracleDbType.Varchar2;
        in_absentee_type.Value = NONSTANDARD_ADDRESS;
        in_absentee_type.ParameterName = "in_nonstandard_address";
        cmd.Parameters.Add(in_nonstandard_address);

        return cmd;
}

Scenario 1: nonstandard_address code is commented out. Everything works.

Scenario 2: nonstandard_address code is not commented out. But instead of passing values into the original datatable, I hardcode the value "null". Everything works. This is where it has been for months.

Scenario 3: the datatable for nonstandard address has a single row which has a value in nonstandard address. All other rows contain null for this column. I get an Oracle.DataAccess.Client.OracleException, #ORA-06550, with the message "Encountered the symbol ">" when expecting one of the following...."

To attempt to identify the problem, I simply looped through the values in the array. I get the same error on the last loop iteration, which is always one more than the number of records in the data table (100). But if I loop without attempting to create an Oracle Parameter for nonstandard, I get no error and only 100 loop iterations.

If I do Scenario 2 and successfully fill the table with everything except nonstandard_address, I can then run the following in Oracle, and successfully update the table.

update vr_application a
set nonstandard_address = (select nonstandard_address from unprocessed_apps b where b.confirmation_number = a.confirmation_number)
where exists (select 1 from unprocessed_apps where confirmation_number = a.confirmation_number)

Can anyone see a mistake here? Anyone seen this before? I'm baffled.

Try putting the code for the nonstandard_address parameter before the code for absentee_type, in order to specify the parameters in the same order they're named in the PL/SQL procedure. I don't think this should matter but it's worth a shot. — Bob Jarvis - Слава Україні, Jul 21 '12 at 01:33
worth a try - I'll be working on this later today and will let you know the outcome. — user158017, Jul 21 '12 at 11:48
moving it wasn't the answer, but it gave me the answer because of a compile error. and now I feel stupid. But I guess after updating 72 fields the eyes get fuzzy. Look at my variable names - I used in_absentee_type as the variable to receive the nonstandard address. This, of course, means the value doesn't fit in the field. The Oracle error was a bit misleading. Thank you for leading me to the right solution! — user158017, Jul 21 '12 at 15:49

score 0 · Accepted Answer · answered Jul 21 '12 at 15:55

Well, it was one of those "duh" moments and a good argument for having coworkers who can be an extra set of eyes.

Look at my code - I fed the address into the absentee field. This is what I had:

    string[] NONSTANDARD_ADDRESS = dt
     .AsEnumerable()
     .Select(row => row.Field<string>("NONSTANDARD_ADDRESS"))
     .ToArray();
    OracleParameter in_nonstandard_address = new OracleParameter();
    **in_absentee_type**.OracleDbType = OracleDbType.Varchar2;
    **in_absentee_type**.Value = NONSTANDARD_ADDRESS;
    **in_absentee_type**.ParameterName = "in_nonstandard_address";
    cmd.Parameters.Add(in_nonstandard_address);

This is what I SHOULD have had:

       string[] NONSTANDARD_ADDRESS = dt
           .AsEnumerable()
           .Select(row => row.Field<string>("NONSTANDARD_ADDRESS"))
           .ToArray();
        OracleParameter in_nonstandard_address = new OracleParameter();
        **in_nonstandard_address**.OracleDbType = OracleDbType.Varchar2;
        **in_nonstandard_address**.Value = NONSTANDARD_ADDRESS;
        **in_nonstandard_address**.ParameterName = "in_nonstandard_address";
        cmd.Parameters.Add(in_nonstandard_address);

After staring at 72 arrays like the above, my eyes just totally missed it. Thanks to Bob Jarvis for suggesting something that made the syntax problem pop out at me.

Oracle ArrayBound Stored Procedure fails only with specific field

1 Answers1