PLSQLAssociativeArray

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

[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

[vonbrana]

Thanks, that works great.

[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.

[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