The TABLE Pseudo-Function

The TABLE operator casts or converts 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 that 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 pseudo-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 returns:

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

What is the difference between 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. In 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.

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

You will also find the pseudo-functions, particularly TABLE, very handy when you are taking advantage of Oracle9i's new table function capability. A table function is a function that returns a collection, and it can be used in the FROM clause of a query. This functionality is explored in Chapter 16.

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 must admit that Oracle hasn't let its language technology get tired!