Oracle Nested Tables

Oracle Nested Tables is a SQL data type in the Oracle Specific category. Oracle: nested table types allow storing collections of values within a column, like PostgreSQL ARRAY. The syntax is CREATE TYPE type_name AS TABLE OF datatype;. It returns nested table. A typical example: CREATE TYPE phone_list AS TABLE OF VARCHAR2(20); / CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR2(100), phones phone_list ) NESTED TABLE phones STORE AS phones_tab; INSERT INTO employees VALUES ( 1, 'Alice', phone_list('555-0101', '555-0102') ); -- Query nested table: SELECT e.name, p.column_value AS phone FROM employees e, TABLE(e.phones) p; -- Alice | 555-0101 -- Alice | 555-0102 -- TABLE() expression expands the nested table into rows -- Similar… A close relative is SEQUENCE (Oracle), which oracle: generates sequential numbers. Independent of tables. Accessed via NEXTVAL and CURRVAL. A close relative is ROWNUM / FETCH FIRST, which oracle: ROWNUM assigns sequential numbers to rows. Used for limiting results (pre-12c).