Passing an array from .Net application to Oracle stored procedure

Question

I need to pass an array from C#.net application to oracle stored procedure. Can anyone please let me know how to go about it? Also, which OracleType type do I use in C# when passing input parameter to stored procedure?

FYI, am using System.Data.OracleClient in my C# app.

Thanks.

Answer 1

You should use DB-defined nested table types to pass an array to PL/SQL. Take a look at this nice tutorial, it might help you.

Answer 2

Jimmy, the System.Data.OracleClient does not support arrays.
http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/57a59fba-09e2-46cb-a072-6706b3ec5131

Depending on your need, you will either need to implement this via nested table types (via UDTs or assoc. arrays) using the ODP.net clients. This is probably a good direction to go, esp. since .net 4.0 has deprecated System.Data.OracleClient.

But if you are stuck with the System.Data.OracleClient and you are really just trying to do a variable in-list, you can do this by passing in a string and following some of these suggestions: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425

hope this helps