Pass collections of data to PL/SQL Package Procedure or Function from .NET?

Question

I have a table that I need to insert a set of records into based on user input. IE: Selection from a list of items to choose from. For this example, let's just say it's a collection of int's.

Not a lot of good examples on the internet. There's only one related question here on SO, but it lends no answers either (both answers are dead ends).

1. How do you setup the call into the Package from .NET?
2. How do you setup the Package Procedure or Function to receive the collection?
3. How do you process the collection once inside of the Procedure or Function?

Answer 1

The last time I had to work with Oracle from .NET I realized pretty heavily on Pro .NET Oracle Programming by Mark A. Williams. That's been 4 or more years, but I found it well written and quite useful.

Answer 2

Although I cannot speak from a .NET perspective, I have had similar issues with Java code. Perhaps this solution will be of some use...

Begin by creating a new TYPE in the database capable of storing a collection of elements. I typically use a TABLE type:

CREATE TYPE my_array_type IS TABLE OF NUMBER
    INDEX BY BINARY_INTEGER;

You then create a procedure capable of accepting a parameter of type MY_ARRAY_TYPE:

CREATE PROCEDURE process_array(my_array MY_ARRAY_TYPE) IS
  i BINARY_INTEGER;
  my_array_element NUMBER;
BEGIN
  i := my_array.FIRST;
  WHILE my_array.EXISTS(i) LOOP
    my_array_element := my_array(i);
    /* do something with my_array_element... */
    i := my_array.NEXT(i);
  END LOOP;
END;

The next trick is to dynamically build an anonymous PL/SQL block in your programming language of choice (I presume VB.NET or C#) and execute it. I can't speak much to .NET, but here is an example in Java:

int[] myArray = ...;

StringBuilder plsql = new StringBuilder();
plsql.append("DECLARE");
plsql.append("  my_array MY_ARRAY_TYPE;");
plsql.append("BEGIN");
for (int i = 0; i < myArray.length; i++) {
    plsql.append("  my_array(" + i + ") := ?;");
}
plsql.append("  PROCESS_ARRAY(my_array);");
plsql.append("END");

CallableStatement statement = connection.prepareCall(plsql.toString());
for (int i = 0; i < myArray.length; i++) {
    statement.setInt(i + 1, myArray[i]);
}

statement.execute();

I know there are better ways, but this seemed to be the most reliable, especially in the Java/JDBC world where only a subset of all of Oracle's JDBC drivers (!) support collection types.

Answer 3

Look at Oracle technet how to section of odp.net. There is an example binding an array. But the procedure is called n-times. The parameters of plsql procedure are of plain type (valuetype).

There is another example passing the array at once. The parameters of plsql procedure are plsql tables. Processing collections (=plsql tables) is described her. That may suits more to your problem.