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 Collections & Table Expression
Version 11.1
 
General Information
A nested table is a table stored within the structure of another table.

You can perform DML operations on nested tables only if they are defined as columns of a table. Therefore, when the query_table_expression_clause of an INSERT, DELETE, or UPDATE statement is a table_collection_expression, the collection expression must be a subquery that uses the TABLE function to select the table's nested table column.
Data Dictionary Objects
collection$ type$ dba_types all_types user_types
System Privileges
ALTER ANY TYPE CREATE TABLE DROP ANY TABLE
CREATE TYPE CREATE ANY TABLE EXECUTE ANY TYPE
CREATE ANY TYPE DROP ANY TYPE UNDER ANY TYPE
 
Creating A Collection Based On An Object

Creation of a User Defined Object Data Type and Collection
CREATE OR REPLACE TYPE <type_name> AS TABLE OF <user_defined_data_type>;
/
CREATE OR REPLACE TYPE phone_t AS OBJECT (
a_code   CHAR(3),
p_number CHAR(12));
/

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

CREATE OR REPLACE TYPE
phone_tab_t AS TABLE OF phone_t;
/

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

col coll_type format a10

SELECT type_name, coll_type, elem_type_owner, elem_type_name
FROM user_coll_types;

Heap Table Of A User Defined Data Object Type
CREATE TABLE <table_name>
column_name <user_defined_data_type);
CREATE TABLE test (
person_id   NUMBER(10),
per_phones  phone_t);

desc test

set describe depth all

desc test

set describe depth 1

col data_type format a40

SELECT column_name, data_type
FROM user_tab_cols
WHERE table_name = 'TEST';

Heap Table Of With A  User Defined Data Object Type As A Nested Table
CREATE TABLE <table_name> (
column_name   <user_defined_data_type>)
NESTED TABLE <column_name> STORE AS <name_of_nested_table>;
CREATE TABLE person (
person_id     NUMBER(10),
first_name    VARCHAR2(10),
last_name     VARCHAR2(10),
person_phones phone_tab_t,
active_flag   VARCHAR2(1))
NESTED TABLE person_phones STORE AS phones_tab
TABLESPACE uwdata;

desc person

SELECT table_name
FROM user_tables;

select table_name, table_type, table_type_owner, nested
FROM user_all_tables;

col parent_table_column format a25

SELECT table_name, table_type_name, parent_table_column
FROM user_nested_tables;
 
Inserting Into A Collection Based On An Object

Insert Into Parent And Nested Table
INSERT INTO <table_name>
(<column_name_list>)
VALUES
(<list_of_column_values>, <table_type_constructor>(<type_constructor(<type_values>)));
INSERT INTO person
(person_id, first_name, last_name, person_phones, active_flag)
VALUES
(0, 'Test', 'Tester', phone_tab_t(), 'I');

INSERT INTO person
(person_id, first_name, last_name, person_phones, active_flag)
VALUES
(1, 'Dan', 'Morgan', phone_tab_t(phone_t('C', '206-555-1212')), 'A');

INSERT INTO person
(person_id, first_name, last_name, person_phones)
VALUES
(2, 'Helen', 'Lofstrom', phone_tab_t(phone_t('C', '425-555-1212')));

col person_phones format a50

SELECT * FROM person;

Insert Into Nested Table
INSERT INTO TABLE (
SELECT <nested_table_column_name>
FROM <table_name>
WHERE <column_name> <condition <value>)
VALUES
(<column_value>, <column_value>);
INSERT INTO TABLE(
SELECT person_phones
FROM person
WHERE person_id = 1)

VALUES
('H','206-987-6543');
 
Selecting From A Collection Based On An Object

Select From Nested Table
SELECT <column_name_list> FROM <table_name>;
SELECT * FROM person;

