Consider this an add-on to my long PL/SQL series on DevShed.  If you are new to PL/SQL, I strongly suggest you to go through my huge “Database interaction with PL/SQL” series on this website.

All of the examples in this article have been tested only with Oracle 10g version 2.  I didn’t really test the examples in any of the previous Oracle versions.  I am confident that all of the examples should give no problems when worked with Oracle version 7.3 or above.  You can drop (or post) me a line if you run into trouble with any of them.  The examples can be directly copied, pasted and tested in your favorite PL/SQL environment.  They should work without any change.

Associative Arrays in Oracle PL/SQL: the beginning

The “Associative Arrays” are also known as “Index-By” tables in PL/SQL.  We all know that a variable can hold only one value (as the variable occupies one memory location based on the data type chosen).  An “Associative Array” can also be considered a single variable, but with more than one memory location. 

An “Associative Array” can hold a huge amount of information in several memory locations, identified by some “index.”  This “index” could simply be an integer or string, or something else. 

In general, an “Associative Array” stores pairs of data (either sequentially or non- sequentially).  Each pair of data would generally contain a “key” and a “value.”  If stored sequentially, the “key” would be nothing but a consecutive number holding the “value.”  While storing information into an “Associative Array,” the user needs to specify both “key” and “value.” 

This concept looks very similar to the concept of arrays in C/C++.  In C/C++ an array is a continuous memory of several locations starting with zero as index.  But, here the “index” could be anything (there is no strict rule for working with the “index”).

A typical “Associative Array” in PL/SQL could be declared like this:

TYPE year_type IS TABLE OF number INDEX BY BINARY_INTEGER;

The above single statement contains a lot of meaning.  First of all, we are simply defining our own data type, “year_type.” Make sure that “year_type” is not a  variable.  It is a user defined data type.  According to the above statement, “year_type” is a data type which can hold a set (or table) of values (typically of type “number”), organized with a BINARY_INTEGER index.  The BINARY_INTEGER in this scenario simply acts as a location number or position of the memory location within the table (or simply called an index).

The next section gives you a better understanding with a simple example.