How to pass Oracle array parameter from ado.net c# to oracle database

This is assuming use of Oracle ODP.NET and you've already got a connection. Also assumes the called package/procedure having one or more input parameters of type table (Oracle's implementation of array).

You can accomplish using Oracle parameter class, and setting the collection type of the parameter to array.

 using System.Data;
 using Oracle.DataAccess.Client;
 using Oracle.DataAccess.Types;

 // create command object and set attributes
 OracleCommand cmd = con.CreateCommand();
 cmd.CommandText = "job_package.add_jobs";
 cmd.CommandType = CommandType.StoredProcedure;

 // create parameter objects for each parameter
 OracleParameter p_job_id = new OracleParameter();
 OracleParameter p_job_title = new OracleParameter();
 OracleParameter p_salary = new OracleParameter();

 // set parameter type for each parameter
 p_job_id.OracleDbType = OracleDbType.Varchar2;
 p_job_title.OracleDbType = OracleDbType.Varchar2;
 p_salary.OracleDbType = OracleDbType.Decimal;

 // set the collection type for each parameter
 p_job_id.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
 p_job_title.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
 p_salary.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

 // set the parameter values
 p_job_id.Value = new string[3] { "IT_DBA", "IT_DEV", "IT_MGR" };
 p_job_title.Value = new string[3] { "DBA", "IT Developer", "IT Manager" };
 p_salary.Value = new decimal[3] { 18000, 48000, 120000};

 // set the size for each array
 p_job_id.Size = 3;
 p_job_title.Size = 3;
 p_salary.Size = 3;

 // add parameters to command object collection
 cmd.Parameters.Add(p_job_id);
 cmd.Parameters.Add(p_job_title);
 cmd.Parameters.Add(p_salary);

  // execute the insert
  cmd.ExecuteNonQuery();

  // clean up 
  p_salary.Dispose();
  p_job_title.Dispose();
  p_job_id.Dispose();
  cmd.Dispose();
  con.Dispose();