Skip Headers
Oracle® Database Object-Relational Developer's Guide
11g Release 2 (11.2)

Part Number E11822-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

5 Support for Collection Data Types

Comment: Modified on May 20, 2011 11:37 am

REM @tdadobjcol.sql
REM examples from Ch 5 Collections
connect hr/hr
REM 6.06.08
SET PAGES 200
SET LIN 140
SET SERVEROUTPUT ON SIZE UNLIMITED FORMAT WRAPPED
ALTER SESSION SET PLSQL_WARNINGS = 'enable:all';
SPOOL tdadobj_col.log
SELECT TO_CHAR(SYSDATE, 'fmMonth DD YYYY') Execution_Date from dual;
SELECT * FROM V$VERSION;
REM                   ********** examples are grouped, first group require ex 5-1 and 5-3

This chapter explains how to create and manage these collection types: varrays and nested tables.

See Also:

Oracle Database PL/SQL Language Reference for a complete introduction to collections

This chapter contains these topics:

Collection Data Types

Oracle supports the varray and nested table collection data types.

If you need to store only a fixed number of items, or loop through the elements in order, or often need to retrieve and manipulate the entire collection as a value, then use a varray.

If you need to run efficient queries on a collection, handle arbitrary numbers of elements, or perform mass insert, update, or delete operations, then use a nested table. See "Design Considerations for Collections".

This section includes the following topics:

Creating a Collection Type

This section shows the creation of a nested table type. Creation of a VARRAY type is demonstrated in "Varrays".

Example 5-1 demonstrates creating a person_typ object and a people_typ as a nested table type of person_typ objects, which are both used in subsequent examples in this chapter.

Example 5-1 CREATE TYPE person_typ for Subsequent Examples

-- Ex. 5-1 
CREATE TYPE person_typ AS OBJECT (
  idno           NUMBER,
  name           VARCHAR2(30),
  phone          VARCHAR2(20),
  MAP MEMBER FUNCTION get_idno RETURN NUMBER, 
  MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY person_typ ) );
/

CREATE TYPE BODY person_typ AS
  MAP MEMBER FUNCTION get_idno RETURN NUMBER IS
  BEGIN
    RETURN idno;
  END;
  MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY person_typ ) IS
  BEGIN
    -- use the put_line procedure of the DBMS_OUTPUT package to display details
    DBMS_OUTPUT.put_line(TO_CHAR(idno) || ' - '  || name || ' - '  || phone);
  END;
END;
/

CREATE TYPE people_typ AS TABLE OF person_typ; -- nested table type
/

Creating an Instance of a VARRAY or Nested Table

You create an instance of a collection type in the same way that you create an instance of any other object type, namely, by calling the constructor method of the type. The name of a constructor method is simply the name of the type. You specify the elements of the collection as a comma-delimited list of arguments to the method, for example.

