Page 1 of 1
PLSQLAssociativeArray
Posted: Tue 06 Dec 2011 15:12
by vonbrana
I am porting some code from ODP to DevArt. I ran into a problem impletmenting the following ODP code in terms of DevArt. The code uses a parameter of collection type PLSQLAssociativeArray to post an array of values to the server in a single parameter. How is this done with the DevArt provider?
oc.CommandType = CommandType.StoredProcedure;
OracleParameter p1 = new OracleParameter();
p1.ParameterName = "P_CONTACTSSARR";
p1.Direction = ParameterDirection.Input;
p1.OracleDbType = OracleDbType.VarChar;
p1.Value = contactArray;
p1.Size = contactArray.Length;
p1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
oc.Parameters.Add(p1);
Posted: Wed 07 Dec 2011 14:04
by Pinturiccio
If you want to port your snippet of code from ODP to Devart you should change it in the following way:
Code: Select all
OracleParameter pa1 = new OracleParameter();
pa1.ParameterName = "P_CONTACTSSARR";
pa1.Direction = System.Data.ParameterDirection.Input;
pa1.OracleDbType = OracleDbType.VarChar;
pa1.ArrayLength = contactArray.Length;
pa1.Value = contactArray;
oc.Parameters.Add(pa1);
Devart.Data.Oracle.OracleParameter does not have the CollectionType property. If you set the Devart.Data.Oracle.OracleParameter.ArrayLength, then this parameter will be PL/SQL AssociativeArray (PL/SQL Table). For more details please refer
http://www.devart.com/dotconnect/oracle ... Table.html
If you want use a nested table or varrays, set pa1.OracleDbType to Array or Table respectively. For more details please refer:
http://www.devart.com/dotconnect/oracle ... bType.html
http://www.devart.com/dotconnect/oracle ... array.html
Posted: Wed 07 Dec 2011 20:26
by vonbrana
Thanks, that works great.
Re: PLSQLAssociativeArray
Posted: Thu 31 Jan 2013 21:02
by pjbeaton
How can I implement the same type of call to a stored procedure with an associative array input parameter using Entity Framework 5.0? I can't figure out which type to make the input parameters so it will work.
Re: PLSQLAssociativeArray
Posted: Fri 01 Feb 2013 16:10
by Shalex
Entity Framework supports only primitive types:
http://msdn.microsoft.com/en-us/library/ee382832.aspx. From the Entity Framework's point of view, a associative array is not a primitive type.
These are examples how you can execute plain SQL directly via myDbContext.Database.ExecuteSqlCommand or myObjectContext.ExecuteStoreQuery:
http://forums.devart.com/viewtopic.php?f=1&t=22638
http://forums.devart.com/viewtopic.php?t=23756