If a constraint is placed on a Nested Table's attribute the DDL extraction for the parent table does not capture that constraint, and as such is incopmplete.
(Note the storage table does not appear under the USER_tables see SQL below. I do not know if this is Oracle's intended behavior.)
/*
|| ***********
|| SET-UP
|| ***********
*/
/* CREATE OBJECT DEFINTION */
CREATE OR REPLACE
TYPE submitted_history_obj_t AS OBJECT
(
SUBMITTED_DATE DATE,
REVIEW_HOURS NUMBER(5,2),
CONFERENCE_HOURS NUMBER(5,2)
)
/
/* CREATE TABLE TYPE */
CREATE OR REPLACE
TYPE submitted_history_tab_t AS TABLE OF submitted_history_obj_t
/
/* CREATE TABLE USING NESTED TABLE */
CREATE TABLE consultant_review
(review_id NUMBER(9,0) NOT NULL,
consultant_id NUMBER(9,0) NOT NULL,
review_type NUMBER NOT NULL,
review_status VARCHAR2(1),
submitted_histories SUBMITTED_HISTORY_TAB_T
)
NESTED TABLE submitted_histories STORE AS submitted_history_ntab
/
/* PK CONSTRAINT */
ALTER TABLE consultant_review
ADD CONSTRAINT consultant_review_pk PRIMARY KEY (review_id, consultant_id)
/
/* CONSTRAINT ON STORAGE TABLE OF NESTED TABLE */
ALTER TABLE submitted_history_ntab ADD CONSTRAINT
submitted_history_ntab_ck01 CHECK (submitted_date IS NOT NULL)
/
/* EXTRACT DDL */
/* STROAGE TABLE APPEARS UNDER USER OBJECTS WITH OBJECT_TYPE = 'TABLE' */
SELECT *
FROM user_objects
WHERE object_name = 'SUBMITTED_HISTORY_NTAB'
/
/* STORAGE TABLE DOES NOT APPEAR IN USER TABLES OR STROAGE TABLES */
SELECT *
FROM user_tables
WHERE table_name = 'SUBMITTED_HISTORY_NTAB'
/
SELECT *
FROM user_constraints
WHERE table_name = 'SUBMITTED_HISTORY_NTAB'
/
SELECT *
FROM user_cons_columns
WHERE table_name = 'SUBMITTED_HISTORY_NTAB'
/
/*
|| ***********
|| CLEAN-UP
|| ***********
*/
DROP TABLE consultant_review
/
DROP TYPE submitted_history_tab_t
/
DROP TYPE submitted_history_obj_t
/
(Note the storage table does not appear under the USER_tables see SQL below. I do not know if this is Oracle's intended behavior.)
/*
|| ***********
|| SET-UP
|| ***********
*/
/* CREATE OBJECT DEFINTION */
CREATE OR REPLACE
TYPE submitted_history_obj_t AS OBJECT
(
SUBMITTED_DATE DATE,
REVIEW_HOURS NUMBER(5,2),
CONFERENCE_HOURS NUMBER(5,2)
)
/
/* CREATE TABLE TYPE */
CREATE OR REPLACE
TYPE submitted_history_tab_t AS TABLE OF submitted_history_obj_t
/
/* CREATE TABLE USING NESTED TABLE */
CREATE TABLE consultant_review
(review_id NUMBER(9,0) NOT NULL,
consultant_id NUMBER(9,0) NOT NULL,
review_type NUMBER NOT NULL,
review_status VARCHAR2(1),
submitted_histories SUBMITTED_HISTORY_TAB_T
)
NESTED TABLE submitted_histories STORE AS submitted_history_ntab
/
/* PK CONSTRAINT */
ALTER TABLE consultant_review
ADD CONSTRAINT consultant_review_pk PRIMARY KEY (review_id, consultant_id)
/
/* CONSTRAINT ON STORAGE TABLE OF NESTED TABLE */
ALTER TABLE submitted_history_ntab ADD CONSTRAINT
submitted_history_ntab_ck01 CHECK (submitted_date IS NOT NULL)
/
/* EXTRACT DDL */
/* STROAGE TABLE APPEARS UNDER USER OBJECTS WITH OBJECT_TYPE = 'TABLE' */
SELECT *
FROM user_objects
WHERE object_name = 'SUBMITTED_HISTORY_NTAB'
/
/* STORAGE TABLE DOES NOT APPEAR IN USER TABLES OR STROAGE TABLES */
SELECT *
FROM user_tables
WHERE table_name = 'SUBMITTED_HISTORY_NTAB'
/
SELECT *
FROM user_constraints
WHERE table_name = 'SUBMITTED_HISTORY_NTAB'
/
SELECT *
FROM user_cons_columns
WHERE table_name = 'SUBMITTED_HISTORY_NTAB'
/
/*
|| ***********
|| CLEAN-UP
|| ***********
*/
DROP TABLE consultant_review
/
DROP TYPE submitted_history_tab_t
/
DROP TYPE submitted_history_obj_t
/