( person_typ(1, 'John Smith', '1-650-555-0135'),

Calling a constructor method with an empty list creates an empty collection of that type. Note that an empty collection is an actual collection that happens to be empty; it is not the same as a null collection. See "Design Considerations for Nested Tables" for more information on using nested tables.

Constructor Methods for Collections

You can use a constructor method in a SQL statement to insert values into a nested table.

Example 5-2 first creates a table that contains an instance of the nested table type people_typ, named people_column, and then shows how to use the constructor method in a SQL statement to insert values into people_typ. This example uses a literal invocation of the constructor method.

Example 5-2 Using the Constructor Method to Insert Values into a Nested Table

-- Ex. 5-2 Using the Constructor Method to Insert Values into a Nested Table
-- Requires Ex. 5-1
CREATE TABLE people_tab (
    group_no NUMBER,
    people_column people_typ )  -- an instance of nested table
    NESTED TABLE people_column STORE AS people_column_nt; -- storage table for NT
CREATE INDEX people_idno_idx ON people_column_nt(idno);

INSERT INTO people_tab VALUES (
            100,
            people_typ( person_typ(1, 'John Smith', '1-650-555-0135'),
                        person_typ(2, 'Diane Smith', NULL)));
SELECT c.idno FROM people_tab p, TABLE(p.people_column) c;
DROP INDEX people_idno_idx;
DROP TABLE people_tab;

When you declare a table column to be of an object type or collection type, you can include a DEFAULT clause. This provides a value to use in cases where you do not explicitly specify a value for the column. The DEFAULT clause must contain a literal invocation of the constructor method for that object or collection.

Example 5-3 shows how to use literal invocations of constructor methods to specify defaults for the person_typ object and the people_typ nested table:

Example 5-3 Creating the department_persons Table Using the DEFAULT Clause

-- Ex. 5-3 Creating the department_persons Table Using the DEFAULT Clause example
-- requires Ex. 5-1
CREATE TABLE department_persons (
  dept_no    NUMBER PRIMARY KEY,
  dept_name  CHAR(20),
  dept_mgr   person_typ DEFAULT person_typ(10,'John Doe',NULL),
  dept_emps  people_typ DEFAULT people_typ() ) -- instance of nested table type
  NESTED TABLE dept_emps STORE AS dept_emps_tab;

INSERT INTO department_persons VALUES 
   ( 101, 'Physical Sciences', person_typ(65,'Vrinda Mills', '1-650-555-0125'),
           people_typ( person_typ(1, 'John Smith', '1-650-555-0135'), 
                        person_typ(2, 'Diane Smith', NULL) ) );
INSERT INTO department_persons VALUES 
  ( 104, 'Life Sciences', person_typ(70,'James Hall', '1-415-555-0101'), 
    people_typ() ); -- an empty people_typ table

Note that people_typ() is a literal invocation of the constructor method for an empty people_typ nested table.

The department_persons table can be queried in two ways as shown in Example 5-16 and Example 5-17.

Varrays

A varray is an ordered set of data elements. All elements of a given varray are of the same data type or a subtype of the declared one. Each element has an index, which is a number corresponding to the position of the element in the array. The index number is used to access a specific element.

When you define a varray, you specify the maximum number of elements it can contain, although you can change this number later. The number of elements in an array is the size of the array.

The following statement creates an array type email_list_arr that has no more than ten elements, each of data type VARCHAR2(80).

-- Declaring a VARRAY Data Type 
CREATE TYPE email_list_arr AS VARRAY(10) OF VARCHAR2(80);
/
DROP TYPE email_list_arr FORCE;

Example 5-4 creates a VARRAY type that is an array of an object type. The phone_varray_typ VARRAY type is used as a data type for a column in the dept_phone_list table. The INSERT statements show how to insert values into phone_varray_typ by invoking the constructors for the varray phone_varray_typ and the object phone_typ.

Example 5-4 Creating and Populating a VARRAY Data Type

-- Ex. 5-4 Creating and populating a VARRAY Data Type
CREATE TYPE phone_typ AS OBJECT (
    country_code   VARCHAR2(2), 
    area_code      VARCHAR2(3),
    ph_number      VARCHAR2(7));
/
CREATE TYPE phone_varray_typ AS VARRAY(5) OF phone_typ;
/
CREATE TABLE dept_phone_list (
  dept_no NUMBER(5), 
  phone_list phone_varray_typ);

INSERT INTO dept_phone_list VALUES (
   100,
   phone_varray_typ( phone_typ ('01', '650', '5550123'),
                      phone_typ ('01', '650', '5550148'),
                      phone_typ ('01', '650', '5550192')));
DROP TABLE dept_phone_list;
DROP TYPE phone_varray_typ FORCE;
DROP TYPE phone_typ FORCE;

Creating an array type, as with a SQL object type, does not allocate space. It defines a data type, which you can use as:

  • The data type of a column of a relational table.

  • An object type attribute.

  • The type of a PL/SQL variable, parameter, or function return value.

A varray is normally stored inline, that is, in the same tablespace as the other data in its row. If it is sufficiently large, Oracle stores it as a BLOB. See "Storage Considerations for Varrays".

You can create a VARRAY type of XMLType or LOB type for procedural purposes, such as in PL/SQL or view queries. However, database storage for varrays of these types is not supported. Thus you cannot create an object table or an object type column of a varray type of XMLType or LOB type.

See Also:

Oracle Database SQL Language Reference for information and examples on the STORE AS LOB clause of the CREATE TABLE statement

Nested Tables

A nested table is an unordered set of data elements, all of the same data type. No maximum is specified in the definition of the table, and the order of the elements is not preserved. You select, insert, delete, and update in a nested table just as you do with ordinary tables using the TABLE expression.

A nested table can be viewed as a single column. If the column in a nested table is an object type, the table can also be viewed as a multi-column table, with a column for each attribute of the object type.

To declare nested table types, use the CREATE TYPE ... AS TABLE OF statement. For example:

CREATE TYPE people_typ AS TABLE OF person_typ; 

A table type definition does not allocate space. It defines a type, which you can use as:

  • The data type of a column of a relational table.

  • An object type attribute.

  • A PL/SQL variable, parameter, or function return type.

Storing Elements of Nested Tables

Elements of a nested table are actually stored in a separate storage table.

Oracle stores nested table data in a single storage table associated with the object table for both nested table types that are columns in a relational table or attributes in an object table. The storage table contains a column that identifies the parent table row or object that each element of the nested table belongs to. See Figure 9-2, "Nested Table Storage".

The NESTED TABLE..STORE AS clause specifies storage names for nested tables. Storage names are used to create an index on a nested table.

Example 5-5 demonstrates creating and populating a nested table, and specifying the nested table storage using the person_typ object and the people_typ nested table as defined in Example 5-1.

Example 5-5 Creating and Populating Simple Nested Tables

-- Ex. 5-5 Creating and Populating Simple Nested Tables
-- Requires 5-1
CREATE TABLE students (
   graduation DATE, 
   math_majors people_typ, -- nested tables (empty)
   chem_majors people_typ, 
   physics_majors people_typ)
  NESTED TABLE math_majors STORE AS math_majors_nt  -- storage tables
  NESTED TABLE chem_majors STORE AS chem_majors_nt
  NESTED TABLE physics_majors STORE AS physics_majors_nt;

CREATE INDEX math_idno_idx ON math_majors_nt(idno);
CREATE INDEX chem_idno_idx ON chem_majors_nt(idno);
CREATE INDEX physics_idno_idx ON physics_majors_nt(idno);

INSERT INTO students (graduation) VALUES ('01-JUN-03');
UPDATE students
  SET math_majors = 
        people_typ (person_typ(12, 'Bob Jones', '650-555-0130'), 
                    person_typ(31, 'Sarah Chen', '415-555-0120'),
                    person_typ(45, 'Chris Woods', '415-555-0124')),
      chem_majors = 
        people_typ (person_typ(51, 'Joe Lane', '650-555-0140'), 
                    person_typ(31, 'Sarah Chen', '415-555-0120'),
                    person_typ(52, 'Kim Patel', '650-555-0135')),
   physics_majors = 
        people_typ (person_typ(12, 'Bob Jones', '650-555-0130'), 
                    person_typ(45, 'Chris Woods', '415-555-0124'))
WHERE graduation = '01-JUN-03';

SELECT m.idno math_id, c.idno chem_id, p.idno physics_id  FROM students s,
 TABLE(s.math_majors) m, TABLE(s.chem_majors) c, TABLE(s.physics_majors) p;
DROP INDEX math_idno_idx;
DROP INDEX chem_idno_idx;
DROP INDEX physics_idno_idx;

A convenient way to access the elements of a nested table individually is to use a nested cursor or the TABLE function. See "Querying Collections".

Specifying a Tablespace When Storing a Nested Table

A nested table can be stored in a different tablespace than its parent table. In Example 5-6, the nested table is stored in the system tablespace:

Example 5-6 Specifying a Different Tablespace for Storing a Nested Table

-- Ex. 5-6 Specifying a Different Tablespace for Storing a Nested Table 
-- Requires Ex. 5-1, must remove code in Ex. 5-2 if created
CREATE TABLE people_tab (
    people_column people_typ )
    NESTED TABLE people_column STORE AS people_column_nt (TABLESPACE system);

If the TABLESPACE clause is not specified, then the storage table of the nested table is created in the tablespace where the parent table is created. For multilevel nested tables, Oracle creates the child table in the same tablespace as its immediately preceding parent table.

You can issue an ALTER TABLE.. MOVE statement to move a table to a different tablespace. If you do this on a table with nested table columns, only the parent table moves; no action is taken on the storage tables of the nested table. To move a storage table for a nested table to a different tablespace, issue ALTER TABLE.. MOVE on the storage table. For example:

-- Alter table move tablespace example
-- not sample schema
ALTER TABLE people_tab MOVE TABLESPACE system;  -- moving table
ALTER TABLE people_column_nt MOVE TABLESPACE example; -- moving storage table
DROP TABLE people_tab;

Now the people_tab table is in the system tablespace and the nested table storage is stored in the example tablespace.

Increasing the Size and Precision of VARRAY and Nested Table Elements

When the element type of a VARRAY type or nested table type is a variable character, or a RAW or numeric type, you can increase the size of the variable character or RAW type, or increase the precision of the numeric type. A new type version is generated for the VARRAY type or nested table type.

You make these changes using an ALTER TYPE..MODIFY statement, which has these options:

  • INVALIDATE: Invalidates all dependent objects

  • CASCADE: Propagates the change to its type and table dependents

See Also:

"ALTER TYPE Statement for Type Evolution" for further description of INVALIDATE and CASCADE

Example 5-7 increases the sizes of a VARRAY and a nested table element type.

Example 5-7 Increasing the Size of an Element Type in a VARRAY and Nested Table

-- Ex. 5-7 Increasing the Size of an Element Type in a VARRAY and Nested Table 
CREATE TYPE email_list_arr AS VARRAY(10) OF VARCHAR2(80);
/
ALTER TYPE email_list_arr MODIFY ELEMENT TYPE VARCHAR2(100) CASCADE;

CREATE TYPE email_list_tab AS TABLE OF VARCHAR2(30);
/
ALTER TYPE email_list_tab MODIFY ELEMENT TYPE VARCHAR2(40) CASCADE;

Increasing VARRAY Limit Size

The ALTER TYPE ... MODIFY LIMIT syntax allows you to increase the number of elements of a VARRAY type. If the number is increased, a new type version is generated for the VARRAY type, and becomes part of the type change history.

The ALTER TYPE ... MODIFY LIMIT statement has these options:

  • INVALIDATE: Invalidates all dependent objects

  • CASCADE: Propagates the change to its type and table dependents

Example 5-8 Increasing the VARRAY Limit Size

-- Ex. 5-8 Increasing the VARRAY Limit Size
-- if you have already creating following types, drop them.
DROP TYPE email_list_tab FORCE;
DROP TYPE email_list_arr FORCE;
CREATE TYPE email_list_arr AS VARRAY(10) OF VARCHAR2(80);
/
CREATE TYPE email_list_typ AS OBJECT (
    section_no   NUMBER, 
    emails       email_list_arr);
/

CREATE TYPE email_varray_typ AS VARRAY(5) OF email_list_typ;
/

ALTER TYPE email_varray_typ MODIFY LIMIT 100 INVALIDATE;
DROP TYPE email_varray_typ FORCE;

When a VARRAY type is altered, changes are propagated to the dependent tables. See "Propagating VARRAY Size Change".

Creating a Varray Containing LOB References

To create a varray of LOB references, first define a VARRAY type of type REF email_list_typ. Note: email_list_typ was defined in Example 5-8. Next, create a table dept_email_list and define a column email_addrs of the array type in it.

Example 5-9 Creating a VARRAY Containing LOB References

-- Ex. 5-9 Creating a VARRAY Containing LOB References example
-- Requires Ex. 5-8
CREATE TYPE ref_email_varray_typ AS VARRAY(5) OF REF email_list_typ;
/

CREATE TABLE dept_email_list (
  dept_no NUMBER, 
  email_addrs ref_email_varray_typ)
  VARRAY email_addrs STORE AS LOB dept_emails_lob3;
DROP TABLE dept_email_list;
DROP TYPE ref_email_varray_typ FORCE;
DROP TYPE email_list_typ FORCE;

Multilevel Collection Types

Multilevel collection types are collection types whose elements are themselves directly or indirectly another collection type. Possible multilevel collection types are:

Like single-level collection types, multilevel collection types:

This section contains the following topics:

Nested Table Storage Tables for Multilevel Collection Types

A nested table type column or object table attribute requires a storage table to store rows for all its nested tables as described in "Storing Elements of Nested Tables". With a multilevel nested table collection of nested tables, you must specify nested-table storage clauses for both the inner set and the outer set of nested tables.

Example 5-10 creates the multilevel collection type nt_country_typ, a nested table of nested tables. The example models a system of corporate regions in which each region has a nested table collection of the countries, and each country has a nested table collection of its locations. This example requires the regions, countries, and locations tables of the Oracle HR sample schema.

See Also:

Oracle Database Sample Schemas for information on using sample schemas

In Example 5-10, the SQL statements create the table region_tab, which contains the column countries, whose type is a multilevel collection, nt_country_typ. This multilevel collection is a nested table of an object type that has the nested table attribute locations. Separate nested table clauses are provided for the outer countries nested table and for the inner locations nested table.

Example 5-10 Multilevel Nested Table Storage

-- Ex. 5-10  Multilevel Nested Table Storage
-- Requires the HR sample schema
CREATE TYPE location_typ AS OBJECT (
  location_id      NUMBER(4),
  street_address   VARCHAR2(40),
  postal_code      VARCHAR2(12),
  city             VARCHAR2(30),
  state_province   VARCHAR2(25));
/

CREATE TYPE nt_location_typ AS TABLE OF location_typ;  -- nested table type
/

CREATE TYPE country_typ AS OBJECT (
  country_id     CHAR(2),
  country_name   VARCHAR2(40),
  locations      nt_location_typ); -- inner nested table
/

CREATE TYPE nt_country_typ AS TABLE OF country_typ;  -- multilevel collection type
/

CREATE TABLE region_tab (
  region_id     NUMBER,
  region_name   VARCHAR2(25),
  countries     nt_country_typ) -- outer nested table
  NESTED TABLE countries STORE AS nt_countries_tab
   (NESTED TABLE locations STORE AS nt_locations_tab);
DROP TABLE region_tab;

In Example 5-10 you can refer to the inner nested table locations by name because this nested table is a named attribute of an object. However, if the inner nested table is not an attribute of an object, it has no name. The keyword COLUMN_VALUE is provided for this case.

Example 5-11 shows keyword COLUMN_VALUE used in place of a name for an inner nested table.

Example 5-11 Multilevel Nested Table Storage Using the COLUMN_VALUE Keyword

-- Ex. 5-11 Multilevel Nested Table Storage Using the COLUMN_VALUE Keyword 
CREATE TYPE inner_table AS TABLE OF NUMBER;
/
CREATE TYPE outer_table AS TABLE OF inner_table;
/
CREATE TABLE tab1 (
  col1 NUMBER,  -- inner nested table, unnamed
  col2 outer_table)
NESTED TABLE col2 STORE AS col2_ntab
  (NESTED TABLE COLUMN_VALUE STORE AS cv_ntab);
DROP TABLE tab1;
DROP TYPE outer_table FORCE;
DROP TYPE inner_table FORCE;

Example 5-12 shows how to specify physical attributes for the storage tables in the nested table clause.

Example 5-12 Specifying Physical Attributes for Nested Table Storage

-- Ex. 5-12  Specifying Physical Attributes for Nested Table Storage
-- Requires Ex. 5-10
-- drop the following if you have previously created it
DROP TABLE region_tab FORCE;

CREATE TABLE region_tab (
  region_id     NUMBER,
  region_name   VARCHAR2(25),
  countries     nt_country_typ)
  NESTED TABLE countries STORE AS nt_countries_tab (
   (PRIMARY KEY (NESTED_TABLE_ID, country_id))
   ORGANIZATION INDEX COMPRESS
   NESTED TABLE locations STORE AS nt_locations_tab);

Every nested table storage table contains a column, referenceable by NESTED_TABLE_ID, that keys rows in the storage table to the associated row in the parent table. A parent table that is itself a nested table has two system-supplied ID columns:

  • A system-supplied ID column that is referenceable by NESTED_TABLE_ID, which keys its rows back to rows in its parent table.

  • A system-supplied ID column that is hidden and referenced by the NESTED_TABLE_ID column in its nested table children.

In Example 5-12, nested table countries is made an index-organized table (IOT) by adding the ORGANIZATION INDEX clause and assigning the nested table a primary key in which the first column is NESTED_TABLE_ID. This column contains the ID of the row in the parent table with which a storage table row is associated. Specifying a primary key with NESTED_TABLE_ID as the first column and index-organizing the table causes Oracle database to physically cluster all the nested table rows that belong to the same parent row, for more efficient access.

If you do not specify a primary key with a NESTED_TABLE_ID column, then the database automatically creates a b-tree index on the NESTED_TABLE_ID column for better performance.

Each nested table needs its own table storage clause, so you must have as many nested table storage clauses as you have levels of nested tables in a collection. See "Nested Table Storage".

Varray Storage for Multilevel Collections

Multilevel varrays are stored in one of two ways, depending on whether the varray is a varray of varrays or a varray of nested tables.

  • In a varray of varrays, the entire varray is stored inline in the row unless it is larger than approximately 4000 bytes or LOB storage is explicitly specified.

  • In a varray of nested tables, the entire varray is stored in a LOB, with only the LOB locator stored in the row. There is no storage table associated with nested table elements of a varray.

You can explicitly specify LOB storage for varrays. The following example does this for a nested table of varray elements.

Example 5-13 shows explicit LOB storage specified for a varray of varray type.

Example 5-13 Specifying LOB Storage for a VARRAY of VARRAY Type

-- Ex. 5-13 Specifying LOB Storage for a VARRAY Type
-- Requires Ex. 5-8, drop following if created

DROP TYPE email_varray_typ FORCE;
CREATE TYPE email_list_typ2 AS OBJECT (
    section_no   NUMBER, 
    emails       email_list_arr);
/

CREATE TYPE email_varray_typ AS VARRAY(5) OF email_list_typ2;
/

CREATE TABLE dept_email_list2 (
  dept_no NUMBER, 
  email_addrs email_varray_typ)
  VARRAY email_addrs STORE AS LOB dept_emails_lob2;

See "Storage Considerations for Varrays". See also Oracle Database SecureFiles and Large Objects Developer's Guide.

Example 5-14 Specifying LOB Storage for a Nested Table of VARRAYs

-- Ex. 5-14 Specifying LOB Storage for a Nested Table of VARRAY Elements
-- drop the following types if you have created them
DROP TYPE email_list_typ FORCE;
DROP TABLE dept_email_list FORCE;
DROP TYPE email_list_arr FORCE;

CREATE TYPE email_list_arr AS VARRAY(10) OF VARCHAR2(80);
/

CREATE TYPE email_list_typ AS TABLE OF email_list_arr;
/

CREATE TABLE dept_email_list (
  dept_no NUMBER, 
  email_addrs email_list_typ)
  NESTED TABLE email_addrs STORE AS email_addrs_nt
  (
VARRAY COLUMN_VALUE STORE AS LOB
 dept_emails_lob);
DROP TABLE dept_email_list2;
DROP TABLE dept_email_list;
DROP TYPE email_varray_typ FORCE;
DROP TYPE email_list_typ FORCE;
DROP TYPE email_list_typ2 FORCE;
DROP TYPE email_list_arr FORCE;

Example 5-14 shows the COLUMN_VALUE keyword used with varrays. See Example 5-11 for discussion of this keyword and its use with nested tables.

Constructors for Multilevel Collections

Multilevel collection types are created by calling the constructor of the respective type, just like single-level collections and other object types. The constructor for a multilevel collection type is a system-defined function that has the same name as the type and returns a new instance of it. Constructor parameters have the names and types of the attributes of the object type.

Example 5-15 shows the constructor call for the multilevel collection type nt_country_typ. The nt_country_typ constructor calls the country_typ constructor, which calls the nt_location_typ, which calls the location_typ constructor.

Note:

nt_country_typ is a multilevel collection because it is a nested table that contains another nested table as an attribute.

Example 5-15 Using Constructors for Multilevel Collections

-- Ex. 5-15 Using Constructors for Multilevel Collections example
-- Requires 5-10 and HR sample schema
INSERT INTO region_tab 
VALUES(1, 'Europe', nt_country_typ( 
  country_typ( 'IT', 'Italy', nt_location_typ (
    location_typ(1000, '1297 Via Cola di Rie','00989','Roma', ''),
    location_typ(1100, '93091 Calle della Testa','10934','Venice','') ) 
    ),
  country_typ( 'CH', 'Switzerland', nt_location_typ (
    location_typ(2900, '20 Rue des Corps-Saints', '1730', 'Geneva', 'Geneve'),
    location_typ(3000, 'Murtenstrasse 921', '3095', 'Bern', 'BE') ) 
    ),
  country_typ( 'UK', 'United Kingdom', nt_location_typ (
    location_typ(2400, '8204 Arthur St', '', 'London', 'London'),
    location_typ(2500, 'Magdalen Centre, The Oxford Science Park', 'OX9 9ZB',
                 'Oxford', 'Oxford'),
    location_typ(2600, '9702 Chester Road', '09629850293', 'Stretford',
                 'Manchester') )
      ) 
  )
);

Operations on Collection Data Types

This section describes the operations on collection data types.

This section contains the following topics:

Querying Collections

There are two general ways to query a table that contains a collection type as a column or attribute.

  • Nest the collections in the result rows that contain them.

  • Distribute or unnest collections so that each collection element appears on a row by itself.

Nesting Results of Collection Queries

The following queries use the department_persons table shown in Example 5-3. The column dept_emps is a nested table collection of person_typ type. The dept_emps collection column appears in the SELECT list like an ordinary scalar column. Querying a collection column in the SELECT list this way nests the elements of the collection in the result row that the collection is associated with.

Example 5-16 shows the query retrieving the nested collection of employees.

Example 5-16 Nesting Results of Collection Queries

-- Ex. 5-16 Nesting Results of Collection Queries
-- Requires Ex. 5-1 and Ex. 5-3
SELECT d.dept_emps 
  FROM department_persons d;

DEPT_EMPS(IDNO, NAME, PHONE)
-------------------------------------------------------------
PEOPLE_TYP(PERSON_TYP(1, 'John Smith', '1-650-555-0135'),
PERSON_TYP(2, 'Diane Smith', '1-650-555-0135'))

The results are also nested if an object type column in the SELECT list contains a collection attribute, even if that collection is not explicitly listed in the SELECT list itself. For example, the query SELECT * FROM department_persons produces a nested result.

Unnesting Results of Collection Queries

Not all tools or applications can deal with results in a nested format. To view Oracle collection data using tools that require a conventional format, you must unnest, or flatten, the collection attribute of a row into one or more relational rows. You can do this using a TABLE expression with the collection. TABLE expressions enable you to query a collection in the FROM clause like a table. In effect, you join the nested table with the row that contains the nested table.

TABLE expressions can be used to query any collection value expression, including transient values such as variables and parameters.

See Also:

Oracle Database SQL Language Reference for further information on the TABLE expression and unnesting collections

The query in Example 5-17, like that of Example 5-16, retrieves the collection of employees, but here the collection is unnested.

Example 5-17 Unnesting Results of Collection Queries

-- Ex. 5- 17 Unnesting Results of Collection Queries
-- Requires Ex. 5-1 and 5-3
SELECT e.* 
  FROM department_persons d, TABLE(d.dept_emps) e;

IDNO NAME PHONE
---------- ------------------------------ ---------------
1 John Smith 1-650-555-0135
2 Diane Smith 1-650-555-0135

Example 5-17 shows that a TABLE expression can have its own table alias. A table alias for the TABLE expression appears in the SELECT list to select columns returned by the TABLE expression.

The TABLE expression uses another table alias to specify the table that contains the collection column that the TABLE expression references. The expression TABLE(d.dept_emps) specifies the department_persons table as containing the dept_emps collection column. To reference a table column, a TABLE expression can use the table alias of any table appearing to the left of it in a FROM clause. This is called left correlation.

In the example, the department_persons table is listed in the FROM clause solely to provide a table alias for the TABLE expression to use. No columns from the department_persons table other than the column referenced by the TABLE expression appear in the result.

The following example produces rows only for departments that have employees.

-- Using TABLE to retrieve rows with employees example
SELECT d.dept_no, e.* 
  FROM department_persons d, TABLE(d.dept_emps) e;

To get rows for departments with or without employees, you can use outer-join syntax:

-- Using TABLE to retrieve rows with no employees example
SELECT d.dept_no, e.* 
  FROM department_persons d, TABLE(d.dept_emps) (+) e;

The (+) indicates that the dependent join between department_persons and e.dept_emps should be NULL-augmented. That is, there will be rows of department_persons in the output for which e.dept_emps is NULL or empty, with NULL values for columns corresponding to e.dept_emps.

Unnesting Queries Containing Table Expression Subqueries

The examples in "Unnesting Results of Collection Queries" show a TABLE expression that contains the name of a collection. Alternatively, a TABLE expression can contain a subquery of a collection.

Example 5-18 returns the collection of employees whose department number is 101.

Example 5-18 Using a Table Expression Containing a Subquery of a Collection

-- Ex. 5-18 Using a Table Expression Containing a Subquery of a Collection
-- Requires Ex. 5-1 and 5-3
SELECT *
  FROM TABLE(SELECT d.dept_emps 
               FROM department_persons d
               WHERE d.dept_no = 101);

Subqueries in a TABLE expression have these restrictions:

  • The subquery must return a collection type.

  • The SELECT list of the subquery must contain exactly one item.

  • The subquery must return only a single collection; it cannot return collections for multiple rows. For example, the subquery SELECT dept_emps FROM department_persons succeeds in a TABLE expression only if table department_persons contains just a single row. If the table contains more than one row, the subquery produces an error.

Example 5-19 shows a TABLE expression used in the FROM clause of a SELECT embedded in a CURSOR expression.

Example 5-19 Using a Table Expression in a CURSOR Expression

--  Ex. 5-19 Using a Table Expression in a CURSOR Expression
-- Requires Ex. 5-1 and 5-3
SELECT d.dept_no, CURSOR(SELECT * FROM TABLE(d.dept_emps)) 
  FROM department_persons d
   WHERE d.dept_no = 101;

Unnesting Queries with Multilevel Collections

Unnesting queries can be also used with multilevel collections, both varrays and nested tables. Example 5-20 shows an unnesting query on a multilevel nested table collection of nested tables. From the table region_tab where each region has a nested table of countries and each country has a nested table of locations, the query returns the names of all regions, countries, and locations.

Example 5-20 Unnesting Queries with Multilevel Collections Using the TABLE Function

-- Ex. 5-20 Unnesting Queries with Multilvel Collections Using the TABLE Function
-- Requires Ex. 5-10 and 5-15
SELECT r.region_name, c.country_name, l.location_id 
  FROM region_tab r, TABLE(r.countries) c, TABLE(c.locations) l;
 
-- the following query is optimized to run against the locations table
SELECT l.location_id, l.city 
  FROM region_tab r, TABLE(r.countries) c, TABLE(c.locations) l;

The output should be as follows:

REGION_NAME               COUNTRY_NAME                             LOCATION_ID
------------------------- ---------------------------------------- -----------
Europe                    Italy                                           1000
Europe                    Italy                                           1100
Europe                    Switzerland                                     2900
Europe                    Switzerland                                     3000
Europe                    United Kingdom                                  2400
Europe                    United Kingdom                                  2500
Europe                    United Kingdom                                  2600
 
7 rows selected.
LOCATION_ID CITY
----------- ------------------------------
       1000 Roma
       1100 Venice
       2900 Geneva
       3000 Bern
       2400 London
       2500 Oxford
       2600 Stretford
 
7 rows selected.

Because no columns of the base table region_tab appear in the second SELECT list, the query is optimized to run directly against the locations storage table.

Outer-join syntax can also be used with queries of multilevel collections. See "Viewing Object Data in Relational Form with Unnesting Queries".

Performing DML Operations on Collections

Oracle supports the following DML operations on collections:

  • Inserts and updates that provide a new value for the entire collection

  • Individual or piecewise updates of nested tables and multilevel nested tables, including inserting, deleting, and updating elements

Oracle does not support piecewise updates on VARRAY columns. However, VARRAY columns can be inserted into or updated as an atomic unit.This section contains these topics:

Piecewise Operations on Nested Tables

For piecewise operations on nested table columns, use the TABLE expression.

The TABLE expression uses a subquery to extract the nested table, so that the INSERT, UPDATE, or DELETE statement applies to the nested table rather than the top-level table.

CAST operators are also helpful. With them, you can do set operations on nested tables using SQL notation, without actually storing the nested tables in the database.

The DML statements in Example 5-21 demonstrate piecewise operations on nested table columns.

Example 5-21 Piecewise Operations on Collections

-- Ex. 5-21 Piecewise Operations on Collections
-- Requires Ex. 5-1 and 5-3
INSERT INTO TABLE(SELECT d.dept_emps 
                  FROM department_persons d
                  WHERE d.dept_no = 101)
   VALUES (5, 'Kevin Taylor', '1-408-555-0199');

UPDATE TABLE(SELECT d.dept_emps 
                  FROM department_persons d
                  WHERE d.dept_no = 101) e   
   SET VALUE(e) = person_typ(5, 'Kevin Taylor', '1-408-555-0199')
   WHERE e.idno = 5;
     
DELETE FROM TABLE(SELECT d.dept_emps 
                  FROM department_persons d
                  WHERE d.dept_no = 101) e
   WHERE e.idno = 5;

Example 5-22 shows VALUE used to return object instance rows for updating:

Example 5-22 Using VALUE to Update a Nested Table

-- Ex. 5-22 Using the VALUE Function to return objects for updating example
-- Requires Ex. 5-1, 5-3
UPDATE TABLE(SELECT d.dept_emps FROM department_persons d 
               WHERE  d.dept_no = 101) p
   SET VALUE(p) = person_typ(2, 'Diane Smith', '1-650-555-0148')
   WHERE p.idno = 2;

Piecewise Operations on Multilevel Nested Tables

Piecewise DML is possible only on multilevel nested tables, not on multilevel varrays. You can perform DML operation atomically on both VARRAYs and nested tables multilevel collections as described in "Collections as Atomic Data Items".

Example 5-23 shows a piecewise insert operation on the countries nested table of nested tables. The example inserts a new country, complete with its own nested table of location_typ:

Example 5-23 Piecewise INSERT on a Multilevel Collection

-- Ex. 5-23 Piecewise INSERT on a Multilevel Collection
-- Requires Ex. 5-10 and 5-15
INSERT INTO TABLE( SELECT countries FROM region_tab r WHERE r.region_id = 2) 
  VALUES ( 'CA', 'Canada', nt_location_typ( 
       location_typ(1800, '147 Spadina Ave', 'M5V 2L7', 'Toronto', 'Ontario')));

Example 5-24 performs a piecewise insert into an inner nested table to add a location for a country. Like the preceding example, this example uses a TABLE expression containing a subquery that selects the inner nested table to specify the target for the insert.

Example 5-24 Piecewise INSERT into an Inner Nested Table

-- Ex. 5-24 Piecewise INSERT into an Inner Nested Table
-- Requires Ex. 5-10 and 5-15
INSERT INTO TABLE( SELECT c.locations 
  FROM TABLE( SELECT r.countries FROM region_tab r WHERE r.region_id = 2) c
  WHERE c.country_id = 'US')
  VALUES (1700, '2004 Lakeview Rd', '98199', 'Seattle', 'Washington');
 
SELECT r.region_name, c.country_name, l.location_id 
  FROM region_tab r, TABLE(r.countries) c, TABLE(c.locations) l;
DROP TABLE region_tab;
DROP TYPE nt_country_typ FORCE;
DROP TYPE country_typ FORCE;
DROP TYPE nt_location_typ FORCE;
DROP TYPE location_typ FORCE;

Atomical Changes on VARRAYs and Nested Tables

This section discusses atomical changes to nested tables and VARRAYs.

Note: While nested tables can also be changed in a piecewise fashions, varrays cannot.

Example 5-25 shows how you can manipulate SQL varray object types with PL/SQL statements. In this example, varrays are transferred between PL/SQL variables and SQL tables. You can insert table rows containing collections, update a row to replace its collection, and select collections into PL/SQL variables.

However, you cannot update or delete individual varray elements directly with SQL; you have to select the varray from the table, change it in PL/SQL, then update the table to include the new varray. You can also do this with nested tables, but nested tables have the option of doing piecewise updates and deletes.

new, was ex 3-17, is integrated

Example 5-25 Using INSERT, UPDATE, DELETE, and SELECT Statements With Varrays

-- Ex. 5-25 using PL/SQL varrays with DML statements
CREATE TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30);
/
CREATE TABLE depts (region VARCHAR2(25), dept_names dnames_var);
BEGIN
   INSERT INTO depts VALUES('Europe', dnames_var('Shipping','Sales','Finance'));
   INSERT INTO depts VALUES('Americas', dnames_var('Sales','Finance','Shipping'));
   INSERT INTO depts 
     VALUES('Asia', dnames_var('Finance','Payroll','Shipping','Sales'));
   COMMIT;
