Oracle PL/SQL collections

Introduction

PL/SQL applications typically consist of SQL statements intermixed with procedural logic to process data retrieved from the database. If compiled as a stored procedure, your PL/SQL code will reside on the server, an ideal place for programs that require intensive database interaction. Having said that, anytime a software application links up with a database, there is a performance price to be paid. Not only that, programs that continually switch off between code and SQL can become quite complex. PL/SQL collections can address some of these concerns.

Why Collections?

Just about all modern programming languages provide support for collections. A collection can be loosely defined as a group of ordered elements, all of the same type that allows programmatic access to its elements through an index. Commonly used collection types used in the programming world include arrays, maps, and lists.

Storing elements in a collection can provide a number of advantages. For starters, collections can help to simplify code. If you need to process a number of items of a similar type, storing these items in a collection will allow you to loop through each element with ease, referencing each one by an index. In addition, most languages define collection types that contain built-in methods to operate on the collection. Probably the biggest advantage a collection can provide is improved application performance. Developers utilize collections to 'cache' static data that needs to be regularly accessed. This results in reduced calls to a database.

Oracle Collections

Oracle provides three types of PL/SQL collections, viz., Associative arrays, nested tables and Varrays. Each type of collection is described in the below section.

Associative Array

Associative arrays are a set of key value pairs where each key is unique and is used to locate the corresponding value in the array. The key can be integer or a string.

Associative arrays represent data set of arbitrary size with fast lookup of individual element without knowing its position within the array and without having to loop through all array elements. As associative arrays are intended for temporary data storage rather than storing persistent data, they cannot use with SQL statements such as INSERT and SELECT INTO. They can make persistent for a life of database session by declaring the type in package and assigning values in package body.

-- Declaration and initialization of Associative Array  
DECLARE
  TYPE book_title_tab IS TABLE OF book.title%TYPE INDEX BY BINARY_INTEGER;
  book_titles   book_title_tab;
BEGIN
  book_titles(1) := 'Computer fundamentals';
  book_titles(2) := 'Database programming';
  …
END;

PL/SQL Associative Arrays allow .NET code to pass arrays as parameters to PL/SQL code (stored procedure or anonymous PL/SQL blocks).

The database stored procedure ‘INSERT_PERMISSIONS’ of package ‘PERMISSIONS’ is called from ASP.NET application.

-- Create table 
CREATE TABLE TABPERMISSIONS (ID NUMBER(8),NAME VARCHAR2(40));

-- Create package specification 
CREATE OR REPLACE PACKAGE SCOTT.PERMISSIONS
AS
   TYPE PER_TY
   IS
      TABLE OF TABPERMISSIONS.ID%TYPE
         INDEX BY BINARY_INTEGER;

   PROCEDURE INSERT_PERMISSIONS (permissions PER_TY);
END PERMISSIONS;
/

-- Create package body 
CREATE OR REPLACE PACKAGE BODY SCOTT.PERMISSIONS
AS
   PROCEDURE INSERT_PERMISSIONS (permissions PER_TY)
   AS
      P_ID   NUMBER (8) := 0;
   BEGIN
      FOR i IN PERMISSIONS.FIRST .. PERMISSIONS.LAST
      LOOP
         INSERT INTO TABPERMISSIONS (id)
           VALUES   (permissions (i));
      END LOOP;
   END INSERT_PERMISSIONS;
END permissions;
/

The following code snippet explains how to pass an array from ASP.NET application to Oracle stored procedure.

protected void btnUpdate_Click(object sender, EventArgs e)
    {
        string constr = "User Id=abc; Password=testpwd; 
	Data Source=ORCL; enlist=false; pooling=false";
        OracleConnection con = new OracleConnection(constr);
        con.Open();
        OracleCommand cmd = new OracleCommand();
        cmd.Connection = con;
        cmd.CommandText = "PERMISSIONS.INSERT_PERMISSIONS";
        cmd.CommandType = CommandType.StoredProcedure;

        OracleParameter p_in_values = new OracleParameter();
        p_in_values.OracleDbType = OracleDbType.Int32;
        p_in_values.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
        p_in_values.Value = new decimal[4] { 1, 2, 3, 4 };
        p_in_values.Size = 4;
        p_in_values.Direction = ParameterDirection.Input;

        cmd.Parameters.Add(p_in_values);

        try
        {
            int a = 0;
            a= cmd.ExecuteNonQuery();
        }
        catch (Exception EX)
        {
            Console.WriteLine(EX.Message);
        }
    }

Summary

1. Associative arrays are appropriate for relatively small lookup tables where the collection can be constructed in memory each time a procedure is called or a package is initialized.
2. They are good for collecting information whose volume is unknown beforehand, because there is no fixed limit on their size.
3. Their index values are more flexible, because associative array subscripts can be negative, can be nonsequential, and can use string values instead of numbers when appropriate.

Varrays

The Varray is short for Variable Array. A Varray stores elements of the same type in the order in which they are added. The number of elements in a Varray must be known at the time of its declaration. In other words, a Varray has fixed lower and upper bounds, making it most similar to collection types from other programming languages. Once it is created and populated, each element can be accessed by a numeric index.

The following statements declare, and then populate, a Varray that will contain 4 elements of the same type as the column name in table Department:

-- Declaration and initialization of VARRAY.
DECLARE
    TYPE deptnames IS VARRAY(4) OF Department.name%TYPE;
    dept_names deptnames;
