Array Binding

In This Topic

Using the Oracle array binding feature can greatly speed up execution of the application when inserting or updating big volumes of data. The main advantage is that array binding allows you to execute several INSERT SQL statements with the different parameters for the one time. Note that you access Oracle server only once that increases the speed of update a lot.

The following Oracle table definition will be used in our VB and C# samples:

CREATE TABLE dept
(
  deptno NUMBER(2) CONSTRAINT pk_dept PRIMARY KEY,
  dname VARCHAR2(14),
  loc VARCHAR2(13)
);

At first, you should create an object of OracleConnection class and open a connection to Oracle database:

Please note that it is only a cut of sample code. The full text will be given at the end of this article.

After that you should create an instance of OracleCommand class associated with the OracleConnection object and specify SQL statement for the execution:

Colons at SQL text mean parameters with the values which will be specified later.

Now for each parameter from SQL you should associate an object of OracleParameter class and add this object to the collection of the parameters of OracleCommand objects. Association is accomplished by the names of parameters in following way:

You should specify the name of each parameter and its type. For VarChar/NVarChar parameters it is also recommended to specify size of the parameter explicitly. This size should be more or equal to the size (in bytes) of the longest string in the array. You may omit specifying the size. In this case it will be determined automatically, however this may cause some performance overhead.

The next important point is assigning values to the parameters. Parameter values should be arrays with the length corresponding to the number of SQL statement executions. And each item of the array must correspond to the single execution of the SQL statement.

After accomplishing previous steps you should call ExecuteArray method that assumes a parameter specifying how many times SQL statement will be executed. Note that the value of this method argument must be equal to the number of elements of parameters value. Now with any Oracle tool (you can use dbForge Studio for Oracle for this purpose) execute SELECT * FROM Dept and you can see four new records appended.

Following is a sample code that executes several INSERT operations using array binding.

public void ArrayBindInsert()
{
  // Create connection object
  OracleConnection conn = new OracleConnection(
     "User Id=Scott;Password=tiger;Data Source=Ora");

  // Open connection
  conn.Open();

  // Create command object with previously opened connection
  OracleCommand cmd = conn.CreateCommand();

  // Set command text property
  cmd.CommandText = "INSERT INTO dept VALUES(:deptno_p, :dname_p, :loc_p)";

  // Add parameters to command parameters collection
  cmd.Parameters.Add("deptno_p", OracleDbType.Integer);
  cmd.Parameters.Add("dname_p", OracleDbType.VarChar);
  cmd.Parameters.Add("loc_p", OracleDbType.VarChar);

  // Set parameters values
  cmd.Parameters["deptno_p"].Value = new int[] { 10, 20, 30, 40 };
  cmd.Parameters["dname_p"].Value = new string[] { 
     "ACCOUNTING", "RESEARCH", "SALES", "OPERATIONS" };
  cmd.Parameters["loc_p"].Value = new string[] { 
     "NEW YORK", "DALLAS", "CHICAGO", "BOSTON" };

  // Insert four records at one time
  cmd.ExecuteArray(4);

  // Dispose command
  cmd.Dispose();

  // Close connection
  conn.Close();
}

Visual Basic



Public Sub ArrayBindInsert()
  ' Create connection object
  Dim conn As OracleConnection = new OracleConnection( _
     "User Id=Scott;Password=tiger;Data Source=Ora")

  ' Open connection
  conn.Open()

  ' Create command object with previously opened connection
  Dim cmd As OracleCommand = conn.CreateCommand()

  ' Set command text property
  cmd.CommandText = "INSERT INTO dept VALUES(:deptno_p, :dname_p, :loc_p)"

  ' Add parameters to command parameters collection
  cmd.Parameters.Add("deptno_p", OracleDbType.Integer)
  cmd.Parameters.Add("dname_p", OracleDbType.VarChar)
  cmd.Parameters.Add("loc_p", OracleDbType.VarChar)

  ' Set parameters values
  Dim deptno(3) As Integer
  deptno(0) = 10
  deptno(1) = 20
  deptno(2) = 30
  deptno(3) = 40

  Dim dname(3) As String
  dname(0) = "ACCOUNTING"
  dname(1) = "RESEARCH"
  dname(2) = "SALES"
  dname(3) = "OPERATIONS"

  Dim loc(3) As String
  loc(0) = "NEW YORK"
  loc(1) = "DALLAS"
  loc(2) = "CHICAGO"
  loc(3) = "BOSTON"

  cmd.Parameters("deptno_p").Value = deptno
  cmd.Parameters("dname_p").Value = dname
  cmd.Parameters("loc_p").Value = loc

  ' Insert four records at one time
  cmd.ExecuteArray(4)

  ' Dispose command
  cmd.Dispose()

  ' Close connection
  conn.Close()
End Sub

So this sample demonstrates how you can insert several records by performing single execute operation. It is important to know that necessary condition of working with array binding feature is assigning array of the items to the parameter value and executing ExecuteArray(int iters) method of the OracleCommand class. The number of times to execute SQL statement within one round trip (iters) should be less than 65535.

Oracle 12c adds the possibility to get the number of rows affected for each array element. dotConnect for Oracle adds the ExecuteArray method overload, with the second out parameter, for this purpose. This parameter, an array of integers, returns the numbers of rows affected. This overload can be used in only OCI mode and only for Oracle 12c both server and client.

See Also