19.5.1 The THE Pseudo-function

If you have a column that's a nested table, and you want to insert, update, or delete from the contents of this column, you cannot do so with any SQL statement that is familiar to you from Oracle7. Instead, you will need to use the strangely named keyword THE, which helps tell Oracle which row from the outer table you want to deal with.

Earlier, we created the color_models table:

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

We had inserted a row with model_type ='RGB' and a colors column containing ('RED', 'GREEN', 'BLUE'). Imagine now that we've populated color_models with a half dozen or so records.

One question that might have come into your mind is: how can we retrieve all of the colors for a single model using a SELECT statement?

SELECT VALUE(c) FROM
   THE(SELECT colors FROM color_models
        WHERE model_type = 'RGB') c;

OK, you can exhale now. The meaning of this statement is "retrieve the individual elements of RGB color model." Or, more literally, "retrieve the value of each element of the colors nested table within the color_models outer table." Sure enough, it displays the following:[3]

[3] As of Oracle 8.0.4.

VALUE(C)
------------------------------
RED
GREEN
BLUE

I guess it's really not that weird; we're just substituting a subquery for a table in the FROM clause.

Why does the language use the keyword name THE instead of, say, OUTER? Oracle's documentation says "THE (select expression) identifies the nested table on which the DML operation is to be performed." I guess somebody at Oracle has a sense of humor!

Another way you could have expressed the previous query is to use the predefined alias COLUMN_VALUE:

SELECT COLUMN_VALUE FROM
   THE(SELECT colors FROM color_models
        WHERE model_type = 'RGB');

COLUMN_VALUE is a way of referring to elements of a nested table of scalars. It is a syntactic shortcut to achieve the same result as the previous example.

The general structure of a SELECT statement with THE is shown here:

SELECT <expression list>
FROM THE(SELECT <outer column name>
           FROM <outer table>          -- which outer table row?
          WHERE <condition on outer table>)
WHERE <condition on inner table>;

Where:

expression list

Is COLUMN_VALUE or a SELECT-list, depending on whether the retrieved collection is a scalar or a composite. If it's a composite (such as an object), then the Select-list can include any of the object type's attributes.

outer column name

Refers to the name you've given the outer table column, which is a collection type.

The WHERE condition on the outer table must retrieve zero or one row(s); otherwise, you'll get the message "ORA-01427: single-row subquery returns more than one row."

You can also use a THE subquery as the target of an INSERT, UPDATE, and DELETE statements. Some brief examples:

-- change BLUE to BURGUNDY inside the collection
UPDATE THE(SELECT colors
                  FROM color_models
                 WHERE model_type = 'RGB')
   SET COLUMN_VALUE = 'BURGUNDY'
 WHERE COLUMN_VALUE = 'BLUE';

-- add a silly extra color
INSERT INTO THE(SELECT colors
                  FROM color_models
                 WHERE model_type = 'RGB')
VALUES ('EXTRA-COLOR');

-- show the current colors
SELECT COLUMN_VALUE FROM THE(SELECT colors
                               FROM color_models
                              WHERE model_type = 'RGB');

COLUMN_VALUE
-------------------
RED
GREEN
BURGUNDY
EXTRA-COLOR

-- delete the extra color
DELETE THE(SELECT colors
             FROM color_models
            WHERE model_type = 'RGB')
WHERE COLUMN_VALUE = 'EXTRA-COLOR';

The Oracle8 Server Application Developers Guide is a good source of more sample DML on collections.

19.5.2 The CAST Pseudo-function

The new THE pseudo-function does not work directly on VARRAYs. However, it is possible to use Oracle8's CAST function on a VARRAY so we can emulate the SELECT statement shown in the previous section.

19.5.3 The MULTISET Pseudo-function

This new Oracle8 function exists only for use within CASTs. MULTISET allows you to retrieve a set of data and convert it on-the-fly to a collection type. The simplest example is this:

SELECT CAST (MULTISET (SELECT field FROM table) AS collection-type)
   FROM DUAL;

So if we happened to have a relational table of colors:

CREATE TABLE some_colors (
   color_name VARCHAR2(30),
   color_classification VARCHAR2(30));

and we wanted to CAST to a collection so we could fetch a set of them at once, we could do this:

DECLARE
   some_hot_colors Color_tab_t;
BEGIN
   SELECT CAST(MULTISET(SELECT color_name
                          FROM some_colors
                         WHERE color_classification = 'HOT')
            AS Color_tab_t)
     INTO some_hot_colors
     FROM DUAL;
END;

Another way to use MULTISET involves a correlated subquery in the SELECT list:

SELECT outerfield,
  CAST(MULTISET(SELECT field FROM whateverTable
                 WHERE correlationCriteria)
    AS collectionTypeName)
  FROM outerTable;

This technique is useful for making joins look as if they include a collection. Going back to our birds example, suppose that we had a detail table that listed, for each bird, the countries where that species lives:

CREATE TABLE birds (
   genus VARCHAR2(128),
   species VARCHAR2(128),
   colors Color_array_t,
   PRIMARY KEY (genus, species)
);

CREATE TABLE bird_habitats (
   genus VARCHAR2(128),
   species VARCHAR2(128),
   country VARCHAR2(60),
   FOREIGN KEY (genus, species) REFERENCES birds (genus, species)
);

CREATE TYPE Country_tab_t AS TABLE OF VARCHAR2(60);

