Oracle Collection of record Array Example

• PL/SQL associative arrays (previously known as index-by or PLSQLtables) - covered in this tutorial.

PLSQL Associative Arrays

Oracle PL/SQL associative arrays were known as index-by tables or PLSQL tables in previous versions of Oracle and this gives us a clue as to their purpose and functionality - they have an index. In earlier versions of Oracle PLSQL tables could only be indexed byBINARY INTEGERs, in Oracle 9i Release 2 and above they can be indexed (associated) with BINARY INTEGER or VARCHAR2 constants/variables.

PLSQL associative arrays are much like PLSQL nested tables:

• they are one-dimensional arrays;
• they are unbounded;
• they are homogeneous (every element must be of the same type).

The other difference between associative arrays and PLSQL nested tables is that nested tables are initially dense whereas associative arrays are sparse.

Let's look at a couple of examples in our PLSQL tutorial.

First we declare the table type:

TYPE phone_no_tab IS TABLE OF VARCHAR2 (20)INDEX BY BINARY_INTEGER;

Next we declare a variable of that type:

phone_nos phone_no_tab;

This creates an array (table) that is available within our PL/SQL session. Inserting and retrieving data is done in the same way as with nested tables but there is no need to initialise the array or extend it. Instead we just access the cells we want, whether we want to insert data or retrieve data from the array, as in the following examples:

SQL> declare
2     TYPE phone_no_tab IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;3     phone_nos phone_no_tab;
4 begin
5     phone_nos(1) := '+44 (0) 117 942 2508';
6     dbms_output.put_line('phone_no(1) is '||phone_nos(1));
7 end;
8 /phone_no(1) is +44 (0) 117 942 2508

As you can see from the above example the elements (cells) of the array are created automatically by Oracle. Line 3 acts as as the declaration and constructor of the array. However if we access a cell that doesn't have any data in it, we will generate a NO_DATA_FOUND exception as in the following example:

SQL> DECLARE2     TYPE phone_no_tab IS IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;3     phone_nos phone_no_tab;4 BEGIN
5     phone_nos (21) := '0117 942 2508';
6     DBMS_OUTPUT.PUT_LINE('phone_no(1) is '||phone_nos(1));
7 END;
8 /
declare
*

ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 6

That's demonstrated the use of associative arrays when indexed by BINARY INTEGERS, but our example is not very practical. It is much more useful to be able to access a table of phone numbers by type (home,office,mobile) rather than by position.

Let's continue our PLSQL tutorial with an example of associating elements of the array with character strings (VARCHAR2 datatype).

SQL> declare
2     TYPE phone_no_tab IS TABLE OF VARCHAR2(20) INDEX BY VARCHAR2(30);3     phone_nos phone_no_tab;
4 begin
5     phone_nos('office') := '+44 (0) 117 942 2508';
6     DBMS_output.put_line('phone_no(office) is '||phone_nos('office'));
7 end;
8 /phone_no(office) is +44 (0) 117 942 2508

We'll finish off this PLSQL tutorial by having a look at other methods we can use on PLSQL associative arrays.

COUNT: This function returns the number of elements (cells) in the collection

DELETE: This procedure with no parameters deletes all the elements in the collection, as with nested tables, though, we can specify a start and end point to say which element(s) is (are) to be deleted. After deleting an element or the whole collection, any subsequent attempt to read that element in the collection generates "ORA-01403: no data found". However you can write to any element without any errors as this re-creates the element

EXISTS(n) : used to determine if the specified element has been created and not deleted, returns TRUE if the element exists, FALSE if not. The index variable can be either a number of type BINARY_INTEGER or a character string of type VARCHAR2

FIRST : returns the subscript of the first element in the PLSQL associative array
LAST : returns the subscript of the last element in the PL/SQL associative array

PRIOR(n): returns the subscript of the previous element in the PL/SQL associative array or NULL if if no more elements exist
NEXT(n) : returns the subscript of the next element in the PLSQL associative array or NULL if no more elements exist.

TRIM : Not appropriate for associative arrays - generates a compilation error
EXTEND : Not appropriate for associative arrays - generates a compilation error