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;
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;

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 ... Table.html

If you want use a nested table or varrays, set pa1.OracleDbType to Array or Table respectively. For more details please refer: ... bType.html ... array.html
Devart Team
Posts: 1866
Joined: Wed 02 Nov 2011 09:44

Postby vonbrana » Wed 07 Dec 2011 20:26

Thanks, that works great.
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.
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: 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:
Devart Team
Posts: 7391
Joined: Thu 14 Aug 2008 12:44

Return to dotConnect for Oracle