Solved

How to pass a arraylist in an oracleparameter array

Posted on 2011-03-17
3
980 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:bschave2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 5

Expert Comment

by:morgulo
ID: 35160969
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;
0
 

Author Comment

by:bschave2
ID: 35167038
could you give me an example of how I would create the bulk insert in oracle?
0
 
LVL 5

Accepted Solution

by:
morgulo earned 500 total points
ID: 35167953
PROCEDURE bulk_save(v_Name IN tt_string) IS
BEGIN
FORALL i IN 1 .. v_Name.COUNT
	INSERT INTO tab (name)
	VALUES(v_Name(i));
END;

Open in new window


tt_string is type from my previous comment.
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question