CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Nested Tables
Version 11.1
 
General Information
Note: A nested table is a table stored within the structure of another table.
Data Dictionary Objects
collection$ tab$ type$
dba_nested_tables all_nested_tables user_nested_tables
dba_nested_table_cols all_nested_table_cols user_nested_table_cols
dba_source all_source user_source
dba_tables all_tables user_tables
dba_tab_cols all_tab_cols user_tab_cols
dba_types all_types user_types
System Privileges
CREATE ANY TABLE CREATE TABLE DROP ANY TABLE
CREATE ANY TYPE CREATE TYPE DROP ANY TYPE
 

Nested Table Example
CREATE OR REPLACE TYPE CourseList AS TABLE OF VARCHAR2(64);
/

desc courselist

col text format a50

SELECT type, text
FROM user_source
WHERE name = 'COURSELIST';

CREATE TABLE department (
name     VARCHAR2(20),
director VARCHAR2(20),
office   VARCHAR2(20),
courses  CourseList)
NESTED TABLE courses STORE AS courses_tab;

desc department

desc courses_tab

SELECT table_name, nested
FROM user_tables;

set linesize 121
col table_name format a20
col data_type format a30
col table_type_name format a15
col parent_table_column format a10

SELECT column_name, data_type, data_length
FROM user_tab_cols
WHERE table_name = 'DEPARTMENT';

SELECT table_name, table_type_owner, table_type_name,
parent_table_column
FROM user_nested_tables;

Insert into Nested Table
SELECT cardinality(courses)
FROM department;

INSERT INTO department
(name, director, office, courses)
VALUES
('English', 'Lynn Saunders', 'Breakstone Hall 205', CourseList(
'Expository Writing',
'Film and Literature',
'Modern Science Fiction',
'Discursive Writing',
'Modern English Grammar',
'Introduction to Shakespeare',
'Modern Drama',
'The Short Story',
'The American Novel')
);

SELECT * FROM department;

SELECT cardinality(courses)
FROM department;

Update a nested table
DECLARE
   new_courses CourseList :=
   CourseList('Expository Writing',
   'Film and Literature',
   'Discursive Writing',
   'Modern English Grammar',
   'Realism and Naturalism',
   'Introduction to Shakespeare',
   'Modern Drama',
   'The Short Story',
   'The American Novel',
   '20th-Century Poetry',
   'Advanced Workshop in Poetry')
;
BEGIN
   UPDATE department
   SET courses = new_courses
   WHERE name = 'English';
END;
/

SELECT * FROM department;

SELECT cardinality(courses)
FROM department;

Drop nested table
SELECT table_name
FROM user_tables;

DROP TABLE courses_tab;

You cannot directly drop the storage table of a nested table. Instead, you must drop the nested table column using the ALTER TABLE ... DROP COLUMN clause.

desc department

ALTER TABLE department
DROP COLUMN courses;
 
Table Unnesting

Collection Unnesting Demo
To select data from a nested table column you use the TABLE function to treat the nested table as columns of a table. This process is called "collection unnesting".

SELECT t1.department_id, t2.*
FROM hr_info t1, TABLE(t1.people) t2
WHERE t2.department_id = t1.department_id;

SELECT t1.name, t2.*
FROM department t1, TABLE(t1.courses) t2;
 
Nested Table Demos

Constructor for a Nested Table
In the following example, you pass multiple elements to the constructor CourseList(), which returns a nested table  containing those elements:

DECLARE

TYPE CourseList IS TABLE OF VARCHAR2(16);
my_courses CourseList;

BEGIN
my_courses := CourseList('Econ 2010','Acct 3401','Mgmt 3100');
END;
/

Because a PL/SQL table does not have a declared maximum size, you can put as many elements in the constructor as necessary.

DECLARE

TYPE CourseList IS TABLE OF VARCHAR2(16);
my_courses CourseList;

BEGIN
  my_courses := CourseList('Econ 2010','Acct 3401','Mgmt 3100');
  my_courses := CourseList('Math 2022','Acct 3431','Mgmt 3100');
  my_courses := CourseList('Phys 2299','Chem 9876');
  my_courses := CourseList('Food 9999');
  my_courses := CourseList('Orcl 3456','Math 3434','Hist 1040');
END;
/
Defining a Type as a database object CREATE OR REPLACE TYPE CourseList AS TABLE OF VARCHAR2(64);
/
Defining Types in a package header CREATE OR REPLACE PACKAGE xyz IS
 TYPE CourseList IS TABLE OF VARCHAR2(64);

 TYPE PartNum IS TABLE OF parent.part_num%TYPE
 INDEX BY BINARY_INTEGER;
END xyz;
/
 
Related Topics
Collections
Instead-Of Triggers
Nested Table Constraints
REF Cursors
Tables
Types
Varrays
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [78 users online]    © 2010 psoug.org