Oracle Collection - Nested tables

Nested tables

         Nested tables hold an arbitrary number of elements and use sequential numbers as the index or key to the elements. Nested tables can be stored in database tables and manipulated through SQL. They are appropriate for data relationships that must be stored persistently. Nested tables are flexible in that arbitrary elements can be deleted, rather than just removing an element from the end. Note that the order and subscripts (keys) of nested tables are not preserved as the table is stored and retrieved in the database

   Nested tables can be stored in a database column.Nested tables are initially dense, but they can become sparse (Data does not have to be stored in consecutive rows) when elements are deleted. Nested Table is available in PL/SQL as well as in SQL.

Nested tables are dense, but they can become sparse (have nonconsecutive subscripts). So, you can delete elements from a nested table using the built-in procedure DELETE. That might leave gaps in the index, but the built-in function NEXT lets you iterate over any series of subscripts

CREATE OR REPLACE TYPE nestedtable_type AS TABLE OF element_type; (PLSQL Type in SQL)

CREATE TABLE nested_table (id NUMBER, col1 nestedtable_type) NESTED TABLE col1 STORE AS col1_tab;   (Nested Table)

TYPE nestedtable_type IS TABLE OF element_type; (PLSQL Type in PLSQL)