END;
/
DECLARE
   new_dnames dnames_var := dnames_var('Benefits', 'Advertising', 'Contracting', 
                                       'Executive', 'Marketing');
   some_dnames dnames_var;
BEGIN
   UPDATE depts SET dept_names  = new_dnames WHERE region = 'Europe';
   COMMIT;
   SELECT dept_names INTO some_dnames FROM depts WHERE region = 'Europe';
   FOR i IN some_dnames.FIRST .. some_dnames.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE('dept_names = ' || some_dnames(i));
   END LOOP;
END;
/
DROP TABLE depts;
DROP TYPE dnames_var;

Collections as Atomic Data Items

The section "Constructors for Multilevel Collections" shows how to insert an entire multilevel collection with an INSERT statement. Multilevel collections (both VARRAY and nested tables) can also be updated atomically with an UPDATE statement. For example, suppose v_country is a variable declared to be of the countries nested table type nt_country_typ.

Example 5-26 updates region_tab by setting the countries collection as a unit to the value of v_country.

Example 5-26 Using UPDATE to Insert an Entire Multilevel Collection

-- Ex. 5-26 Using UPDATE to Insert an Entire Multilevel Collection
-- Requires Ex. 5-10 and 5-15
INSERT INTO region_tab (region_id, region_name) VALUES(2, 'Americas');

DECLARE
  v_country nt_country_typ;
BEGIN
  v_country :=  nt_country_typ( country_typ( 
   'US', 'United States of America', nt_location_typ (
   location_typ( 1500,'2011 Interiors Blvd','99236','San Francisco','California'),
   location_typ(1600,'2007 Zagora St','50090','South Brunswick','New Jersey'))));
  UPDATE region_tab r 
    SET r.countries = v_country WHERE r.region_id = 2;
END;
/
-- Invocation:
SELECT r.region_name, c.country_name, l.location_id 
  FROM region_tab r, TABLE(r.countries) c, TABLE(c.locations) l
     WHERE r.region_id = 2;

Using BULK COLLECT to Return Entire Result Sets

The PL/SQL BULK COLLECT clause is an alternative to using DML statements, which can be time consuming to process. You can return an entire result set in one operation.

In Example 5-27, BULK COLLECT is used with a multilevel collection that includes an object type.

comment 3-18, has been integrated

Example 5-27 Using BULK COLLECT with Collections

-- Ex. 5-27 Using BULK COLLECT with VARRAYs
-- unrelated to other examples in this chapter
CREATE TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30);
/
CREATE TABLE depts (region VARCHAR2(25), dept_names dnames_var);
BEGIN
   INSERT INTO depts VALUES('Europe', dnames_var('Shipping','Sales','Finance'));
   INSERT INTO depts VALUES('Americas', dnames_var('Sales','Finance','Shipping'));
   INSERT INTO depts 
     VALUES('Asia', dnames_var('Finance','Payroll','Shipping','Sales'));
   COMMIT;
