11.6.1.4 Varray integration

Does this database-to-PL/SQL integration work forVARRAYs too? You bet, although there are a couple of differences.

First of all, realize that when you store and retrieve the contents of a nested table in the database, Oracle makes no promises about preserving the order of the elements. This makes sense, because the server is just putting the nested data into a store table behind the scenes, and we all know that relational databases don't give two hoots about row order. By contrast, storing and retrieving the contents of a VARRAY do preserve the order of the elements.

Preserving the order of VARRAY elements is a fairly useful capability. It makes it possible to embed meaning in the order of the data, which is something you cannot do in a pure relational database. For example, if you want to store someone's favorite colors in rank order, you can do it with a single VARRAY column. Every time you retrieve the column collection, its elements will be in the same order as when you last stored it. In contrast, abiding by a pure relational model, you would need two columns, one for an integer corresponding to the rank, and one for the color.

This order-preservation of VARRAYs suggests some possibilities for interesting utility functions. For example, you could fairly easily code a tool that would allow the insertion of a new "favorite" at the low end of the list by "shifting up" all the other elements.

A second difference between integration of nested tables and integration of VARRAYs with the database is that some SELECT statements you could use to fetch the contents of a nested table will have to be modified if you want to fetch a VARRAY. (See Section 11.7 later for some examples.)

11.6.2 Assigning Values to Elements

You can assign values to rows in a collection with the standard assignment operator of PL/SQL, as shown here:

countdown_test_list (43) := 'Internal pressure';

company_names_table (last_name_row+10) := 'Johnstone Clingers';

You can also perform an "aggregate assignment" of the contents of an entire collection to another collection of similar type. Here is an example of such a transfer:

DECLARE

TYPE name_table IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;

old_names name_table;

new_names name_table;

BEGIN

/* Assign values to old_names table */

old_names(1) := 'Smith';

old_names(2) := 'Harrison';

/* Assign values to new_names table */

new_names(111) := 'Hanrahan';

new_names(342) := 'Blimey';

/* Transfer values from new to old */

old_names := new_names;

/* This assignment will raise NO_DATA_FOUND */

DBMS_OUTPUT.PUT_LINE (old_names (1));

END;

A collection-level assignment completely replaces the previously defined rows in the collection. In the preceding example, rows 1 and 2 in old_names are defined before the last, aggregate assignment.

After the assignment, only rows 111 and 342 in the old_names collection have values. As a result (and as explained in the following discussion), if I try to then reference the contents of row 1 in old_names, PL/SQL will raise a NO_DATA_FOUND exception.

If you are working with associative arrays, you can assign a value (of the appropriate type) to any valid row number in the collection (a row number must be between -2³¹ + 1 and 2³¹ - 1). The simple act of assigning the value creates the row and deposits the value in that row.

In contrast to associative arrays, you can't assign values to arbitrarily numbered subscripts of nested tables and VARRAYs; instead, the indexes (at least initially) are monotonically increasing integers, assigned by the PL/SQL engine. That is, if you initialize n elements, they will have subscripts 1 through n—and those are the only rows to which you can assign a value.

Before you try to assign a value to a row in a nested table or VARRAY, you must make sure that it has been extended to include or initialize that row. Use the EXTEND operator, discussed later in this chapter, to make new rows available in nested tables and VARRAYs.