Pass PL/SQL associative array to Oracle stored procedure from C#

The ArrayBind feature of ODP.NET comes allows passing arrays to a stored procedure. Since all parameters should be arrays of equal length, it may not be the right choice if not all parameters are arrays. Say, we want to call a procedure that takes a user name and an array of role names. One way to accomplish this is to pass a delimited string for role names and parse it inside the procedure.

PROCEDURE create_user (
       i_user_name        IN VARCHAR2,
       i_delim_role_list  IN VARCHAR2)
IS
   l_user_id app_user.user_id%TYPE;
BEGIN
   INSERT INTO app_user
     (user_id, user_name, first_name, last_name)
   VALUES
     (user_seq.nextval, i_user_name, i_first_name, i_last_name)
   RETURNING id INTO l_user_id;

   --parse i_delim_role_list and insert
   --INSERT INTO app_user_role
   --  (user_id, role_name)
   --VALUES
   --  (l_user_id, l_role_name);
END;

Another way is to use PL/SQL associative arrays instead. First define the PL/SQL associative array types in an Oracle package. I prefer to declare them in a separate package for re-usability.

PACKAGE PKG_TYPES
IS
  type t_varchar2 is table of varchar2(4000) index by pls_integer;
  type t_number is table of number index by pls_integer;
  type t_date is table of date index by pls_integer;
END;

Next modify the procedure and set the i_role_name parameter type to pkg_types.t_varchar2 defined above.

PROCEDURE create_user (
    i_user_name  IN VARCHAR2,
    i_role_name  IN pkg_types.t_varchar2)
IS
    l_user_id app_user.user_id%TYPE;
BEGIN
    INSERT INTO app_user
      (user_id, user_name, first_name, last_name)
    VALUES
      (user_seq.nextval, i_user_name, i_first_name, i_last_name)
    RETURNING id INTO l_user_id;

    --iterate the roles array and insert
    FOR x IN i_role_name.FIRST .. i_role_name.LAST
    LOOP
        INSERT INTO app_user_role
        (user_id, role_name)
        VALUES
        (l_user_id, i_role_name(x));
    END LOOP;
END;

Finally the C# code to call the stored procedure.

var con = new OracleConnection("connection string");

// the CreateCommand extension method is taken from
// http://www.vickram.me/oracle-createcommand-extension-method
var com = con.CreateCommand("create_user");

com.Parameters.Add("i_user_name", OracleDbType.Varchar2, userName, ParameterDirection.Input);

com.Parameters.AddArray<string>(
    name:      "i_role_name",
    dbType:    OracleDbType.Varchar2, 
    array:     new string[] { "TEAM_ADMIN", "ALL_REPORTS" },
    direction: ParameterDirection.Input, 
    emptyArrayValue: null);

The AddArray method is an extension that I created to simplify the code. It is important to set the CollectionType property of each Oracle parameter object to OracleCollectionType.PLSQLAssociativeArray for this to work.

public static void AddArray<T>(
       this OracleParameterCollection parameters,
       string name,
       OracleDbType dbType,
       T[] array,
       ParameterDirection dir,
       T emptyArrayValue)
{
    parameters.Add(new OracleParameter {
                ParameterName = name,
                OracleDbType = dbType,
                CollectionType = OracleCollectionType.PLSQLAssociativeArray
            });

    // oracle does not support passing null or empty arrays.
    // so pass an array with exactly one element
    // with a predefined value and use it to check
    // for empty array condition inside the proc code
    if (array == null || array.Length == 0)
    {
        parameters[name].Value = new T[1] { emptyArrayValue };
        parameters[name].Size = 1;
    }
    else
    {
        parameters[name].Value = array;
        parameters[name].Size = array.Length;
    }
}

Oracle does not support passing null or empty arrays to the PL/SQL array types. I get around this by sending a one-element array with a predefined value. Later in the procedure I check for this condition and act accordingly.

IF i_role_name.COUNT = 1 AND i_role_name (1) IS NULL
THEN
    --handle empty array scenario. raise error?
ELSE
    --array is not empty
END IF;

Here is another procedure that takes three parameters as PL/SQL associative arrays.

PROCEDURE update_employees (
    i_employee_id     IN pkg_types.t_varchar2,
    i_new_salary      IN pkg_types.t_number,
    i_account_expiry  IN pkg_types.t_date)
IS
BEGIN
   FOR x IN i_employee_id.FIRST .. i_employee_id.LAST
   LOOP
      UPDATE employee 
         SET salary = i_new_salary (x),
             account_expiry = i_account_expiry (x)
       WHERE employee_id  = i_employee_id (x);
   END LOOP;
END;

Here emps is of type List<Employee>.

var con = new OracleConnection("connection string");

// the CreateCommand extension method is taken from
// http://www.vickram.me/oracle-createcommand-extension
var com = con.CreateCommand("update_employees");

com.Parameters.AddArray<string>(
       name:      "i_employee_id",
       dbType:    OracleDbType.Varchar2, 
       array:     emps.Select(x => x.EmployeeId).ToArray(),
       direction: ParameterDirection.Input, 
       emptyArrayValue: null);

com.Parameters.AddArray<decimal>(
       name:      "i_new_salary",
       dbType:    OracleDbType.Number, 
       array:     emps.Select(x => x.Salary).ToArray(),
       direction: ParameterDirection.Input, 
       emptyArrayValue: 0);

com.Parameters.AddArray<DateTime?>(
       name:      "i_account_expiry",
       dbType:    OracleDbType.Number, 
       array:     emps.Select(x => x.AccountExpiry).ToArray(),
       direction: ParameterDirection.Input, 
       emptyArrayValue: null);

http://www.oracle.com/technetwork/issue-archive/2009/09-sep/o59odpnet-085168.html

http://www.oracle.com/technetwork/issue-archive/2007/07-jan/o17odp-093600.html