Solved

How to pass a arraylist in an oracleparameter array

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

759 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

19 Experts available now in Live!

Get 1:1 Help Now