Oracle PL/SQL Programming: Guide to Oracle8i Features

In Oracle 8.1, you can now more easily use SQL to operate on PL/SQL collections (nested tables and variable arrays). This feature, built upon the CAST operator, allows you to employ SQL capabilities against data stored in such structures, and also to integrate data in these collections with data in tables.

Here is the syntax needed to reference a collection inside a query:

SELECT column_list FROM TABLE (CAST ( collection AS collection_type )) [ collection_alias ]

Where collection is a collection declared in a PL/SQL block, collection_type is the TYPE from which the collection is declared, and collection_alias is an optional alias for the collection-cast-into-table.

column_list is a list of expressions returned by the query. If the collection TYPE is a nested table or variable array based on a scalar, then column_list must be the keyword COLUMN_VALUE, as shown:

SELECT COLUMN_VALUE FROM TABLE (CAST ...);

If the collection is based on an object, then the column list can directly reference individual elements of the object by name .

Let's look at a few examples. I will create a type of nested table and a database table to use in the scripts (see collsql.sql for the full set of steps):

/* Filename on companion disk: collsql.sql */ CREATE TYPE cutbacks_for_taxcuts AS TABLE OF VARCHAR2(100); / CREATE TABLE lobbying_results ( activity VARCHAR2(200)); INSERT INTO lobbying_results VALUES ('No tax on stock transactions'); INSERT INTO lobbying_results VALUES ('Cut city income taxes');

Then I can merge the data for these two structures together, as follows :

/* Filename on companion disk: collsql.sql */ DECLARE nyc_devolution cutbacks_for_taxcuts := cutbacks_for_taxcuts ( 'Stop rat extermination programs', 'Fire building inspectors', 'Close public hospitals'); BEGIN DBMS_OUTPUT.PUT_LINE ( 'How to Make the NYC Rich Much, Much Richer:'); FOR rec IN ( SELECT COLUMN_VALUE ohmy FROM TABLE (CAST ( nyc_devolution AS cutbacks_for_taxcuts)) UNION SELECT activity FROM lobbying_results) LOOP DBMS_OUTPUT.PUT_LINE (rec.ohmy); END LOOP; END; /

And out comes the following data:

How to Make the NYC Rich Much, Much Richer: Close public hospitals Cut city income taxes Fire building inspectors No tax on stock transactions Stop rat extermination programs

As you can see, the data is sorted, as will happen automatically with a UNION.

Now let's try this capability with a nested table of objects (see collsql2.sql ):

CREATE TYPE labor_source AS OBJECT (labor_type VARCHAR2(30), hourly_rate NUMBER); / CREATE TYPE union_busters AS TABLE OF labor_source; /

In the following block, I query both members of the object individually, employing an ORDER BY clause to rearrange the data:

/* Filename on companion disk: collsql2.sql */ DECLARE low_wage_pressure union_busters := union_busters ( labor_source ('Workfare', 0), labor_source ('Prisoner', '5')); BEGIN FOR rec IN ( SELECT labor_type, hourly_rate FROM TABLE ( CAST (low_wage_pressure AS union_busters)) ORDER BY labor_type) LOOP DBMS_OUTPUT.PUT_LINE ( rec.labor_type '-$' rec.hourly_rate); END LOOP; END; /

And we see this output:

Prisoner- Workfare-

Prisoner-$5 Workfare-$0