In the case of collections, TABLE is operating as a function that coerces a collection-valued column into something you can SELECT from. 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:

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

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

  FROM color_models c
       (SELECT * FROM TABLE(c.colors));

which, in SQL*Plus, returns

------------ ------------------------------------------------------
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 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. The TABLE function is similar to THE nested table function. Recall our THE example:

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

which (in Oracle 8.0.4 and later) returns:


The difference between the pseudo-functions THE and TABLE lies in 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.

Note: TABLE is available only within SQL statements. That means you can’t use it directly within PL/SQL. However, it is useful in SQL statements that appear in your PL/SQL programs.