Oracle Object Tables
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose Oracle defines two basic table types: Relational and Object. This page focuses on Object Tables also referred to as Nested Tables. Object Tables based upon VARRAYS can be found in the Library page on VARRAYs linked at page bottom. A nested table is a table stored within the structure of another table.

Object tables are tables based upon user defined data types so in all cases, in the demos below, you will see tables based on object types defined by Oracle as part of the database deliverable or created by the developer or DBA.
Dependencies
ALL_NESTED_TABLES CDB_TABLES DBA_TYPES
ALL_NESTED_TABLE_COLS CDB_TAB_COLS TAB$
ALL_SOURCE CDB_TYPES TYPE$
ALL_TABLES COLLECTION$ USER_NESTED_TABLES
ALL_TAB_COLS DBA_NESTED_TABLES USER_NESTED_TABLE_COLS
ALL_TYPES DBA_NESTED_TABLE_COLS USER_SOURCE
CDB_NESTED_TABLES DBA_SOURCE USER_TABLES
CDB_NESTED_TABLE_COLS DBA_TABLES USER_TAB_COLS
CDB_SOURCE DBA_TAB_COLS USER_TYPES
System Privileges
CREATE ANY TABLE CREATE TABLE DROP ANY TABLE
CREATE ANY TYPE CREATE TYPE DROP ANY TYPE
 
Create Non-Editionable Type
Defining a Type for an Object Table -- it is critically important that types used to create segments are non-editionable as show here
CREATE OR REPLACE NONEDITIONABLE TYPE CourseList AS TABLE OF VARCHAR2(64);
/

desc courselist

col text format a50

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

col typecode format a15

SELECT type_name, typecode, attributes, incomplete, final, instantiable
FROM user_types;

SELECT object_name, editionable
FROM user_objects
WHERE editionable IS NOT NULL;
 
Object Table DDL
Create Nested Table  
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, table_type
FROM user_all_tables;

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

col table_type_owner format a30
col table_type_name format a15
col parent_table_column format a10

SELECT table_name, table_type_owner, table_type_name, parent_table_column
FROM user_nested_tables;
Deleting a Nested Table Column  
SELECT table_name, nested, table_type
FROM user_all_tables;

DROP TABLE courses_tab;

-- you cannot directly drop the storage table of a nested table you must drop the column itself

desc department

ALTER TABLE department
DROP COLUMN courses;
 
Nested Table DML
Insert into a Nested Table INSERT INTO <[schema_name.] table_name> <column_list>
VALUES (<values, <table_type_name(<value>,<value>,...));
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;
Updates in a Nested Table DECLARE
 <variable_name> <table_type_name>= <table_type_name>(<value>, <value>, ...);
BEGIN
  UPDATE [schema_name.] table_name
  SET <column_name> = <variable_name>
  WHERE <filter_condition>
END;
/
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;
Deletes in a Nested Table  
DECLARE
 new_courses CourseList := CourseList ('Realism and Naturalism', 'Introduction to Shakespeare', 'Advanced Workshop in Poetry');
BEGIN
   UPDATE department
   SET courses = new_courses
   WHERE name = 'English';
END;
/

SELECT * FROM department;

SELECT cardinality(courses)
FROM department;
Collection Unnesting Demo -- to select data from a nested table column unnest it with the TABLE function to treat the nested table as columns of a table.

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;
 
Object Table Demos
Constructor for a Nested Table -- In the following example, 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;
/

Related Topics
Collections
Instead-Of Triggers
Object Table Constraints
REF Cursors
Relational Tables
Types
VArrays

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved