Collection Pseudo-Functions

I've been working with Oracle's SQL for more than thirteen years and PL/SQL for more than eight, but my brain has rarely turned as many cartwheels over SQL's semantics as it did when I first contemplated the collection pseudo-functions introduced in Oracle8. These pseudo-functions exist to coerce database tables into acting like collections, and vice versa. Because there are some manipulations that work best when data is in one form versus the other, these functions give application programmers access to a rich and interesting set of structures and operations.

The collection pseudo-functions are not available in PL/SQL proper, only in SQL. You can, however, employ these operators in SQL statements that appear in your PL/ SQL code, and it is extremely useful to understand how and when to do so. We'll see examples in the following sections.

The four collection pseudo-functions are as follows:

THE (now deprecated)

Maps a single column value in a single row into a virtual database table. This pseudo-function allows you to manipulate the elements of a persistent collection.

CAST

Maps a collection of one type to a collection of another type. This can encompass mapping a VARRAY into a nested table.

MULTISET

Maps a database table to a collection. With MULTISET and CAST, you can actually retrieve rows from a database table as a collection-typed column.

TABLE

Maps a collection to a database table. This is the inverse of MULTISET.

Oracle introduced these pseudo-functions in order to manipulate collections that live in the database. They are important to our PL/SQL programs for several reasons, not least of which is that they provide an incredibly efficient way to move data between the database and the application.

Yes, these pseudo-functions can be puzzling. But if you're the kind of person who gets truly excited by arcane code, these SQL extensions introduced in Oracle8 will make you jumping-up-and-down silly.