In Oracle, a nested table can be stored as a database column. This means that the entire nested table is contained in one row of the database table, and each row of the database can contain a different nested table. To store a nested table in the database, you must use the
CREATE TYPE statement to creates the nested table type, rather than a type statement in a PL/SQL block.
CREATE TYPE, the type is stored in the data dictionary and is thus available for use as a column type. The following example illustrates how to create a nested table as a database column.
Oracle PL/SQL – Creating Nested Table in the Database
CREATE TYPE BookObj AS OBJECT ( title VARCHAR2(40), author VARCHAR2(40), catalog_number NUMBER(4) ); CREATE TYPE BookList AS TABLE OF BookObj; CREATE TABLE course_material ( department CHAR(3), course NUMBER(3), required_reading BookList ) NESTED TABLE required_reading STORE AS required_tab;
There are several things to note about the above listing and creating nested tables in the database:
- The table type is designed with the
CREATE TYPEstatement so it can be stored in the data dictionary.
- The table type is used in the table definition, just like a column object.
- For each nested table in a given database table, the
NESTED TABLEclause is required. This clause indicates the name of the store table.
A store table is a system-generated table that is used to store the actual data in the nested table. This data is not stored inline with the rest of the table columns; it is stored separately.
required_reading column will store a
REF into the
required_tab table, where the list of books will be stored. For each row of
course_material, required_reading contains a REF to the corresponding rows in required_tab.
The store table (
required_tabin the above example) can exist in another schema and can have different storage parameters from the main table. The store table can be described, and exists in
user_tables, but can not be accessed directly.
If you attempt to query or modify the store table directly, you will get the Oracle error “ORA-22812: cannot reference nested table column’s storage table”. The contents of the store table are manipulated through SQL on the main table.