Using PL/SQL Associative Arrays

Bring the power of PL/SQL stored procedures to ODP.NET.

By Mark A. Williams Oracle ACE

January/February 2007

Suppose you've just received an e-mail detailing several IT initiatives, two of which will affect your future application development techniques. First, all applications should be designed to minimize network utilization. And second, applications that use Oracle databases must use PL/SQL for access to the database structures.

Knowing that you need to use PL/SQL in the database as the application-programming interface (API) and that you need to reduce network usage, where do you begin? Fortunately, ODP.NET's support for PL/SQL associative arrays can help you meet both IT mandates.

ODP.NET developers can use PL/SQL as an API to the data in the database and use 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.

The Sample Application

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. This column 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 jobscmd.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
      con.Dispose();// 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.

To test the new PL/SQL interface, run the C# console application—available at—in Visual Studio.

First, change the connection string to the appropriate values for your Oracle database instance so ODP.NET can pass associative arrays, then compile the code in Visual Studio, and then select Debug -> Step Into from the Visual Studio menu to see how it works.

As you step through the code, note that the application creates an OracleCommand object with the CommandText property value set to the PL/SQL package name and procedure (associative_array.array_insert).

Next, the application creates Oracle-Parameter objects for each parameter to the PL/SQL stored procedure. The application then sets each parameter's type to the OracleDbType to match that of the targeted column for each parameter.

One key point to note as you step through the code is that the application must correctly set the collection type for each parameter to OracleCollectionType.PLSQLAssociativeArray for associative arrays to work correctly. This is what enables the ODP.NET application to bind an OracleParameter object, as a PL/SQL associative array, to the PL/SQL stored procedure (see Figure 1).

figure 1
Figure 1: Stepping through the sample

After setting the collection type, the application assigns the values (for the three new jobs) that will be inserted into the JOBS table. Next, the application sets the size of each array, adds the parameters to the OracleCommand object's parameter collection, and invokes the stored procedure to insert the data.

Finally, the application queries the table to show that the three new jobs (IT_DBA, IT_MAN, and IT_VP) were successfully added to the JOBS table.

Going Further

PL/SQL associative arrays are not limited to INSERT operations. You can also use them to perform SELECT, UPDATE, and DELETE operations. I encourage you to experiment further with PL/SQL associative arrays to discover how your applications may benefit from centralized data access via a PL/SQL API as well as from a mechanism for bulk data sharing between Oracle databases and .NET middle tiers.

Next Steps

READ more about PL/SQL associative arrays
Oracle Database PL/SQL User's Guide and Reference
Oracle Data Provider for .NET Developer's Guide

 VISIT the .NET Developer Center

sample code for this column

Photography by Teo Duldulao, Unsplash