Reducing Loop Overhead for Collections with Bulk Binds in Oracle Options

codeling Posts: 1085 Points: 4548
Posted: Tuesday, January 26, 2016 2:20:57 PM

Example: Performing a Bulk Bind with INSERT

In the example below, 5000 part numbers and names are loaded into index-by tables. All table elements are inserted into a database table twice: first using a FOR loop, then using a FORALL statement. The FORALL version is much faster.

SQL> CREATE TABLE parts (pnum NUMBER(4), pname CHAR(15));
Table created.
SQL> GET test.sql
 4     pnums  NumTab;
 5     pnames NameTab;
 6     t1 NUMBER(5);
 7     t2 NUMBER(5);
 8     t3 NUMBER(5);
12     FOR j IN 1..5000 LOOP  -- load index-by tables
13        pnums(j) := j;
14        pnames(j) := 'Part No. ' || TO_CHAR(j); 
15     END LOOP;
16     t1 := dbms_utility.get_time;
17     FOR i IN 1..5000 LOOP  -- use FOR loop
18        INSERT INTO parts VALUES (pnums(i), pnames(i));
19     END LOOP;
20     t2 := dbms_utility.get_time;
21     FORALL i IN 1..5000  -- use FORALL statement
22        INSERT INTO parts VALUES (pnums(i), pnames(i));
23     get_time(t3);
24     dbms_output.put_line('Execution Time (secs)');
25     dbms_output.put_line('---------------------');
26     dbms_output.put_line('FOR loop: ' || TO_CHAR(t2 - t1));
27     dbms_output.put_line('FORALL:   ' || TO_CHAR(t3 - t2));
28* END;
SQL> /
Execution Time (secs)
FOR loop: 32
PL/SQL procedure successfully completed.

codeling Posts: 1085 Points: 4548
Posted: Thursday, May 4, 2017 3:18:56 PM

How to use .NET, PL/SQL associative array binding to reduce network round-trips?

ODP.NET developers can use PL/SQL associative array binding to reduce network round-trips. The result is a reduced workload for both the network and Oracle Database, which means faster performance and better scalability for your applications.

Suppose that one of the reasons for the PL/SQL and performance mandates is that your company is growing rapidly: The IT department alone has three new job classifications—database administrator, manager, and vice president. the following code demonstrates how to use .NET, PL/SQL, and associative arrays to add these job categories to an Oracle database—with minimal network utilization.

The sample developed in this column uses the HR schema provided with Oracle Database. This schema includes a JOBS table that contains a row for each job.

The PL/SQL Code

PL/SQL code will provide the interface between the application and the database. The associative_array package specification and body code in Listing 1 are the interface, and it runs in the database's HR schema. (Note that a Microsoft Visual Studio developer might use Oracle Developer Tools for Visual Studio .NET or a tool such as Oracle SQL Developer to create and edit the PL/SQL code.)

Code Listing 1: The PL/SQL code 

create or replace package associative_array as
  -- define an associative array type for each column in the jobs table

  type t_job_id is table of jobs.job_id%type index by pls_integer;
  type t_job_title is table of jobs.job_title%type index by pls_integer;
  type t_min_salary is table of jobs.min_salary%type index by pls_integer;
  type t_max_salary is table of jobs.max_salary%type index by pls_integer;

  -- define the procedure that will perform the array insert

  procedure array_insert (p_job_id in t_job_id,
                                   p_job_title in t_job_title,
                                   p_min_salary in t_min_salary,
                                   p_max_salary in t_max_salary);
end associative_array;

