Nested Tables:

A nested table is such as an object table without the object identifiers. This table has a single column and the type of that column is a built-in type or an object type.  This table can also be viewed as a multi-column table if it is an object type, with a column for every attribute of the object type.

A nested table definition does not allocate space. It defines a type that you can use

• The datatype is a column of a relational table.
• An object type attributes.

When a nested table appears as the type of a column in a relational table or as an attribute of the underlying object type of an object table, An Oracle stores all of the nested table data in a single table that it related with the enclosing relational or object table. They are also known as Multiple Row Object Type. They have no restrictions on the number of records to be created.

Example

The given example creates a nested table and embeds the type with the relational table.

MULTIPLE ROW OBJECT TYPE (NESTED TABLE)

CREATE OR REPLACE TYPE stud_type AS

OBJECT (

studno             number,

studname         char(20),

sex       char(1)

) ;

CREATE OR  REPLACE TYPE stud_nt AS           TABLE OF stud_type;

CREATE TABLE faculty

(

factno              number,

name    varchar2(30),

students           STUD_NT

) NESTED TABLE students STORE AS stud_nt_tab ;

Here, NESTED TABLE nested_item STORE AS storage_table

specifies storage_table as the name of the storage table in that the rows of all nested_item values reside. That clause must be involved when creating a table with columns or column attributes whose categorized is a nested table. A nested_item is the name of a column or a column- qualified attribute whose type is a nested table.

The storage_table is the name of the storage table. A storage table is created in the similar schema and the similar tablespace as the parent table.

For creating a table with columns of type TABLE implicitly creates a storage table for every nested table column. A storage table is created in the similar tablespace as its parent table (by using the default storage characteristics) and stores the nested table values of the column for that it is created.  You cannot query or perform DML statements on the storage table straightly, but you can change the nested table column storage characteristics through using the name of storage table in an ALTER TABLE statement.

INSERT                      INTO  faculty VALUES

(                       1,         'Ramesh' ,

stud_nt (

stud_type( 10, 'Rajesh',           'M' ) ,

stud_type( 11, 'Suresh',           'M' ),

stud_type( 12, 'Uma',  'F'        )

)

) ;

Insertion  into  the  table  is  done  using  the  abstract  data  type  called  stud_type.  As same multiple records can be inserted into the table.

Although querying, individual columns can be selected. If,

SELECT * FROM faculty is issued, the display will be,

FACTNO NAME

--------- ------------------------------

STUDENTS (STUDNO, STUDNAME, SEX)

----------------------------------------

1 Ramesh

STUD_NT(STUD_TYPE(10,'Rajesh','M'),STUD_TYPE(11,'Suresh','M'),ST

_TYPE (12,'Uma','F'))

Similarly the multiple records can be inserted.