We should then be able to "smush" the master and detail tables together in a single SELECT that converts the detail records into a collection type. This feature has enormous significance for client/server programs, since the number of round trips can be cut down without the overhead of duplicating the master records with each and every detail record:

DECLARE
   CURSOR bird_curs IS
      SELECT b.genus, b.species,
         CAST(MULTISET(SELECT bh.country FROM bird_habitats bh
                        WHERE bh.genus = b.genus
                          AND bh.species = b.species)
           AS country_tab_t)
        FROM birds b;
   bird_row bird_curs%ROWTYPE;
BEGIN
   OPEN bird_curs;
   FETCH bird_curs into bird_row;
   CLOSE bird_curs;
END;

NOTE: In Oracle 8.0.3 this code produces the error "PLS-00201: identifier 'B.GENUS' must be declared." It works properly in 8.0.4. See Oracle's SQL Reference Guide for more information and examples. As with the CAST pseudo-function, MULTISET cannot serve as the target of an INSERT, UPDATE, or DELETE statement.

The workaround for this bug is to create a packaged function which will return the desired data expressed in the form of a collection. Then you can use this function, rather than the correlated subquery, in the SELECT statement. Here is the package specification that we need:

CREATE PACKAGE fixbug AS
    FUNCTION get_country_tab (p_genus IN VARCHAR2,
      p_species IN VARCHAR2)
      RETURN Country_tab_t;

   PRAGMA RESTRICT_REFERENCES (get_country_tab, WNDS, RNPS, WNPS);
END;

And the body:

CREATE PACKAGE BODY fixbug AS
   FUNCTION get_country_tab (p_genus IN VARCHAR2,p_species IN VARCHAR2)
     RETURN Country_tab_t
   IS
      l_country_tab Country_tab_t;
      CURSOR ccur IS
         SELECT CAST(MULTISET(SELECT bh.country FROM bird_habitats bh
                               WHERE bh.species = p_species
                                 AND bh.genus = p_genus)
              AS country_tab_t)
        FROM DUAL;

   BEGIN
      OPEN ccur;
      FETCH ccur INTO l_country_tab;
      CLOSE ccur;
      RETURN l_country_tab;

   END;
END;

Note that the CAST above uses a selection from DUAL to substitute for the correlated subquery.

We can now rewrite our original PL/SQL fragment as follows:

DECLARE
   CURSOR bird_curs IS
      SELECT b.genus, b.species,
        fixbug.get_country_tab(b.genus, b.species)
        FROM birds b;
   bird_row bird_curs%ROWTYPE;
BEGIN
   OPEN bird_curs;
   FETCH bird_curs into bird_row;
   CLOSE bird_curs;
END;

19.5.4 The TABLE Pseudo-function

Just what you were hoping for, another use of the TABLE keyword! In this case, TABLE is operating as a function that coerces a collection-valued column into something you can SELECT from. It sounds complicated, but this section presents an example that's not too hard to follow.

Looking at it another way, let's say that you have a database table with a column of a collection type. How can you figure out which rows in the table contain a collection that meets certain criteria? That is, how can you select from the database table, putting a WHERE clause on the collection's contents?

Wouldn't it be nice if you could just say:

SELECT *
  FROM table_name
 WHERE collection_column
       HAS CONTENTS 'whatever';   -- invalid; imaginary syntax!

Logically, that's exactly what you can do with the TABLE function. Going back to our color_models database table, how could we get a listing of all color models which contain the color RED? Here's the real way to do it:

SELECT *
  FROM color_models c
 WHERE 'RED' IN
       (SELECT * FROM TABLE(c.colors));

which, in SQL*Plus, returns

MODEL_TYPE   COLORS
------------ ------------------------------------------------------
RGB          COLOR_TAB_T('RED', 'GREEN', 'BLUE')

The query means "go through the color_models table and return all rows whose list of colors contains at least one RED element." Had there been more rows with a RED element in their colors column, these rows too would have appeared in our SQL*Plus result set.

As illustrated above, TABLE accepts an alias-qualified collection column as its argument:

TABLE(alias_name.collection_name)

TABLE returns the contents of this collection coerced into a virtual database table. Hence, you can SELECT from it; in our example, it's used in a subquery.

Does the TABLE function remind you vaguely of the THE pseudo-function? Recall our THE example:

SELECT VALUE(c) FROM
   THE(SELECT colors FROM color_models
        WHERE model_type = 'RGB') c;

which (in Oracle 8.0.4 and later) returns:

VALUE(C)
------------------------------
RED
GREEN
BLUE

So what is the difference between the pseudo-functions THE and TABLE? Both return something that, for purposes of the rest of the SQL statement, serves as a "virtual database table." So the difference between the functions must lie in that on which they operate -- their "inputs." The TABLE function operates on a (column-typed) nested table. By contrast, the pseudo-function THE operates on a SELECT statement's result set that contains exactly one row with one column which is a (column-typed) nested table.

As it turns out, the TABLE function gets called "under the covers" whenever you use THE as the target of an INSERT, UPDATE, or DELETE statement. This under-the-covers call coerces the results of the subquery into a virtual database table upon which the DML makes sense to operate.

TIP: To repeat an earlier admonition, none of the collection pseudo-functions are available from within PL/SQL, but PL/SQL programmers will certainly want to know how to use these gizmos in their SQL statements!

Personally, I find these new features fascinating, and I enjoy the mental calisthenics required to understand and use them. Maybe mine isn't a universal sentiment, but at least you can admit that Oracle hasn't let their language technology get tired!