Oracle 9i Release 2 Developments for PL/SQL Collections by Steven Feuerstein, coauthor of Oracle PL/SQL Programming, 3rd Edition and Bryn Llewellyn 04/01/2003

Editor's note: In the previous article in their continuing series on new Oracle 9i features, Steven Feuerstein and Bryn Llewellyn looked at Utl_Http and showed how you can use it in an Oracle 9i database to implement a requestor in a B2B implementation. In this article, the pair explores PL/SQL collections in Oracle 9i Release 2, with particular emphasis on associative arrays. Find out why extending the flexibility of the collection syntax, storage, and access, makes it possible to write much simpler, more efficient code than was possible in earlier versions.

Introduction

For the first several years after Steven published the first edition of Oracle PL/SQL Programming in 1995, he evangelized the use of packages as a fundamental building block of PL/SQL-based applications. This seemed to be a critical message for a number of years, as relatively few developers knew about and used packages. Lately, it appears that the "package story" has caught on; most developers do deploy the majority of their functionality from within packages. In the course of querying students and presentation attendees about their programming habits, however, Steven has discovered another very helpful aspect of PL/SQL that's being drastically under-utilized: the collection.

A PL/SQL collection is, in its essence, very similar to a single-dimensioned array. A collection allows you to maintain lists of information, and it can be used to improve query performance and also simplify the code you write to manage data within a PL/SQL program. These collection data structures come in three flavors: nested table, varying array (a.k.a. VARRAY), and associative array. We can't, within the scope of this article, offer a complete introduction to collections. You can obtain such coverage from any number of PL/SQL texts and the Oracle documentation. Our intention in this article is, instead, to let you know about some very interesting developments in Oracle 9i Release 2 regarding PL/SQL collections.

Related Reading Oracle PL/SQL Programming By Steven Feuerstein Table of Contents Index Sample Chapter Read Online--Safari Search this book on Safari:   Only This Book All of Safari Code Fragments only

We will, in fact, focus on one particular type of collection: the associative array. For those of you who have worked with collections over the years, this will be an unfamiliar term. Some of you will remember back in Oracle 7 when collections were first introduced; at that point, they were called "PL/SQL tables," since they were similar to very simple relational tables (consisting of a single column) but were declared and manipulated only within PL/SQL programs. Then in Oracle 8, Oracle introduced two other kinds of single- dimensioned lists (VARRAYS and nested tables). In the process, they changed the name of the original collection type from "PL/SQL table" to "index-by table" (reflecting its mandatory INDEX BY BINARY_INTEGER clause).

VARRAYS and nested tables can be used both in schema-level declarations (especially, for example, for the type of a column of a relational table) and in PL/SQL declarations. Index-by tables can be used only in PL/SQL declarations. Now, in Oracle 9i Release 2, Oracle has once again renamed this collection type, this time from "index-by table" to "associative array." Why another change? The term associative array is the name commonly used in other programming languages (including Perl, C++, JavaScript, and Cymbal, to name a few) to refer to a data structure that stores pairs of keys and values. By making this change, PL/SQL becomes more consistent with the nomenclature of much of the programming world, and therefore makes PL/SQL a bit more accessible to developers who are new to PL/SQL, but have experience in other languages.

Associative arrays can still be used only in PL/SQL declarations. The wonderful development regarding collections in Oracle 9i Release 2 is, however, not in the name change, but in some significant new functionality. Let's take a look.

Declaring Associative Arrays

In the bad old days, there was just one way to declare an associative array:

DECLARE
   TYPE names_list_t IS
      TABLE OF employee.last_name%TYPE
      INDEX BY BINARY_INTEGER;

The "INDEX BY BINARY_INTEGER" clause was fixed and unchangeable. This meant that the only index allowed on an associative array was the row number, and the row number had to have been declared as BINARY_INTEGER.

There were several drawbacks to this scheme. First, it required reliance on an outmoded datatype, since BINARY_INTEGER has since been superseded by PLS_INTEGER as a more efficient integer datatype. Second, it meant that if the list being manipulated had a non-integer key, the developer had to write some very complex and/or compute-intensive logic (namely, to perform full collection scans or create alternative indexes via hashing) to take advantage of collections.

These restrictions have now been lifted. You can now declare associative arrays to be indexed by BINARY_INTEGER, PLS_INTEGER, VARCHAR2 and even anchored declarations of those types using %TYPE. All of the following statements are valid declarations of associative array types with integer indexes:

DECLARE  
   TYPE array_t1 IS 
      TABLE OF NUMBER 
      INDEX BY BINARY_INTEGER;
        
   TYPE array_t2 IS 
      TABLE OF NUMBER 
      INDEX BY PLS_INTEGER;

   TYPE array_t3 IS 
      TABLE OF NUMBER 
      INDEX BY POSITIVE;

   TYPE array_t4 IS 
      TABLE OF NUMBER 
      INDEX BY NATURAL;
BEGIN
   ...
END;

And, very interestingly, if you do declare a type based on a constrained BINARY_INTEGER subtype, such as POSITIVE, then, if you try to reference a negative row number, you'll get an error, as shown here:

SQL> DECLARE     
  2     TYPE pos_only_t IS 
  3        TABLE OF NUMBER 
  4     INDEX BY POSITIVE;
  5  
  6     pos_only pos_only_t;
  7  BEGIN
  8     pos_only (-9) := 1;
  9  END;
 10  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 8

Note: Even at Oracle 8i Version 8.1.7, the syntax to use subtypes of BINARY_INTEGER for an index-by table was allowed, but the implied constraint wasn't enforced.

You can even use a user-defined subtype, thus:

DECLARE
   SUBTYPE my_integer IS PLS_INTEGER NOT NULL;
   TYPE array_t4 IS
      TABLE OF NUMBER
   INDEX BY my_integer;
BEGIN
  ...
END; 

So PL/SQL is now much more flexible than it used to be when it comes to declaring integer-indexed collections. (Note: There are still restrictions. See the section titled "Invalid declarations" for reminders of which syntax is still not deemed acceptable.)

Much more exciting, however, is the fact that you can now declare associative arrays to have VARCHAR2 or string index values! Here are some examples of such declarations:

DECLARE     
  TYPE array_t1 IS 
     TABLE OF NUMBER 
     INDEX BY VARCHAR2(64);

  TYPE array_t3 IS 
     TABLE OF NUMBER 
     INDEX BY VARCHAR2(32767);
                          
  TYPE array_t4 IS 
     TABLE OF NUMBER 
     INDEX BY employee.last_name%TYPE;
BEGIN
  ...
END;
/

It's especially impressive that Oracle now lets us use %TYPE to declare an associative array with a VARCHAR2 index. This allows us to avoid hard-coding a VARCHAR2 maximum length in the TYPE statement.