END;
/
DECLARE
   TYPE dnames_tab IS TABLE OF dnames_var;
   v_depts dnames_tab;
BEGIN
    SELECT dept_names BULK COLLECT INTO v_depts FROM depts;
    DBMS_OUTPUT.PUT_LINE(v_depts.COUNT); -- prints 3
END;
/
DROP TABLE depts;
DROP TYPE dnames_var;

Conditions that Compare Nested Tables

The conditions listed in this section allow comparisons of nested tables, including multilevel nested tables. There is no mechanism for comparing varrays. The SQL examples in this section use the nested tables created in Example 5-5, and contain the objects created in Example 5-1.

Equal and Not Equal Comparisons

The equal (=) and not equal (<>) conditions determine whether the input nested tables are identical or not, returning the result as a Boolean value.

Two nested tables are equal if they have the same named type, have the same cardinality, and their elements are equal. Elements are equal depending on whether they are equal by the elements own equality definitions, except for object types which require a map method. Equality is determined in the existing order of the elements, because nested tables are unordered.

comment examples 5-28 and 5-29 below output is "no rows selected" because of the where clause.

Example 5-28 Using an Equality Comparison with Nested Tables

-- Ex. 5-28 Using an Equality Comparison with Nested Tables PROBLEM
-- Requires Ex. 5-1 and 5-5
SELECT p.name 
  FROM students, TABLE(physics_majors) p 