create or replace package body associative_array as
  -- implement the procedure that will perform the array insert

  procedure array_insert (p_job_id in t_job_id,
                                   p_job_title in t_job_title,
                                   p_min_salary in t_min_salary,
                                   p_max_salary in t_max_salary) is
    forall i in p_job_id.first..p_job_id.last
    insert into jobs (job_id,
              values (p_job_id(i),
  end array_insert;
end associative_array;


The associative_array package contains code for both the package specification and the package body. The package specification declares the single procedure that will be implemented in the package body as well as four datatypes that define the parameter types to the procedure. Each type represents a column in the JOBS table, which has the following structure: 

SQL> desc jobs

 Name            Null?     Type
 ----------      -------   --------------
 JOB_ID          NOT NULL  VARCHAR2(10)
 MIN_SALARY                NUMBER(6)
 MAX_SALARY                NUMBER(6)


Because each datatype in the associative_array package is defined to be a table of each database column type, the package code effectively creates four arrays that match the types of their respective columns in the JOBS table.

For example, the t_job_id type is declared to be a single-column table (a PL/SQL table, not a database table) whose type matches that of the job_id column in the JOBS table.

Each single-column PL/SQL table is essentially an array. The array_insert procedure in the associative_array package body takes four parameters (one for each column in the table); each parameter is an array of values supplied by the .NET client application.

The statement for inserting the rows into the JOBS table resembles a traditional INSERT statement, except that it uses the FORALL keyword and that the inserted values are identified by a lowercase i .

The FORALL keyword allows PL/SQL to process all of the elements in the associative array as a group rather than looping over the array, as with a typical FOR LOOP statement.

The lower and upper bounds of the array are indicated by the first and last methods. The lowercase i in the values clause identifies the correct element in the array for PL/SQL retrieval.

Creating the C# Code

With the PL/SQL interface code available in the Oracle database instance, it's time to deploy the .NET client application code, shown in Listing 2.

Code Listing 2: The .NET C# code 

using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace JanFeb2007 {
  class Program {
    static void Main(string[] args) {
      // connection string - make sure to adjust for your environment
      string constr = "user id=hr; password=hr; data source=oramag; enlist=false; pooling=false";

      // create and open connection object
      OracleConnection con = new OracleConnection(constr);

      // insert the new jobs into the jobs table
      // create command object and set attributes
      OracleCommand cmd = con.CreateCommand();
      cmd.CommandText = "associative_array.array_insert";
      cmd.CommandType = CommandType.StoredProcedure;

      // create parameter objects for each parameter
      OracleParameter p_job_id = new OracleParameter();
      OracleParameter p_job_title = new OracleParameter();
      OracleParameter p_min_salary = new OracleParameter();
      OracleParameter p_max_salary = new OracleParameter();

      // set parameter type for each parameter
      p_job_id.OracleDbType = OracleDbType.Varchar2;
      p_job_title.OracleDbType = OracleDbType.Varchar2;
      p_min_salary.OracleDbType = OracleDbType.Decimal;
      p_max_salary.OracleDbType = OracleDbType.Decimal;

      // set the collection type for each parameter
      p_job_id.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
      p_job_title.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
      p_min_salary.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
      p_max_salary.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

      // set the parameter values
      p_job_id.Value = new string[3] { "IT_DBA", "IT_MAN", "IT_VP" };
      p_job_title.Value = new string[3] { "Database Administrator", "IT Manager", "IT Vice President" };
      p_min_salary.Value = new decimal[3] { 8000, 12000, 18000 };
      p_max_salary.Value = new decimal[3] { 16000, 24000, 36000 };

      // set the size for each array
      p_job_id.Size = 3;
      p_job_title.Size = 3;
      p_min_salary.Size = 3;
      p_max_salary.Size = 3;

      // add parameters to command object collection

      // execute the insert

      // display the new jobs
      cmd.CommandText = "select job_id, job_title from jobs where job_id in 
('IT_DBA', 'IT_MAN', 'IT_VP') order by job_id";
      cmd.CommandType = CommandType.Text;

      OracleDataReader dr = cmd.ExecuteReader();

      Console.WriteLine("New jobs have been added to the JOBS table:\n");

      while (dr.Read()) {
        Console.WriteLine("{0,6}: {1}", dr.GetString(0), dr.GetString(1));


      // delete the new jobs
      cmd.CommandText = "delete from jobs where job_id in ('IT_DBA', 'IT_MAN', 'IT_VP')";

      Console.WriteLine("New jobs have been removed from the JOBS table.");

      // clean up objects

      // simple prompt to keep output window from closing when executing from IDE
      Console.WriteLine("Press ENTER to continue...");


Because the data processing logic is in the database, the client code is concerned largely with creating parameters, setting values, and invoking the code in the database. Note that the code for inserting data contains no SQL statements, because the PL/SQL interface has been colocated with the data in the database.

Users browsing this topic