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

Collections

A collection is a composite data structure that acts like a list or a single-dimensional array (traditional arrays are not supported in PL/SQL).

There are three different types of collections in PL/SQL: associative arrays (called index-by tables in Oracle8 and Oracle8i and PL/SQL tables before that), nested tables, and VARRAYs (variable arrays). The following sections briefly describe the declarations for these different types, but for information about using and comparing these collection types, see Section 9.4.11 under Section 9.4 later in this chapter. Table 9-2 compares the three collection types.

Collections are implemented as TYPEs. Like any programmer-defined type, you must first define the type; then you can declare instances of that type. The TYPE definition can be stored in the database or declared in the PL/SQL program. Each instance of the TYPE is a collection.

Table 9-2. Comparison of collection types

Characteristic

Associative array

Nested table

VARRAY

Dimensionality

Single

Single

Single

Usable in SQL?

No

Yes

Yes

Usable as a column datatype in a table?

No

Yes; data stored “out of line” (in a separate table)

Yes; data typically stored “in line” (in the same table)

Uninitialized state

Empty (cannot be NULL); elements are undefined

Atomically null; illegal to reference elements

Atomically null; illegal to reference elements

Initialization

Automatic, when declared

Via constructor, fetch, assignment

Via constructor, fetch, assignment ...

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