Nested Tables : Nested Tables « Collections « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Collections
3. Nested Tables

1. A nested table is an unordered set of any number of elements, all of the same data type.
2. A nested table has a single column.
3. The type of that column may be a built-in database type or an object type.
4. If the column in a nested table is an object type, the table can also be viewed as a multicolumn table, with a column for each attribute of the object type.
5. You can insert, update, and delete individual elements in a nested table.

declare
  type <NestedTable> is table of <ElementType>;
...

create or replace type <NestedTable> is table of <ElementType>;

SQL>
SQL> CREATE OR REPLACE PACKAGE aa_types
  2  IS
  3     TYPE boolean_aat IS TABLE OF BOOLEAN
  4      INDEX BY BINARY_INTEGER;
  5
  6     TYPE date_aat IS TABLE OF DATE
  7      INDEX BY BINARY_INTEGER;
  8
  9     TYPE integer_aat IS TABLE OF INTEGER
 10      INDEX BY BINARY_INTEGER;
 11
 12     TYPE pls_integer_aat IS TABLE OF PLS_INTEGER
 13      INDEX BY BINARY_INTEGER;
 14
 15     TYPE binary_integer_aat IS TABLE OF BINARY_INTEGER
 16      INDEX BY BINARY_INTEGER;
 17
 18     TYPE natural_aat IS TABLE OF NATURAL
 19      INDEX BY BINARY_INTEGER;
 20
 21     TYPE positive_aat IS TABLE OF POSITIVE
 22      INDEX BY BINARY_INTEGER;
 23
 24     TYPE number_aat IS TABLE OF NUMBER
 25      INDEX BY BINARY_INTEGER;
 26
 27     TYPE maxdbvarchar2_aat IS TABLE OF VARCHAR2(4000)
 28      INDEX BY BINARY_INTEGER;
 29
 30     TYPE maxvarchar2_aat IS TABLE OF VARCHAR2(32767)
 31      INDEX BY BINARY_INTEGER;
 32
 33     TYPE identifier_aat IS TABLE OF VARCHAR2(30)
 34      INDEX BY BINARY_INTEGER;
 35
 36     TYPE utl_file_aat IS TABLE OF UTL_FILE.FILE_TYPE
 37      INDEX BY BINARY_INTEGER;
 38
 39     TYPE xml_aat IS TABLE OF XML_TYPE
 40      INDEX BY BINARY_INTEGER;
 41
 42     TYPE clob_aat IS TABLE OF CLOB
 43      INDEX BY BINARY_INTEGER;
 44
 45     TYPE blob_aat IS TABLE OF BLOB
 46      INDEX BY BINARY_INTEGER;
 47  END aa_types;
 48  /

Warning: Package created with compilation errors.

SQL>
SQL> show errors
Errors for PACKAGE AA_TYPES:

LINE/COL ERROR
-------- -----------------------------------------------------------------
36/4     PL/SQL: Declaration ignored
36/34    PLS-00201: identifier 'UTL_FILE' must be declared
39/4     PL/SQL: Declaration ignored
39/29    PLS-00201: identifier 'XML_TYPE' must be declared
SQL>