Possible to pass array/table param to SP via LinqConnect?

Possible to pass array/table param to SP via LinqConnect?

Postby rayrad » Sat 27 Nov 2010 20:02

I am evaluating dotConnect for Oracle to use to call an Oracle 10g XE database from a Sitefinity 3.7 (SP4) CMS installation. While I have a good deal of experience in C#, LINQ, SQL Server, and programming in general, I am fairly new to Oracle.

The environment is:
    Visual Studio 2010
    .NET Framework 3.5
    LinqConnect (rather than EF, because we are using .NET 3.5)
    C#
    Oracle 10g XE
    Doing my Oracle work in SQL Developer (not SQL*Plus)
My question is, is it possible to pass a simple array, associative array (key/value pairs), and/or a table to a stored procedure in Oracle via dotConnect for Oracle. I know that Oracle itself supports passing either an array or table to a procedure, but I do not know if it is possible to leverage this functionality via dotNetConnect for Oracle using LINQConnect.

Thanks,
Dave Parker
Coherent Interactive
rayrad
 
Posts: 2
Joined: Sat 27 Nov 2010 18:54
Location: Kirkland, WA

Postby StanislavK » Mon 29 Nov 2010 10:00

Oracle user-defined types, including array and table types, are not supported in LinqConnect. However, you can use OracleArray and OracleTable components to simplify working with Oracle array and table types.

For detailed information about the dotConnect for Oracle support for Oracle UDTs, please refer to the corresponding topics in the 'Working with Data' section of our documentation:
http://www.devart.com/dotconnect/oracle/docs/Data.html
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby rayrad » Mon 29 Nov 2010 18:05

If I understand your reply correctly, you are saying that in order to pass a varray or table object to an Oracle stored procedure, I need to use the dotConnect for Oracle native API, rather than the O/R mapping layer that LinqConnect provides. Have I got that right?

If so, then is it possible to do what I want using .NET Framework 4.0 and a DevArt Entity Model, in other words, LINQ to EF?

Thanks,
Dave Parker
Coherent Interactive
rayrad
 
Posts: 2
Joined: Sat 27 Nov 2010 18:54
Location: Kirkland, WA

Postby AndreyR » Thu 02 Dec 2010 14:28

Yes, it is possible to call the procedures with, for example, the OracleTable parameter. Use the ExecuteStoreCommand method in the similar syntax:
Code: Select all
db.ExecuteStoreCommand("begin TEMPPROC(:p1); end;",
  new Devart.Data.Oracle.OracleParameter("p1", Devart.Data.Oracle.OracleDbType.Table,
  oraTable, System.Data.ParameterDirection.Input));
, where oraTable is a valid OracleTable instance.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for Oracle