I asked Mark Williams, Author of
Pro .NET Oracle Programming
</code> http://www.amazon.com/exec/obidos/redirect?tag=asktom03-20&path=tg%2Fdetail%2F-%2F1590594258

and he says:


Tom,

Here's my response:


It is true that the current version of ODP.NET does not support passing
a ref cursor as an IN parameter to a PL/SQL procedure or function;
however, the next version which will be released with the Oracle
Database 10g Release 2 for Windows will support this functionality.

There are currently two options for performing bulk bind operations
(which can be used to simulate a "record set") using ODP.NET:

- Use a host language array for an insert, update, or delete operation
in a SQL statement. You can not use a host language array for a select
operation. The "internals" reason for this is that ODP.NET utilizes the
Oracle Call Interface (OCI) and the OCIStmtExecute procedure within OCI
supports arrays for insert, update and delete operations only. This is
not an issue though as one typically uses an array to avoid single row
fetching from the database. You can perform multi-row fetching quite
easily by setting the FetchSize property (which defaults to 64K) on the
data reader object. For example, to fetch 128 rows at a time you set the
data reader object FetchSize property equal to the value of the command
object RowSize * 128 (i.e. dr.FetchSize = cmd.RowSize * 128).

- Use a PL/SQL Associative Array in conjunction with a stored procedure
or function. I really recommend placing the procedure or function in a
package. PL/SQL Associative Arrays (formerly known as index-by tables)
may be used in select, insert, update, and delete operations.

I cover using host language arrays in Chapter 2 and PL/SQL Associative
Arrays in Chapter 5 in my book. Chapter 5 - Using PL/SQL from .NET
(along with Chapter 7 - Advanced Connections and Authentication) can be
downloaded at the bottom of the ODP.NET homepage (as of 26-JUN-2005):

http://www.oracle.com/technology/tech/windows/odpnet/index.html <code>

It is important to note that in both cases the operation is a true bulk
operation. That is, the data is not sent row-by-row from the client or
"iterated over to perform a series of single row operations". The
operations are batched and performed as a single operation on the
server. I show this in the sample below.

Here is a simple example based on code from Chapter 5 in my book that
performs bulk inserts into a table in order to illustrate both of these
methods:

SQL> create table array_test
2 (
3 c number
4 );

Table created.

SQL> create or replace package associative_insert as
2 -- create a type for the column
3 type t_c is table of array_test.c%type index by binary_integer;
4
5 -- the procedure that will perform the work
6 procedure bulk_insert (p_c in t_c);
7 end associative_insert;
8 /

Package created.

SQL> create or replace package body associative_insert as
2 procedure bulk_insert (p_c in t_c) is
3 begin
4 forall i in p_c.first..p_c.last
5 insert into array_test (c)
6 values (p_c(i));
7 end bulk_insert;
8 end associative_insert;
9 /

Package body created.

SQL>

Create a new console application using VB.NET and add a reference to the
Oracle Data Provider for .NET assembly (Oracle.DataAccess.dll) to the
project. Here's the complete code from the module:

Imports System
Imports System.Data
Imports Oracle.DataAccess.Client

Module Module1

Sub Main()
'// create the connection using local db and o/s authentication
Dim con As OracleConnection = New OracleConnection("User Id=/")

'// open the connection to the database
con.Open()

'// call method to insert using array binding
arrayInsert(con)

'// truncate the table
truncateTable(con)

'// call method to insert using pl/sql associative array
associativeInsert(con)

'// truncate the table
truncateTable(con)

'// explicitly dispose the connection object
con.Dispose()
End Sub

Private Sub arrayInsert(ByVal con As OracleConnection)
'// sql statement to insert an array of values
Dim sql As String = "insert into array_test (c) values (:c)"

'// create an array of 1024 elements
'// remember that in vb the array subscript specifies
'// the upper bound not the number of elements
Dim c_vals(1023) As Integer

'// simple loop to populate the array
For i As Int32 = 0 To 1023
c_vals(i) = i
Next i

'// create the parameter object
Dim c As OracleParameter = New OracleParameter
c.OracleDbType = OracleDbType.Decimal
c.Value = c_vals

'// create the command object
Dim cmd As OracleCommand = New OracleCommand
cmd.Connection = con
cmd.CommandText = sql
cmd.ArrayBindCount = c_vals.Length

'// add the parameter object to the collection
cmd.Parameters.Add(c)

'// insert all 1024 rows in one round trip
cmd.ExecuteNonQuery()
End Sub

Private Sub associativeInsert(ByVal con As OracleConnection)
'// create the command object and set attributes
Dim cmd As OracleCommand = New
OracleCommand("associative_insert.bulk_insert", con)
cmd.CommandType = CommandType.StoredProcedure

'// create parameter object
Dim p_c As OracleParameter = New OracleParameter

'// set parameter type
p_c.OracleDbType = OracleDbType.Decimal

'// set the collection type for the parameter
'// this is vital to set when using PL/SQL Associative Arrays
p_c.CollectionType = OracleCollectionType.PLSQLAssociativeArray

'// create an array of 1024 elements
'// remember that in vb the array subscript specifies
'// the upper bound not the number of elements
Dim c_vals(1023) As Integer

'// simple loop to populate the array
For i As Int32 = 0 To 1023
c_vals(i) = i
Next i

'// set the parameter values
p_c.Value = c_vals

'// set the size for the array
p_c.Size = c_vals.GetLength(0)

'// add the parameter object to the collection
cmd.Parameters.Add(p_c)

'// execute the insert
Try
cmd.ExecuteNonQuery()
Catch ex As OracleException
Console.WriteLine(ex.Message)
End Try
End Sub

Private Sub truncateTable(ByVal con As OracleConnection)
'// create the command object
Dim cmd As OracleCommand = New OracleCommand
cmd.Connection = con
cmd.CommandText = "truncate table array_test"

'// truncate the table
cmd.ExecuteNonQuery()
End Sub
End Module

Turn on a 10046 level 4 trace and execute the code. The results from the
tkprof on my system show:

using array binding:

insert into array_test (c)
values
(:c)


call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.01 0.01 0 3 21
1024
Fetch 0 0.00 0.00 0 0 0
0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 2 0.01 0.01 0 3 21
1024

using PL/SQL Associative Array:

INSERT INTO ARRAY_TEST (C)
VALUES
(:B1 )


call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.00 0.00 0 2 21
1024
Fetch 0 0.00 0.00 0 0 0
0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 2 0.00 0.00 0 2 21
1024



As far as client software version I use the latest version that I can in
order to take advantage of new features, fixes, etc. For example, using
the 10g Release 1 or newer provider it is possible to take advantage of
statement caching. Using ODP.NET with the 10g Release 1 client is
backward compatible to 8.1.7.4.1 (this is an attribute of the client
software, not ODP.NET).

I hope that helps to illustrate how it is possible to perform a bulk
operation using ODP.NET with either an array or a PL/SQL Associative
Array.

- Mark