Select From Nested Table With Table Function
SELECT <column_name_list, <tested_table_column_list>
FROM <table> <alias>, TABLE (<alias.nested_table_name> <alias>;
SELECT person_id, last_name, t2.*
FROM person t1, TABLE(t1.person_phones) t2;
Note: Be sure to use the link at page bottom to explore COLLECTION FUNCTIONS
 
Updating A Collection Based On An Object

Update All Nested Table Rows
UPDATE person
SET person_phones =   <table_constructor>(<type_constructor(<column_values>));
SELECT person_id, last_name, t2.*
FROM person t1, TABLE(t1.person_phones) t2;

UPDATE person
SET person_phones = phone_tab_t(phone_t('H', '360-555-1212'))
WHERE person_id = 2;

SELECT person_id, last_name, t2.*
FROM person t1, TABLE(t1.person_phones) t2;

Update Selected Nested Table Rows
UPDATE TABLE (
<nested_select_statment>) <alias>
SET <nested_column> = <value>
WHERE <alias.nested_column_name> <condition> <value>;
UPDATE TABLE (
SELECT person_phones
FROM person
WHERE person_id = 1) p
SET p.a_code = 'W'
WHERE p.p_number = '206-987-6543';

SELECT t1.person_id, last_name, t2.*
FROM person t1, TABLE(t1.person_phones) t2;
 
Deleting From A Collection Based On An Object

Delete From Nested Table
DELETE TABLE (
<nested_select_statment>) <alias>
WHERE <alias.nested_column_name> <condition> <value>;
DELETE TABLE (
SELECT person_phones
FROM person
WHERE person_id = 1) p
WHERE p.p_number = '206-987-6543';

SELECT t1.person_id, last_name, t2.*
FROM person t1, TABLE(t1.person_phones) t2;
 
Creating A Collection Based On A VARRAY

Creation of a User Defined Object Data Type
CREATE OR REPLACE TYPE <type_name> AS OBJECT (
<column_name> <data_type>);
/
CREATE OR REPLACE TYPE title_t AS OBJECT (
title_name VARCHAR2(5));
/

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

VARRAY Creation
CREATE TYPE TitleList AS VARRAY(<integer>) OF <data_type>;
/
CREATE TYPE TitleList AS VARRAY(3) OF title_t;
/

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

SELECT parent_table_name, parent_table_column, type_owner, type_name
FROM user_varrays;
-- note there is no record

Create Heap Table With Nested Varray and Collection
CREATE TABLE <table_name> (
column_name  varray_name);
DROP TABLE person PURGE;

CREATE TABLE person (
person_id    NUMBER(10),
first_name   VARCHAR2(25),
last_name    VARCHAR2(25),
person_title TitleList,
active_flag  VARCHAR2(1))
NESTED TABLE person_title STORE AS title_tab
TABLESPACE uwdata;

CREATE TABLE person (
person_id    NUMBER(10),
first_name   VARCHAR2(25),
last_name    VARCHAR2(25),
person_title TitleList,
active_flag  VARCHAR2(1))
TABLESPACE uwdata;

desc person

set describe depth all

desc person

set describe depth 1


col parent_table_column format a30

SELECT parent_table_name, parent_table_column, type_name
FROM user_varrays;
 
Inserting Into A Collection Based On A VARRAY

Insert Records into Parent,  VARRAY, and Nested Table
INSERT INTO <table_name>
(<column_name_list>)
VALUES
(<list_of_column_values>,
<varray_constructor>(<varray_values>) <table_type_constructor>(<type_constructor(<type_values>)));
INSERT INTO person
(person_id, last_name, person_title, active_flag)
VALUES
(1, 'Dan', 'Morgan', TitleList(title_t('PhD')),'A');

SELECT * FROM person;

INSERT INTO person
(person_id, last_name, person_title, active_flag)
VALUES
(2, 'Tara', 'Havemeyer', TitleList(title_t('MD'), title_t('RN')), 'I');

SELECT person_id, first_name, last_name, person_title, active_flag
FROM person;
 
Selecting From A Collection Based On A VARRAY

Simple Select
SELECT <column_name_list> FROM <table_name>;
SELECT person_id, last_name, person_title, active_flag
FROM person;

col person_title format a40

SELECT person_id, last_name, person_title, active_flag
FROM person;

Unnesting Using the  TABLE  Function
SELECT <column_name_list, <tested_table_column_list>
FROM <table> <alias>, TABLE(<alias.nested_table_name> <alias>
SELECT t1.person_id, t1.last_name, t2.*, t1.active_flag
FROM person t1, TABLE(t1.person_title) t2;
 
Updating A Collection Based On A VARRAY

Update all Nested Table rows associated with a single table row
UPDATE person
SET person_phones =   <table_constructor>(<type_constructor(<column_values>));
UPDATE person
SET person_title = TitleList(title_t('DSc'))
WHERE person_id = 1;

To change the information in a nested table by using the UPDATE statement with a VARRAY, you cannot update individual elements in the varray. You must update all the elements at once - that is, replace the varray.
UPDATE TABLE (
<nested_select_statment>) <alias>
SET <nested_column> = <value>
WHERE <nested_column_name> <condition> <value>
select * from person;

UPDATE person
SET person_title = TitleList(title_t('RN'), title_t('MD'))
WHERE person_id = 2;

select * from person;
 
Deleting From A Collection Based On A VARRAY

Delete From Nested Table with VARRAY
DELETE TABLE (
<nested_select_statment>) <alias>
SELECT * FROM person;

SELECT t1.person_id, t2.*
FROM person t1, TABLE(t1.person_title) t2;

SELECT person_id
FROM (
SELECT t1.person_id, t2.*
FROM person t1, TABLE(t1.person_title) t2)
WHERE title_name = 'RN';

DELETE person
WHERE person_id IN (
  SELECT person_id
  FROM (
  SELECT t1.person_id, t2.*
  FROM person t1, TABLE(t1.person_title) t2)
  WHERE title_name = 'RN');

SELECT * FROM person;

DELETE person
WHERE person_id = 1;
 
Comparing Collections

Collection Comparison
Collections cannot be directly compared for equality or inequality. For instance, the following IF condition is not allowed:

set serveroutput on

DECLARE
 TYPE clientele IS TABLE OF VARCHAR2(64);
 group1 clientele := clientele('Customer 1', 'Customer 2');
 group2 clientele := clientele('Customer 1', 'Customer 3');
BEGIN
  -- Equality test causes compilation error
  IF group1 = group2 THEN
    dbms_output.put_line('Equal');
  ELSE
    dbms_output.put_line('Not Equal');
  END IF;
END;
/

This restriction also applies to implicit comparisons. For example, collections cannot appear in a DISTINCT,
GROUP BY, or ORDER BY list. Individual elements, however can be compared. For example:


DECLARE
 TYPE Clientele IS TABLE OF VARCHAR2(64);
 group1 Clientele := Clientele('Customer 1', 'Customer 2');
 group2 Clientele := Clientele('Customer 1', 'Customer 3');
BEGIN
  -- Equality test causes compilation error
  IF group1(1) = group2(1) THEN
    dbms_output.put_line('Equal');
  ELSE
    dbms_output.put_line('Not Equal');
  END IF;
END;
/

and

DECLARE
 TYPE Clientele IS TABLE OF VARCHAR2(64);
 group1 Clientele := Clientele('Customer 1', 'Customer 2');
 group2 Clientele := Clientele('Customer 1', 'Customer 3');
BEGIN
  -- Equality test causes compilation error
  IF group1(2) = group2(2) THEN
    dbms_output.put_line('Equal');
  ELSE
    dbms_output.put_line('Not Equal');
  END IF;
END;
/

so ...

DECLARE
 TYPE Clientele IS TABLE OF VARCHAR2(64);
 group1 Clientele := Clientele('Customer 1', 'Customer 2');
 group2 Clientele := Clientele('Customer 1', 'Customer 3');
BEGIN
  -- Equality test causes compilation error
  FOR i IN 1..2
  LOOP
    IF group1(i) = group2(i) THEN
      dbms_output.put_line('Equal');
    ELSE
      dbms_output.put_line(group1(i) ||
      ' Is Not The Same As ' || group2(i));
    END IF;
  END LOOP;
END;
/
 
Related Topics
Collection Functions
Nested Tables
REF Cursors
Types
VArrays
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [152 users online]    © 2010 psoug.org