ORACLE : NESTED TABLES

NESTED TABLES in ORACLE
Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.
Within the database, nested tables can be considered one-column database tables. Oracle stores the rows of a nested table in no particular order. But, when you retrieve the nested table into a PL/SQL variable, the rows are given consecutive subscripts starting at 1. That gives you array-like access to individual rows.
PL/SQL nested tables are like one-dimensional arrays. You can model multi-dimensional arrays by creating nested tables whose elements are also nested tables.
Nested tables differ from arrays in two important ways:
1. Arrays have a fixed upper bound, but nested tables are unbounded. So, the size of a nested table can increase dynamically
2. Arrays must be dense (have consecutive subscripts). So, you cannot delete individual elements from an array. Initially, 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.

Note:
Nested tables can be stored in a database column, so Nested tables are appropriate for important data relationships that must be stored persistently.

Example scripts

----- Creating Nested Tables type ----------------------
---- Unlimited rows of varchar2(25) column.
CREATE OR REPLACE
TYPE TYPE_NESTED_TABLES IS TABLE OF VARCHAR2(25);
/
---------------------------------------------------------

-------- Creating table with nested table types ---------
CREATE TABLE TBL_WITH_NESTEDTABLES
(
ID NUMBER
, ADDEDDATE DATE
, DATA TYPE_NESTED_TABLES
) NESTED TABLE DATA STORE AS NESTED_TABLES_TAB
/
---------------------------------------------------------

DESC TBL_WITH_NESTEDTABLES
DESC NESTED_TABLES_TAB

SELECT * FROM USER_NESTED_TABLES
SELECT * FROM USER_TAB_COLS WHERE TABLE_NAME = 'TBL_WITH_NESTEDTABLES'
SELECT * FROM USER_TYPES


INSERT INTO TBL_WITH_NESTEDTABLES
(
ID
, ADDEDDATE
, DATA
) VALUES
(
1
, SYSDATE
, TYPE_NESTED_TABLES('A','B','C')
);
/

INSERT INTO TBL_WITH_NESTEDTABLES
(
ID
, ADDEDDATE
, DATA
) VALUES
(
2
, SYSDATE
, TYPE_NESTED_TABLES('1','2','3','4','5')
);
/
INSERT INTO TBL_WITH_NESTEDTABLES
(
ID
, ADDEDDATE
, DATA
) VALUES
(
3
, SYSDATE
, TYPE_NESTED_TABLES('AA','BB','CC','DD','EE','FF')
);
/

SELECT * FROM TBL_WITH_NESTEDTABLES

SET SERVEROUTPUT ON;

DECLARE
TMP_TYPE TYPE_NESTED_TABLES;
BEGIN
SELECT DATA INTO TMP_TYPE FROM TBL_WITH_NESTEDTABLES WHERE ID = 1;
DBMS_OUTPUT.PUT_LINE('NO.OF RECORDS : ' || TMP_TYPE.COUNT);
FOR CNT IN TMP_TYPE.FIRST..TMP_TYPE.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(TMP_TYPE(CNT));
END LOOP;
END;
/

DECLARE
TMP_TYPE TYPE_NESTED_TABLES := TYPE_NESTED_TABLES('TN','AP','KA','KE','UP');
BEGIN
UPDATE TBL_WITH_NESTEDTABLES SET DATA = TMP_TYPE WHERE ID = 1;
END;
/

SELECT
T1.ID
, T1.ADDEDDATE
, T2.*
FROM
TBL_WITH_NESTEDTABLES T1,
TABLE(T1.DATA) T2

For More information