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