ORACLE - Using PL/SQL Associative Arrays

By Mark A. Williams

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

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   begin     forall i in p_job_id.first..p_job_id.last     insert into jobs (job_id,                            job_title,                            min_salary,                            max_salary)               values (p_job_id(i),                          p_job_title(i),                          p_min_salary(i),                          p_max_salary(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)  JOB_TITLE     NOT NULL  VARCHAR2(35)  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);       con.Open();        // 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       cmd.Parameters.Add(p_job_id);       cmd.Parameters.Add(p_job_title);       cmd.Parameters.Add(p_min_salary);       cmd.Parameters.Add(p_max_salary);        // execute the insert       cmd.ExecuteNonQuery();        // 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));       }        Console.WriteLine();        // delete the new jobs       cmd.CommandText = "delete from jobs where job_id in ('IT_DBA', 'IT_MAN', 'IT_VP')";       cmd.ExecuteNonQuery();        Console.WriteLine("New jobs have been removed from the JOBS table.");        // clean up objects       p_max_salary.Dispose();       p_min_salary.Dispose();       p_job_title.Dispose();       p_job_id.Dispose();       cmd.Dispose();       con.Dispose();        // simple prompt to keep output window from closing when executing from IDE       Console.WriteLine("Press ENTER to continue...");       Console.ReadLine();     }   } } 

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 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

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.

---

Mark A. Williams is an Oracle ACE, an Oracle Certified Professional DBA, the author of Pro.NET Oracle Programming (Apress, 2004), and a contributor to the Oracle Data Provider for .NET forum on Oracle Technology Network.