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

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
rayrad
Posts: 5
Joined: Sat 27 Nov 2010 18:54
Location: Kirkland, WA

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

Post by 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

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by 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

rayrad
Posts: 5
Joined: Sat 27 Nov 2010 18:54
Location: Kirkland, WA

Post by 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

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by 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.

Post Reply