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 you know from "traditional" SQL. Instead, you will need to use the strangely namedkeyword THE, which helps tell Oracle which row from the outer table you want to deal with.

The THE function has been replaced in Oracle8i and above by the TABLE operator, which is discussed in the next section.

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 the 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:

VALUE(C) ------------------------------REDGREENBLUE

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

Another way you could have expressed the previous query is using the predefinedalias COLUMN_VALUE, as shown in the following example. 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.

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

You can also use a THE subquery as the target of anINSERT, UPDATE, or DELETE statement. Here are some examples:

BEGIN -- 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'); -- delete the extra color DELETE THE(SELECT colors FROM color_models WHERE model_type = 'RGB') WHERE COLUMN_VALUE = 'EXTRA-COLOR';END;