Mega Code Archive
Nested Tables
<![CDATA[
A nested table is an unordered set of any number of elements, all of the same data type.
A nested table has a single column.
The type of that column may be a built-in database type or an object type.
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.
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>
]]>