BEGIN
    dept_names := deptnames ('COMPUTER','MECHANICAL', 'ELCTRICAL');
END;

-- Create Varray
CREATE TYPE depts IS VARRAY (4) OF VARCHAR2(50);
/
--Create table with varray column.
CREATE TABLE Department (
    DEPTNO     NUMBER,
    SECTION    VARCHAR2(30),
    Dept_names   depts);
/
--Insert a new collection into the column on our book_library table.
INSERT INTO Department (DEPTNO, SECTION, Dept_names)
  VALUES (DEPTNO_SEQ.NEXTVAL,'Engineering', 
          depts ('Electronics','Computer', 'Instrumentation', 'Information technology'));
/

-- Retrieve varray from the database.
SELECT section, Dept_names from Department; 
NAME                  DEPT_NAMES
--------------------  ---------------------------------------------
Engineering      DEPTS('Electronics','Computer','Instrumentation',  
                       'Information technology')                                     
-- Note: Insertion order of elements of varray retained after retrieval.

Summary

1. Varrays can be added into database tables.
2. The Varray is ideal for storing fixed values that will be processed collectively.
3. It is not possible to perform inserts, updates, and deletes on the individual elements in a Varray.
4. When a table contains a Varray type, its data is included in-line, with the rest of the table's data.
5. Varrays are a good choice when the number of elements is known in advance, and when the elements are usually all accessed in sequence.
6. When stored in the database, varrays retain their ordering and subscripts.

Nested Table

Nested Tables, like the Varray, can be stored in a relational table as well as function as a PL/SQL program variable. Unlike Varray, nested tables require no size specification. In other words, they are unbound.

CREATE TYPE dept_tab IS TABLE OF VARCHAR2(50);
/
CREATE TABLE Department (
    DEPTNO     NUMBER,
    SECTION    VARCHAR2(30),
    dept_name_tab dept_tab)
    NESTED TABLE dept_name_tab STORE AS names_table;
/
--Insert a record into Department, with a Nested Table of dept names.
INSERT INTO Department (DEPTNO, SECTION, dept_name_tab)
  VALUES (DEPTNO_SEQ.NEXTVAL,'Engineering', 
          dept_tab('Electronics','Computer', _
		'Instrumentation', 'Information technology'));
/
--Declare a nested table type
DECLARE 
  DeptName_tab dept_tab;
BEGIN
  DeptName_tab := 
    dept_tab ('Mechanical',''Electronics', 'Computer', 'Instrumentation',
               ''Information technology','Production',’ELECTRICAL’);
 
--Update the existing record with a new dept names Nested Table.
  UPDATE Department 
    SET dept_name_tab = DeptName_tab;
END;
/

How Nested Tables are Stored in Database?

With Nested Tables, a separate database table will store the data. This table is specified following the 'STORE AS' clause. If a database table has more than one Nested Table type, the same storage table will store data for all the Nested Tables on that parent table. These storage tables contain a column called NESTED_TABLE_ID that allows the parent table to reference a row's nested table data.

How to Operate on Individual Elements of Collection?

To operate on collection elements, use the TABLE command. The TABLE command operator informs Oracle that you want your operations to be directed at the collection, instead of its parent table.

--1.Select all depts from section 'Engineering' that are like %ELCTRO%'.
SELECT column_value FROM TABLE(SELECT dept_name_tab
FROM Department WHERE SECTION = ''Engineering')
   WHERE column_value LIKE '%ELECTRO%';
-- Output of the above query
COLUMN_VALUE
------------------------------
ELECTRONICS
ELCTRICAL 
--2.Update DPET NAME ‘Production’ to a new value ‘Production sandwitch’.  
--This is possible Only with a nested table, Not Possible in Varray!!
UPDATE TABLE(SELECT dept_name_tab 
             FROM Department WHERE SECTION = 'ENGINEERING')
  SET column_value   = 'Production'
  WHERE column_value = 'Production Sandwitch';
 
--3.Select all department entries for ENGINEERING SECTION.
SELECT column_value FROM TABLE(SELECT dept_name_tab
                               FROM Department WHERE SECTION = 'Engineering');
-- Output of the above query
COLUMN_VALUE
------------------------------
MECHANICAL
ELECTRONICS
COMPUTER
PRODUCTION SANDWITCH

Summary

1. Nested tables are appropriate for important data relationships that must be stored persistently.
2. Nested tables can be sparse: you can delete arbitrary elements, rather than just removing an item from the end.
3. Nested table data is stored out-of-line in a store table, a system-generated database table associated with the nested table.
4. The order and subscripts of elements of nested table are not preserved when it is stored in database.
5. Nested Tables have an advantage over varrays in that they allow for inserts, updates, and deletes on individual elements.

When to Use What?

Varray

1. Use to preserve ordered list
2. Use when working with a fixed set, with a known number of entries
3. Use when you need to store in the database and operate on the Collection as a whole

Nested Table

1. Use when working with an unbounded list that needs to increase dynamically
2. Use when you need to store in the database and operate on elements individually

Associative Array

1. Use when there is no need to store the Collection in the database. Its speed and indexing flexibility make it ideal for internal application use.

Conclusion

Oracle PL/SQL is not a difficult programming language to learn. However, like all good programming languages, there are many things we can do to maximize efficiency and minimize complexity. Considering PL/SQL's power to interact with the database, it can be tempting to simply fall into the habit of making excessive database calls to do our work. Collections can help you build simpler, faster Oracle database applications, the goal of every good PL/SQL developer.