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.
By using 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 TYPE
statement 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 TABLE
clause 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.
The 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.
NOTE
The store table (
required_tab
in 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 inuser_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.
See also: