ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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
courselist TABLE OF VARCHAR2(64)
col text format a50
SELECT type, text
FROM user_source
WHERE name = 'COURSELIST';
TYPE TEXT
------------ ------------------------------------------
TYPE TYPE CourseList AS TABLE OF VARCHAR2(64);
col typecode format a15
SELECT type_name, typecode, attributes, incomplete, final, instantiable
FROM user_types;
TYPE_NAME TYPECODE ATTRIBUTES INC FIN INS
---------------- ----------- ---------- --- --- ---
COURSELIST COLLECTION 0 NO YES YES
SELECT object_name, editionable
FROM user_objects
WHERE editionable IS NOT NULL
ORDER BY 1;
OBJECT_NAME E
------------------------------ -
ARRAY_LOAD Y
BLENDED Y
C2T_DEMO Y
CC_VIEW Y
COURSELIST N
DBSECWORX Y
LOAD_C2T_TEST Y
LOAD_SOURCES_IMPORT Y
PROFRESET Y
_DBA_REPL_COMPONENT Y
_DBA_REPL_COMPONENT_LINK Y
_DBA_REPL_COMPONENT_PROP Y
_DBA_REPL_COMPONENT_STAT Y
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
Name Null? Type
--------------- -------- -------------
NAME VARCHAR2(20)
DIRECTOR VARCHAR2(20)
OFFICE VARCHAR2(20)
COURSES COURSELIST
desc courses_tab
Name Null? Type
--------------- -------- -------------
COLUMN_VALUE VARCHAR2(64)
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';
COLUMN_NAME DATA_TYPE DATA_LENGTH
------------------ ----------- -----------
COURSES COURSELIST 16
NAME VARCHAR2 20
DIRECTOR VARCHAR2 20
OFFICE VARCHAR2 20
SYS_NC0000400005$ RAW 16
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;
SELECT table_name, nested, table_type
FROM user_all_tables;
DROP TABLE courses_tab;
*
ERROR at line 1:
ORA-22914: DROP of nested tables not supported
-- 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;
Table altered.
Nested Table DML
Insert into a Nested Table
INSERT INTO <[schema_name.] table_name> <column_list>
VALUES (<values, <table_type_name(<value>,<value>,...));
-- before this insert recreate the DEPARTMENT table with the COURSES column
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'));
1 row created.
SELECT * FROM department;
NAME DIRECTOR OFFICE
-------- -------------- --------------------
English Lynn Saunders Breakstone Hall 205
COURSES
---------------------------------------------------------------------------------------
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 cardinality(courses)
FROM department;
CARDINALITY(COURSES)
--------------------
9
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;
/
PL/SQL procedure successfully completed.
SELECT * FROM department;
NAME DIRECTOR OFFICE
-------- -------------- --------------------
English Lynn Saunders Breakstone Hall 205
COURSES
---------------------------------------------------------------------------------------
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', '20th-Century Poetry', 'Advanced Workshop in Poetry')
SELECT cardinality(courses)
FROM department;
CARDINALITY(COURSES)
--------------------
11
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;
/
