asked on

How to pass a arraylist in an oracleparameter array

I have an oracleParameter array that I add parameters to. Usually this works fine if I put in a single value. In this case I am trying to push in an array so that I can complete a bulk insert. My co-worker has successfully done this, but not with a oracleparameter array, onlly doing it like cmd.parameter... My code is attached, hopefully, I can use this otherwise is there an alternative that doesn't involve splitting strings in my procedure?

namespace CallOptionsAvailability
{
    public class CallOption
    {

        public DAL dataAccessOPR;
        public DAL dataAccessTrades;
        public validate valid;
        DataSet ds;
        public DataTable dt = new DataTable();

        public const string opr = "opr";
        public const string trades = "trades";

        public int ContractId;
        public string ProductName;
        public string CallOptionName;
        public string CounterNotification;
        public string CounterEmail;
        public string EMONotification;
        public string EMOEmail;
        public string TradesNo;
        public string DefaultOptionVolume;
        public string PORAvailability;
        public string MW;
        public string AdditionalMW;
        public string SellerAvailabilityBy;
        public string EMOStrikeBy;
        public string MinRunTime;
        public string MinDownTime;
        public string StartsPerDay;
        public string EMOIntradayChange;
        public string MinStarts;
        public string MaxStarts;
        public string StartCharge;
        public string Comments;

        //lists for database bulk inserts.
        public List<string> LProductName = new List<string>();
        public List<string> LCallOptionName = new List<string>();
        public List<string> LCounterNotification = new List<string>();
        public List<string> LCounterEmail = new List<string>();
        public List<string> LEMONotification = new List<string>();
        public List<string> LEMOEmail = new List<string>();
        public List<string> LTradesNo = new List<string>();
        public List<string> LDefaultOptionVolume = new List<string>();
        public List<string> LPORAvailability = new List<string>();
        public List<string> LMW = new List<string>();
        public List<string> LAdditionalMW = new List<string>();
        public List<string> LSellerAvailabilityBy = new List<string>();
        public List<string> LEMOStrikeBy = new List<string>();
        public List<string> LMinRunTime = new List<string>();
        public List<string> LMinDownTime = new List<string>();
        public List<string> LStartsPerDay = new List<string>();
        public List<string> LEMOIntradayChange = new List<string>();
        public List<string> LMinStarts = new List<string>();
        public List<string> LMaxStarts = new List<string>();
        public List<string> LStartCharge = new List<string>();
        public List<string> LComments = new List<string>();

        public OracleParameter[] parms;


        private void createOracleParms()
        {
            parms = new OracleParameter[2]
            {
                new OracleParameter("v_Contract_Id", ContractId),
                new OracleParameter("v_Name", LProductName)
            };
        }


        public void InsertCallOptions()
        {
            //Creates a list<> for each sql parameter.
            this.populateLists();
            this.createOracleParms();

            dataAccessOPR = new DAL(opr);
            dataAccessOPR.executeNonQuery("CALL_OPTION_AVAILABILITY", parms);
            
            clearLists();
	}
            
    }
}

Open in new window

morgulo

You should create array parameter like this:

OracleParameter p = new OracleParameter("v_Name", OracleDbType.Varchar2);
p.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
p.Size = LProductName.Count;
p.Value = LProductName.ToArray();

Open in new window

You can set p.ArrayBindSize is you want set up strings lenth.

You should also create db procedure to bulk insert with array parameter, ie:
TYPE tt_string is table of varchar(500) index by binary_integer;

bschave2

ASKER

could you give me an example of how I would create the bulk insert in oracle?

ASKER CERTIFIED SOLUTION

morgulo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial