19.4.1.1 Initializing with a constructor

Earlier, we saw declarations that looked like this:

my_favorite_colors Color_tab_t := Color_tab_t('PURPLE', 'GREEN');
my_favorite_numbers Number_t := Number_t(42, 65536);

Color_tab_t( ) is the constructor function supplied by Oracle when we created the Color_tab_t collection type. This function accepts an arbitrary number of arguments, as long as each argument is of the "proper" datatype -- which in this case is VARCHAR2(30), since our original type definition statement was the following:

CREATE TYPE Color_tab_t AS TABLE OF VARCHAR2(30);

At initialization, Oracle allocates to the variable an amount of memory necessary to hold the values you supply as arguments. Initialization both creates the "slots" for the elements and populates them.

So, if I want to "fix" the earlier invalid example, I can simply initialize the variable:

DECLARE
   cool_colors Color_tab_t := Color_tab_t('VIOLET');  -- initialize
BEGIN
   IF cool_colors(1) IS NULL THEN   -- This is OK now!

What do you suppose Oracle does with the following initialization?

   working_colors Color_tab_t := Color_tab_t();

This is a way of creating an "empty" collection. Empty is a sort of enigmatic state in which the collection is not atomically null but still has no data. Whenever you create such an empty collection, you'll need to "extend" the collection variable later when you want to put elements into it. (The EXTEND built-in is explored later in this chapter.)

19.4.1.2 Initializing implicitly during direct assignment

You can copy the entire contents of one collection to another as long as both are built from the exact same datatype. When you do so, initialization comes along "for free."

Here's an example illustrating implicit initialization that occurs when we assign wedding_colors to be the value of earth_colors.

DECLARE
   earth_colors Color_tab_t := Color_tab_t('BRICK', 'RUST', 'DIRT');
   wedding_colors Color_tab_t;
BEGIN
   wedding_colors := earth_colors;
   wedding_colors(3) := 'CANVAS';
END;

This code initializes wedding_colors and creates three elements that match those in earth_colors. These are independent variables rather than pointers to identical values; changing the third element of wedding_colors to 'CANVAS' does not have any effect on the third element of earth_colors.

Note that assignment is not possible when datatypes are merely "type-compatible." Even if you have created two different types with the exact same definition, the fact that they have different names makes them different types. A collection variable cannot be assigned to another variable of a different datatype:

DECLARE
   TYPE Local_colors_t IS VARRAY(16) OF VARCHAR2(30);
   TYPE Remote_colors_t IS VARRAY(16) OF VARCHAR2(30);
   l_color Local_colors_t := Local_colors_t('THALO BLUE');
   r_color Remote_colors_t;
BEGIN
   r_color := l_color;  -- invalid
END;

This code will fail with the compile-time error "PLS-00382: expression is of wrong type," because r_color and l_color are of different types.

19.4.1.3 Initializing implicitly via fetch

If you use a collection as a type in a database table, Oracle provides some very elegant ways of moving the collection between PL/SQL and the table. As with direct assignment, when you use FETCH or SELECT INTO to retrieve a collection and drop it into a collection variable, you get automatic initialization of the variable. Collections can turn out to be incredibly useful!

Although we mentioned this briefly in an earlier example, let's take a closer look at how you can read an entire collection in a single fetch. First, we want to create a table containing a collection and populate it with a couple of values:

CREATE TABLE color_models (
   model_type VARCHAR2(12),
   colors Color_tab_t)
NESTED TABLE colors STORE AS color_model_colors_tab;

insert into color_models
   values ('RGB', Color_tab_t('RED','GREEN','BLUE'));
insert into color_models
   values ('CYMK',Color_tab_t('CYAN','YELLOW','MAGENTA','BLACK'));

Now we can show off the neat integration features. With one trip to the database we can retrieve all of the values of the "colors" column for a given row, and deposit them into a local variable:

DECLARE
   l_colors Color_tab_t;
BEGIN
   /* Retrieve all the nested values in a single fetch.
   || This is the cool part.
   */
   SELECT colors INTO l_colors FROM color_models
      WHERE model_type = 'RGB';

   /* Loop through each value and print it. This is only to demonstrate
   || that we really have the data in the local variable.
   */
   FOR element IN 1..l_colors.COUNT
   LOOP
      dbms_output.put_line (element || ' ' || l_colors(element));
   END LOOP;