WHERE math_majors = physics_majors;

In Example 5-28, the nested tables contain person_typ objects, which have an associated map method. See Example 5-1. Since the two nested tables in the WHERE clause are not equal, no rows are selected.

IN Comparison

The IN condition checks whether or not a nested table is in a list of nested tables, returning the result as a Boolean value. NULL is returned if the nested table is a null nested table.

Example 5-29 Using an IN Comparison with Nested Tables

-- Ex. 5-29 Using an IN Comparison with Nested Tables PROBLEM
-- Requires Ex. 5-1 and 5-5
SELECT p.idno, p.name 
  FROM students, TABLE(physics_majors) p 
WHERE physics_majors IN (math_majors, chem_majors);

Subset of Multiset Comparison

The SUBMULTISET [OF] condition checks whether or not a nested table is a subset of another nested table, returning the result as a Boolean value. The OF keyword is optional and does not change the functionality of SUBMULTISET.

This condition is implemented only for nested tables.

Example 5-30 Testing the SUBMULTISET OF Condition on a Nested Table

-- Ex. 5-30 Testing the SUBMULTISET OF Condition on a Nested Table
-- Requires Ex. 5-1 and 5-5
SELECT p.idno, p.name 
  FROM students, TABLE(physics_majors) p 
WHERE physics_majors SUBMULTISET OF math_majors;

Member of a Nested Table Comparison

The MEMBER [OF] or NOT MEMBER [OF] condition tests whether or not an element is a member of a nested table, returning the result as a Boolean value. The OF keyword is optional and has no effect on the output.

In Example 5-31, the person_typ is an element of the same type as the elements of the nested table math_majors.

Example 5-31 Using MEMBER OF on a Nested Table

-- Ex. 5-31 Using MEMBER OF on a Nested Table
-- Requires Ex. 5-1 and 5-5
SELECT graduation 
  FROM students 
WHERE person_typ(12, 'Bob Jones', '1-650-555-0130') MEMBER OF math_majors;

Example 5-32 presents an alternative approach to the MEMBER OF condition, which performs more efficiently for large collections.

Example 5-32 Alternative to Using MEMBER OF on a Nested Table

-- Ex. 5-32 Alternative to Using MEMBER OF on a Nested Table
-- Requires Ex. 5-1 and 5-5
SELECT graduation
  FROM students
WHERE person_typ(12, 'Bob Jones', '1-650-555-0130') in (select value(p) 
    from TABLE( math_majors) p);

Empty Comparison

The IS [NOT] EMPTY condition checks whether a given nested table is empty or not empty, regardless of whether any of the elements are NULL. If a NULL is given for the nested table, the result is NULL. The result is returned as a Boolean value.

Example 5-33 Using IS NOT on a Nested Table

-- Ex. 5-33 Using IS NOT on a Nested Table
-- Requires Ex. 5-1 and 5-5
SELECT p.idno, p.name 
  FROM students, TABLE(physics_majors) p 
WHERE physics_majors IS NOT EMPTY;

Set Comparison

The IS [NOT] A SET condition checks whether or not a given nested table is composed of unique elements, returning a Boolean value.

Example 5-34 Using IS A SET on a Nested Table

-- Ex. 5-34 Using IS A SET on a Nested Table example
-- Requires Ex. 5-1 and 5-5
SELECT p.idno, p.name 
  FROM students, TABLE(physics_majors) p 
WHERE physics_majors IS A SET;

Multiset Operations for Nested Tables

This section describes multiset operators for nested tables. Multiset operations are not available for varrays.

The SQL examples in this section use the nested tables created in Example 5-5 and the objects created in Example 5-1.

See Also:

CARDINALITY

The CARDINALITY function returns the number of elements in a nested table. The return type is NUMBER. If the nested table is a null collection, NULL is returned.

Example 5-35 Determining the CARDINALITY of a Nested Table

-- Ex. 5-35 Determining the CARDINALITY of a Nested Table
-- Requires Ex. 5-1 and 5-5
SELECT CARDINALITY(math_majors) 
  FROM students;

For more information about the CARDINALITY function, see Oracle Database SQL Language Reference.

COLLECT

The COLLECT function is an aggregate function which creates a multiset from a set of elements. The function takes a column of the element type as input and creates a multiset from rows selected. To get the results of this function, you must use it within a CAST function to specify the output type of COLLECT. See "CAST" for an example of the COLLECT function.

For more information about the COLLECT function, see Oracle Database SQL Language Reference.

MULTISET EXCEPT

The MULTISET EXCEPT operator inputs two nested tables and returns a nested table whose elements are in the first nested table but not the second. The input nested tables and the output nested table will all be of the same nested table type.

The ALL or DISTINCT options can be used with the operator. The default is ALL.

  • With the ALL option, for ntab1 MULTISET EXCEPT ALL ntab2, all elements in ntab1 other than those in ntab2 are part of the result. If a particular element occurs m times in ntab1 and n times in ntab2, the result shows (m - n) occurrences of the element if m is greater than n, otherwise, 0 occurrences of the element.

  • With the DISTINCT option, any element that is present in ntab1 and is also present in ntab2 is eliminated, irrespective of the number of occurrences.

Example 5-36 Using the MULTISET EXCEPT Operation on Nested Tables

-- Ex. 5-36 Using the MULTISET EXCEPT Operation on Nested Tables
-- Requires Ex. 5-1 and 5-5
SELECT math_majors MULTISET EXCEPT physics_majors 
  FROM students 
WHERE graduation = '01-JUN-03';

For more information about the MULTISET EXCEPT operator, see Oracle Database SQL Language Reference.

MULTISET INTERSECT

The MULTISET INTERSECT operator returns a nested table whose values are common to the two input nested tables. The input nested tables and the output nested table are all type name equivalent.

There are two options associated with the operator: ALL or DISTINCT. The default is ALL. With the ALL option, if a particular value occurs m times in ntab1 and n times in ntab2, the result contains the element MIN(m, n) times. With the DISTINCT option, the duplicates from the result are eliminated, including duplicates of NULL values if they exist.

Example 5-37 Using the MULTISET INTERSECT Operation on Nested Tables

-- Ex. 5-37 Using the MULTISET INTERSECT Operation on Nested Tables 
-- Requires Ex. 5-1 and 5-5
SELECT math_majors MULTISET INTERSECT physics_majors 
  FROM students 
WHERE graduation = '01-JUN-03';

For more information about the MULTISET INTERSECT operator, see Oracle Database SQL Language Reference.

MULTISET UNION

The MULTISET UNION operator returns a nested table whose values are those of the two input nested tables. The input nested tables and the output nested table are all type name equivalent.

There are two options associated with the operator: ALL or DISTINCT. The default is ALL. With the ALL option, all elements in ntab1 and ntab2 are part of the result, including all copies of NULLs. If a particular element occurs m times in ntab1 and n times in ntab2, the result contains the element (m + n) times. With the DISTINCT option, the duplicates from the result are eliminated, including duplicates of NULL values if they exist.

Example 5-38 Using the MULTISET UNION Operation on Nested Tables

-- Ex. 5-38 Using the MULTISET UNION DISTINCT Operation on Nested Tables
-- Requires Ex. 5-1 and 5-5
SELECT math_majors MULTISET UNION DISTINCT physics_majors 
  FROM students 
WHERE graduation = '01-JUN-03';

PEOPLE_TYP(PERSON_TYP(12, 'Bob Jones', '1-650-555-0130'),
       PERSON_TYP(31, 'Sarah Chen', '1-415-555-0120'),
       PERSON_TYP(45, 'Chris Woods', '1-408-555-0128'))
-- Ex. 5-38a Using the MULTISET UNION ALL Operation on Nested Tables
-- Requires Ex. 5-1 and 5-5
SELECT math_majors MULTISET UNION ALL physics_majors
  FROM students 
WHERE graduation = '01-JUN-03';

PEOPLE_TYP(PERSON_TYP(12, 'Bob Jones', '1-650-555-0130'),
    PERSON_TYP(31, 'Sarah Chen', '1-415-555-0120'),
    PERSON_TYP(45, 'Chris Woods', '1-408-555-0128'),
    PERSON_TYP(12, 'Bob Jones', '1-650-555-0130'),
    PERSON_TYP(45, 'Chris Woods', '1-408-555-0128'))

For more information about the MULTISET UNION operator, see Oracle Database SQL Language Reference.

POWERMULTISET

The POWERMULTISET function generates all non-empty submultisets from a given multiset. The input to the POWERMULTISET function can be any expression which evaluates to a multiset. The limit on the cardinality of the multiset argument is 32.

Example 5-39 Using the POWERMULTISET Operation on Multiset

-- Ex. 5-39 Using the POWERMULTISET Operation on Multiset
-- Requires Ex. 5-1 and 5-5
SELECT * FROM TABLE(POWERMULTISET( people_typ (
           person_typ(12, 'Bob Jones', '1-650-555-0130'), 
           person_typ(31, 'Sarah Chen', '1-415-555-0120'), 
           person_typ(45, 'Chris Woods', '1-415-555-0124'))));

For more information about the POWERMULTISET function, see Oracle Database SQL Language Reference.

POWERMULTISET_BY_CARDINALITY

The POWERMULTISET_BY_CARDINALITY function returns all non-empty submultisets of a nested table of the specified cardinality. The output is rows of nested tables.

POWERMULTISET_BY_CARDINALITY(x, l) is equivalent to TABLE(POWERMULTISET(x)) p where CARDINALITY(value(p)) = l, where x is a multiset and l is the specified cardinality.

The first input parameter to the POWERMULTISET_BY_CARDINALITY can be any expression which evaluates to a nested table. The length parameter must be a positive integer, otherwise an error is returned. The limit on the cardinality of the nested table argument is 32.

Example 5-40 Using the POWERMULTISET_BY_CARDINALITY Function

-- Ex. 5-40 Using the POWERMULTISET_BY_CARDINALITY Function 
-- Requires Ex. 5-1 and 5-5
SELECT * FROM TABLE(POWERMULTISET_BY_CARDINALITY( people_typ (
           person_typ(12, 'Bob Jones', '1-650-555-0130'), 
           person_typ(31, 'Sarah Chen', '1-415-555-0120'), 
           person_typ(45, 'Chris Woods', '1-415-555-0124')),2));

For more information about the POWERMULTISET_BY_CARDINALITY function, see Oracle Database SQL Language Reference.

SET

The SET function converts a nested table into a set by eliminating duplicates, and returns a nested table whose elements are distinct from one another. The nested table returned is of the same named type as the input nested table.

Example 5-41 Using the SET Function on a Nested Table

-- Ex.5-41 Using the SET Function on a Nested Table 
-- Requires Ex. 5-1 and 5-5
SELECT SET(physics_majors) 
  FROM students 
WHERE graduation = '01-JUN-03';

For more information about the SET function, see Oracle Database SQL Language Reference.

-- clean up for example testing
DROP TABLE students;
DROP TABLE department_persons;
PURGE RECYCLEBIN;
DROP TYPE email_list_arr FORCE;
DROP TYPE people_typ FORCE;
DROP TYPE person_typ FORCE;
SPOOL OFF
COMMIT;

Partitioning Tables That Contain Oracle Objects

fyi...section moved fr chap 8

Partitioning addresses the key problem of supporting very large tables and indexes by allowing you to decompose them into smaller and more manageable pieces called partitions. Oracle extends partitioning capabilities by letting you partition tables that contain objects, REFs, varrays, and nested tables. Varrays stored in LOBs are equipartitioned in a way similar to LOBs. See also Oracle Database SecureFiles and Large Objects Developer's Guide.

feature 16545 below

Janis: discuss with Drew re: markers

With Oracle Database 11g release 1 (11.1), nested tables are equipartitioned, meaning that the partitioning of storage tables associated with nested tables corresponds to that of the top level base tables. The keyword LOCAL is the default and indicates this behavior. Previous releases stored unpartitioned nested tables with partitioned base tables. To obtain this behavior, specify the GLOBAL keyword. To partition your existing non-partitioned nested tables, see the discussion of online redefinition in Oracle Database VLDB and Partitioning Guide.

Generally, maintenance operations are carried out on the top level (or parent table) and cascade to the associated nested tables. However, you must perform the following operations directly on the nested table partition:

/ end 16545

See Also:

For further information on equipartitioning

Example 5-42 partitions the purchase order table along zip codes (ToZip), which is an attribute of the ShipToAddr embedded column object. The LineItemList_nt nested table illustrates storage for the partitioned nested table.

Example 5-42 Partitioning a Nested Table That Contains Objects

CREATE TYPE StockItem_objtyp AS OBJECT (
      StockNo NUMBER,
      Price NUMBER,
      TaxRate NUMBER);
/
 
CREATE TYPE LineItem_objtyp AS OBJECT (
      LineItemNo NUMBER,
      Stock_ref REF StockItem_objtyp,
      Quantity NUMBER,
      Discount NUMBER);
/
 
CREATE TYPE Address_objtyp AS OBJECT (
      Street VARCHAR2(200),
      City VARCHAR2(200),
      State CHAR(2),
      Zip VARCHAR2(20))
/
 
CREATE TYPE LineItemList_nt as table of LineItem_objtyp;
/
 
CREATE TYPE PurchaseOrder_ntyp AS OBJECT ( 
      PONo                NUMBER, 
      OrderDate           DATE, 
      ShipDate            DATE, 
      OrderForm           BLOB, 
      LineItemList        LineItemList_nt, 
      ShipToAddr          Address_objtyp, 
 
   MAP MEMBER FUNCTION 
      ret_value RETURN NUMBER, 
   MEMBER FUNCTION 
      total_value RETURN NUMBER);
/
 
CREATE TABLE PurchaseOrders_ntab of PurchaseOrder_ntyp  
LOB (OrderForm) store as (nocache logging)  
NESTED TABLE LineItemList STORE AS LineItemList_ntab
PARTITION BY RANGE (ShipToAddr.zip)  
   (PARTITION PurOrderZone1_part VALUES LESS THAN ('59999')  
       LOB (OrderForm) store as (  
       storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100))  
       NESTED TABLE LineItemList store as LineitemZone1_part(  
       storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100)),  
   PARTITION PurOrderZone2_part  VALUES LESS THAN ('79999')  
       LOB (OrderForm) store as (  
       storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100))  
      NESTED TABLE LineItemList store as LineitemZone2_part( 
       storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100)),  
   PARTITION PurOrderZone3_part  VALUES LESS THAN ('99999')  
       LOB (OrderForm) store as (  
       storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100))  
       NESTED TABLE LineItemList store as LineitemZone3_part(  
   storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100)) )
/