Skip to Main Content
Oracle in a Nutshell
book

Oracle in a Nutshell

by Rick Greenwald, David C. Kreines
December 2002
Intermediate to advanced content levelIntermediate to advanced
928 pages
85h 29m
English
O'Reilly Media, Inc.
Content preview from Oracle in a Nutshell

Collection functions and methods

You can manipulate collections with the following functions:

CAST

Maps a collection of one type to a collection of another type.

SELECT column_value
FROM TABLE(SELECT CAST(colors AS color_tab_t)
           FROM color_models_a
          WHERE model_type ='RGB');
MULTISET

Maps a database table to a collection. With MULTISET and CAST, you can retrieve rows from a database table as a collection-typed column. You can use CAST and MULTISET to extract nested columns for use in trigger logic (described later in the chapter.)

SELECT b.genus ,b.species,
      CAST(MULTISET(SELECT bh.country
                      FROM bird_habitats bh
                     WHERE bh.genus = b.genus
                       AND bh.species = b.species)
          AS country_tab_t)
FROM birds b;
TABLE

Maps a collection to a database table; the inverse of MULTISET.

SELECT *
  FROM color_models c
 WHERE 'RED' IN (SELECT * FROM TABLE(c.colors));

You can use TABLE( ) to unnest a transient collection:

DECLARE
   birthdays Birthdate_t :=
      Birthdate_t('24-SEP-1984', '19-JUN-1993');
BEGIN
   FOR the_rec IN
      (SELECT COLUMN_VALUE
         FROM TABLE(CAST(birthdays AS Birthdate_t)))

Collections also support a set of methods, described in the “Collection Datatypes” section in Appendix A. These methods are:

COUNT
DELETE
EXISTS
EXTEND
FIRST
LAST
LIMIT
PRIOR
NEXT
TRIM

The syntax for using these methods is:

                     collection_name.method_name[(parameters)]
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle Internals

Oracle Internals

Donald K. Burleson
Oracle PL/SQL

Oracle PL/SQL

Lewis Cunningham
Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

Rick Greenwald, Robert Stackowiak, Jonathan Stern

Publisher Resources

ISBN: 0596003366Errata Page