10.4 SQL Operations on Collections

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-$5
Workfare-$0