2 TYPE numbers_nt IS TABLE OF
NUMBER;
3 l_numbers numbers_nt;
4 BEGIN
5 l_numbers.EXTEND;
6 l_numbers( 1) := 1;
7 END;
8 /
DECLARE
*
ERROR at line 1:
ORA-06531: Reference to uninitialized
collection
ORA-06512: at line 5
You do not need to initialize an associative
array before assigning values to it.
POPULATING COLLEC TIONS
You can assign values to elements in a collection in a variety of ways:
•;Call a constructor function (for nested
tables and varrays).
•;Use the assignment operator, for single elements as well as entire collections.
•;Pass the collection to a subprogram as an
OU T or IN OUT parameter, and then assign
the value inside the subprogram.
•;Use a BULK COLLECT query.
The previous section included an example
Answer to the Challenge
The PL/SQL Challenge question in last issue’s
“Working with Records in PL/SQL” article
tested your knowledge of how to declare a
record variable based on a table or a cursor.
The question asked which of the following
could be used in the question’s code block so
that a value (“Keyboard”) from the question’s
table would be displayed. All the choices are
listed below; only (c) and (d) are correct.
a.
l_part plch_parts%TYPE;
b.
l_part plch_parts;
c.
l_part plch_parts%ROWTYPE;
d.
CURSOR parts_cur
IS
SELECT FROM plch_parts;
l_part parts_cur%ROWTYPE;
that used a constructor function. Following
are examples of the other approaches:
1. Assign a number to a single index value.
Note that with an associative array, it is
not necessary to use EX TEND or start
with index value 1.
DECLARE
TYPE numbers_aat IS TABLE OF
NUMBER
INDEX BY PLS_INTEGER;
l_numbers numbers_aat;
BEGIN
l_numbers (100) := 12345;
END;
2. Assign one collection to another. As long
as both collections are declared with the
same type, you can perform collection-level assignments.
DECLARE
TYPE numbers_aat IS TABLE OF
NUMBER
INDEX BY PLS_INTEGER;
l_numbers1 numbers_aat;
l_numbers2 numbers_aat;
BEGIN
l_numbers1 (100) := 12345;
l_numbers2 := l_numbers1;
END;
3. Pass a collection as an IN OU T argument,
and remove all the elements from that
collection:
DECLARE
TYPE numbers_aat IS TABLE OF
NUMBER
INDEX BY PLS_INTEGER;
l_numbers numbers_aat;
PROCEDURE empty_collection (
numbers_io IN OUT numbers_aat)
IS
BEGIN
numbers_io.delete;
END;
BEGIN
l_numbers (100) := 123;
empty_collection (l_numbers);
END;
4. Fill a collection directly from a query with
BULK COLLECT (covered in more detail in
the next article in this series):
DECLARE
TYPE numbers_aat IS TABLE OF
NUMBER
INDEX BY PLS_INTEGER;
l_numbers numbers_aat;
BEGIN
SELECT employee_id
BULK COLLECT INTO l_numbers
FROM employees
ORDER BY last_name;
END;
I TERATING THROUGH COLLEC TIONS
A very common collection operation is to
iterate through all of a collection’s elements. Reasons to perform a “full collection
scan” include displaying information in the
collection, executing a data manipulation
language (DML) statement with data in the
element, and searching for specific data.
The kind of code you write to iterate
through a collection is determined by the
type of collection with which you are working
and how it was populated. Generally, you will
choose between a numeric FOR loop and a
WHILE loop.
Use a numeric FOR loop when
•;Your collection is densely filled (every index
value between the lowest and the highest
is defined)
•;You want to scan the entire collection, not
terminating your scan if some condition
is met
Conversely, use a WHILE loop when
•;Your collection may be sparse
•;You might terminate the loop before you
have iterated through all the elements in
the collection
You should use a numeric FOR loop with
dense collections to avoid a NO_DATA_
FOUND exception. Oracle Database will
also raise this exception, however, if you try
to “read” an element in a collection at an
undefined index value.
The following block, for example, raises a
NO_DATA_FOUND exception:
