PLSQL collections come in once you've been using PLSQL for a while and  realize that there is a need for more complex or sophisticated data types such as the array, table or set types that exist in other languages. Oracle PLSQL has 3 types of collections:

1. associative arrays (also known as index-by tables)
2. VARRAYs (varying size arrays)
3. nested tables

Let's explore nested tables in detail. PLSQL Nested Table Collections Nested tables are one- dimensional arrays with no upper boundary. This makes them similar to an associative array indexed by integer, although there are important differences as we'll see later. Nested tables are equivalent

to sets or bags in other languages and are most  suitable when:-

• the number of elements or cells is not fixed
• random access to the cells  in the table is required
• you don't need to be able to update all cells simultaneously

Although similar to associative arrays in that they have no upper limit, unlike associative arrays, nested tables are:-

• initially densely populated (although they may become sparsely populated) 
• are NULL when created (associative arrays are empty but not null)
• are initialized by use of a constructor method

Initialising a Nested Table PLSQL Collection

Like varray collections, an uninitialised nested table is null (undefined) rather than empty and has to be initialized using a constructor and also like varrays the index for nested tables always start from 1, so for a nested table with 10 elements the index would go from 1 to 10 although some cells may be empty.

Let's look at an example.

As with the associative array and varray collection types, first we declare the type, then we declare a variable of that type.

DECLARE

TYPE colour_tab IS TABLE OF VARCHAR2(20);
--the type declaration defines our plsql collection

colours colour_tab; -- a plsql collection variable
new_colours colour_tab('GREEN','BLACK','YELLOW');
                               -- variable declared and                                        -- initialized at same time
BEGIN

    colours  := colour_tab('RED','WHITE','BLUE');
                               --nested table fully initialized

END;

Accessing elements in a Nested Table PLSQL Collection

Reading from and writing to a nested table is done in the same way as with varrays. First of all we have to initialise the array (as shown in the preceding example), then we can insert data into and retrieve data from the varray.

Oracle Tips & Tricks to SKYROCKET Your Career!

DECLARE
    TYPE colour_tab IS TABLE OF VARCHAR2(20);
    colours colour_tab; -- a plsql collection variable
BEGIN
    colours  := colour_tab('RED','WHITE','BLUE');
    colours(2) := 'GREEN'; -- overwrite 2nd element
    DBMS_OUTPUT.PUT_LINE('second colour = '||colours (2));
END;
/
second colour = GREEN

As with varrays, if you attempt to read from or write to an un-initialized nested table, Oracle raises an exception as in the following example.

DECLARE
    TYPE colour_tab IS TABLE OF VARCHAR2(20);
   colours colour_tab; -- a plsql collection variable
BEGIN
   DBMS_OUTPUT.PUT_LINE('third colour = '||colours (3));
END;
/
declare
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 5

Oracle also raises an exception

• when we attempt to access a cell that has been deleted (ORA-1403: no data found) - example 1
• when we read or write to a cell that is outside of the range i.e doesn't exist (ORA-6533: Subscript beyond count) - example 4
• when we read or write to a cell using a negative subscript (ORA-6532: Subscript outside of limit) - example 5

Example 1 - read from empty cell

DECLARE
    TYPE colour_tab IS TABLE OF VARCHAR2(20);
   colour colour_tab := colour_tab('green','purple');
BEGIN
    colour.DELETE(2); -- empty cell 2, can't now read from it 
    DBMS_OUTPUT.PUT_LINE('2ND colour = '||colour (2));
           -- invalid attempt to read cell 2 which is empty
END;
/
declare
*
ERROR at line 1:
ORA-1403: no data found
ORA-06512: at line 6

Deleting a cell of a nested table doesn't remove it entirely, we can resurrect it just by writing to that cell again as in the following example.

Example 2 - write to empty cell

DECLARE
    TYPE colour_tab IS TABLE OF VARCHAR2(20);
   colour colour_tab := colour_tab('GREEN');
BEGIN
    colour.DELETE(1); -- empty cell 1, can't now read from it       colour.(1) := 'BROWN'; -- but we can write to it!
    DBMS_OUTPUT.PUT_LINE('1st colour = '||colour (1));
END;
/
second colour = BROWN

However, we can remove empty cells entirely from a nested table using TRIM.

Example 3 - Remove empty cells

DECLARE
    TYPE colour_tab IS TABLE OF VARCHAR2(20);
    colour colour_tab := colour_tab('GREEN')
BEGIN
    colour.DELETE(1);      -- empty cell 
    colour.TRIM;           -- remove empty cell
    colour.(1) := 'BROWN'; -- can't write to it!
END;
/
declare
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 7

Example 4 - attempt to access non-existant cell

DECLARE
    TYPE colour_tab IS TABLE OF VARCHAR2(20);
    colour colour_tab := colour_tab('green','purple')
BEGIN
   DBMS_OUTPUT.PUT_LINE('2ND colour = '||colour (4));
       -- attempt to read cell 4 which hasn't been created
END;
/
declare
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 5

Example 5 - attempt to access cell using negative subscript

DECLARE
    TYPE colour_tab IS TABLE OF VARCHAR2(20);
    colour colour_tab;
BEGIN
    DBMS_OUTPUT.PUT_LINE('colour(-2) = '||colour (-2));
           -- attempt to read cell -2, 1st element is 1
END;
/
declare
*
ERROR at line 1:
ORA-06532: Subscript outside of limit
ORA-06512: at line 5

Adding Elements to a Nested Table PLSQL Collection

We've seen how to initialise a nested table using constants but what if we don't know how many elements there will be initially or just want to add more? Simple, we use the EXTEND method to increase the size as in the following example.

DECLARE
    TYPE colour_tab IS TABLE OF VARCHAR2(20);
    spanish_flag colour_tab; -- a plsql collection variable

BEGIN
    spanish_flag colour_tab := colour_tab('RED');
    spanish_flag.EXTEND(2); -- add another 2 cells
    spanish_flag(3):='yellow';
    DBMS_OUTPUT.PUT_LINE('spanish_flag(3) = '||spanish_flag(3);
END;
/
spanish_flag(3) = yellow

If we don't specify how many cells to add with EXTEND, then just 1 cell is added.

Other Nested Table PLSQL Collection Methods

We've already seen the TRIM, DELETE and EXTEND methods. The other ones applicable to nested table are:

• COUNT: This function returns the number of elements (cells) in the collection, 0 if the collection is empty
• LAST: For an initialized empty nested table, LAST is NULL. For an initialized non-empty VARRAY it returns COUNT+1. For an unitialised nested table, attempting to read LAST causes an exception to be raised (ORA_06531: reference to unitiialized collection
• FIRST: For an initialized empty nested table, FIRST is NULL. For an initialized non-empty nested table, FIRST always returns 1
• EXISTS: Returns true or false depending on whether or not the specified element exists (and won't cause an exception if the nested table has not been initialized)
• LIMIT: Returns the maximum size of the nested table
• PRIOR and NEXT: These functions return the index of the previous and next cells in the nested table. At the last cell in the table, NEXT returns NULL and at the first cell, PRIOR returns NULL

---

Looking for Oracle PL/SQL training courses? Learn PL/SQL from the experts and sky-rocket your career with Oracle PL/SQL training in the UK from Smartsoft.

Return from plsql collections to the introductory PLSQL tutorial for an overview of Oracle PL/SQL
Return to home page