END;

With SERVEROUTPUT turned on, SQL*Plus prints the following when this code fragment executes:

1 RED
2 GREEN
3 BLUE

Pretty neat, huh? A few important points to notice:

• Oracle, not the programmer, assigns the subscripts of l_colors when fetched from the database.

• Oracle's assigned subscripts begin with 1 (as opposed to 0, as you may be used to in some other languages) and increment by 1.

• Fetching satisfies the requirement to initialize the local collection variable before assigning values to elements. We didn't initialize l_colors with a constructor, but PL/SQL knew how to deal with it.

You can also make changes to the contents of the nested table and just as easily move the data back into a database table. Just to be mischievous, let's create a Fuschia-Green-Blue color model:

DECLARE
   color_tab Color_tab_t;
BEGIN
   SELECT colors INTO color_tab FROM color_models
      WHERE model_type = 'RGB';
   FOR element IN 1..color_tab.COUNT
   LOOP
      IF color_tab(element) = 'RED'
      THEN
         color_tab(element) := 'FUSCHIA';
      END IF;
   END LOOP;
   /* Here is the cool part of this example. Only one insert
   || statement is needed -- it sends the entire nested table
   || back into the color_models table in the database.
   */
   INSERT INTO color_models VALUES ('FGB', color_tab);


END;

19.4.1.4 VARRAY integration

Does this database-to-PL/SQL integration work for VARRAYs 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 does preserve the order of the elements.

Preserving the order of VARRAY elements is actually a fairly useful capability. It makes possible something you cannot do in a pure relational database: embedding meaning in the order of the data. 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. By 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.

Thinking about this order-preservation of VARRAYs brings to mind some 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 19.5 later for some examples.)

19.4.3.1 Adding elements using EXTEND

Adding an element to a collection requires a separate allocation step. Making a "slot" in memory for a collection element is independent from assigning a value to it. If you haven't initialized the collection with a sufficient number of elements (null or otherwise), you must first use the EXTEND procedure on the variable. (For the formal syntax and usage of this procedure, refer to Section 19.6, "Collection Built-Ins".)

DECLARE
   /* The colors variable begins life initialized but with
   || no elements allocated
   */
   colors Color_tab_t := Color_tab_t();
BEGIN
   colors.EXTEND;  -- allocate space for a single element
   colors(1) := 'GRANITE';  -- this works
   colors(2) := 'HUNTER GREEN';  -- invalid; we only extended by 1
END;

19.4.3.2 Removing elements using DELETE

When you DELETE an element, PL/SQL removes that element from the collection. Interestingly, it doesn't actually remove all traces of the element; in fact, a placeholder gets left behind. That means you can reassign the element later without having to re-allocate the space.

DELETE has three different forms depending on how much you want to remove: one, several (contiguous), or all of the elements. Section 19.6 describes all the forms of this procedure.

In physical terms, PL/SQL actually releases the memory only when your program deletes a sufficient number of elements to free an entire page of memory (unless you DELETE all of the elements, which frees all of the memory immediately). This de-allocation happens automatically and requires no accommodations or devices in your code.

19.4.3.3 ...And what about TRIM?

TRIM is another built-in which lets you remove elements from a collection; it's equally applicable to nested tables and VARRAYs. (Again, refer to Section 19.6 for details.) As its name implies, TRIM drops elements off the end of a collection. Unlike DELETE, TRIM leaves no placeholder behind when it does its work.

Although my programming exercise above didn't need TRIM, this built-in, combined with EXTEND, can be very useful if you want to program a "stack" abstraction. In general, the syntax is simply the following:

collection_name.TRIM(n);

where n is the number of elements to remove. If you don't supply n, it defaults to 1.

Unfortunately, if you use TRIM and DELETE on the same collection, you can get some very surprising results. Consider this scenario: if you DELETE an element at the end of a nested table variable and then do a TRIM on the same variable, how many elements have you removed? You would think that you have removed two elements, but, in fact, you have removed only one. The placeholder that is left by DELETE is what TRIM acts upon.

TIP: To avoid confusion, Oracle Corporation recommends using either DELETE or TRIM, but not both, on a given collection.