Lesson 7

Now that we have defined varrays and nested tables and you have learned the different ways to query them, you should be able to:

1. Explain the concept of flattening a nested table
2. Describe the SQL enhancements for nested tables such as THE and TABLE
3. Query nested tables and varrays
4. Create a PL/SQL block to query nested tables and varrays

Varrays are single-dimensional structures of an Oracle 10g data type or a userdefined record/object type. Varrays may be used in table, record, and object definitions and may be accessed in SQL and PL/SQL. They are arrays in the traditional sense of programming languages such as Java, C, C++, and C#. They use sequential index values to reference elements in the structure.

The syntax to define a varray in a PL/SQL program unit is

TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit)
OF element_type [ NOT NULL ];

The type name is often a string followed by an underscore and the word varray. Many programmers and configuration management people find it a useful pattern to improve code readability. Either VARRAY or VARYING ARRAY syntax may be used, but the former is much more common. The size limit is a required value. It is a positive integer giving the maximum number of elements in the varray. Element type may be any Oracle 11g data type or a user-defined data type. Allowing null values in varrays is the default. If null values should be disallowed, the fact must be specified when they are defined. The following example program demonstrates defining, declaring, and initializing a varray of integers in a PL/SQL program unit. An integer is a subtype of the Oracle 10g number data type. Subscript index values begin at 1, not zero. This is consistent with the longstanding behavior of index-by tables in Oracle 8 to Oracle 9i and PL/SQL tables in Oracle 7. Most programming languages, including Java, C, C++, and C#, use subscript index values that begin with zero.

DECLARE
-- Define a varray of integer with 3 rows.
TYPE integer_varray IS VARRAY(3) OF INTEGER;
-- Declare and initialize a varray that allows nulls.
varray_integer INTEGER_VARRAY :=
integer_varray(NULL,NULL,NULL);

BEGIN
-- Print title.
dbms_output.put_line('Varray initialized as nulls.');
dbms_output.put_line('--------');
-- Loop through the three records.
FOR i IN 1..3 LOOP
-- Print the contents.
dbms_output.put ('Integer Varray ['||i||'] ');
dbms_output.put_line('['||varray_integer(i)||']');
END LOOP;

-- Assign values to subscripted members of the varray.
varray_integer(1) := 11;
varray_integer(2) := 12;
varray_integer(3) := 13;
-- Print title.
dbms_output.put (CHR(10)); - Visual line break.
dbms_output.put_line('Varray initialized as values.');
dbms_output.put_line('--------');
-- Loop through the three records.
FOR i IN 1..3 LOOP
-- Print the contents.
dbms_output.put_line('Integer Varray ['||i||'] '
|| '['||varray_integer(i)||']');
END LOOP;
END;
/

In this module, you were introduced to the following glossary term:

1. Flattening: A relational table which is associated with a nested table consists of two tables, the main relational table and the nested table. To query, update, insert, or to delete records from the nested table, it must be queried as if it were a single table.
   This is achieved by the usage of "THE" or "TABLE" functions.

In the next module, you will learn the different ways to modify data within object tables. You will also learn how to insert, update, and delete records from object tables.