PLSQLAssociativeArray

PLSQLAssociativeArray

Postby vonbrana » Tue 06 Dec 2011 15:12

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);
vonbrana
 
Posts: 8
Joined: Sun 04 Dec 2011 00:10

Postby Pinturiccio » Wed 07 Dec 2011 14:04

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
Pinturiccio
Devart Team
 
Posts: 2046
Joined: Wed 02 Nov 2011 09:44

Postby vonbrana » Wed 07 Dec 2011 20:26

Thanks, that works great.
vonbrana
 
Posts: 8
Joined: Sun 04 Dec 2011 00:10

Re: PLSQLAssociativeArray

Postby pjbeaton » Thu 31 Jan 2013 21:02

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.
pjbeaton
 
Posts: 1
Joined: Thu 31 Jan 2013 20:06

Re: PLSQLAssociativeArray

Postby Shalex » Fri 01 Feb 2013 16:10

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
Shalex
Devart Team
 
Posts: 7839
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle