Solved

How to pass a arraylist in an oracleparameter array

Posted on 2011-03-17
3
932 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
  • 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

932 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now