Solved

How to pass a arraylist in an oracleparameter array

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

679 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