Oracle PL/SQL Programming, Third Edition

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.

Tip

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 ...