On generating DDL for an object type that has evolved over time. The resulting DDL is a cartesian procuct of the object's elements for each revision.
e.g.
/* DDL for object type with one evolutionary change see set-up below for original DDL */
CREATE OR REPLACE TYPE submitted_history_obj_t AS OBJECT
TYPE submitted_history_obj_t AS OBJECT
(
(
SUBMITTED_DATE DATE,
SUBMITTED_DATE DATE,
REVIEW_HOURS NUMBER(3,1),
REVIEW_HOURS NUMBER(3,1),
CONFERENCE_HOURS NUMBER(3,1)
CONFERENCE_HOURS NUMBER(3,1)
)
)
ALTER TYPE submitted_history_obj_t MODIFY ATTRIBUTE (
REVIEW_HOURS NUMBER(5,2),
CONFERENCE_HOURS NUMBER(5,2)
) CASCADE
Additionally the object type appears in the DB browser multiple times once for each revison.
If a collection type is based on an evolving object likewise it's create statement is repeated for each evolution (Note the duplicate create statement only occurs if the object evolves after the collection has been created. The object may evolve prior to the creation of the collection):
e.g.
CREATE OR REPLACE TYPE submitted_history_tab_t AS TABLE OF submitted_history_obj_t
TYPE submitted_history_tab_t AS TABLE OF submitted_history_obj_t
SAMPLE DDL:
/*
||************
|| SET-UP
||************
*/
/* CREATE OBJECT DEFINITON */
CREATE OR REPLACE
TYPE submitted_history_obj_t AS OBJECT
(
SUBMITTED_DATE DATE,
REVIEW_HOURS NUMBER(3,1),
CONFERENCE_HOURS NUMBER(3,1)
)
/* EXTRACT DDL FOR OBJECT TYPE */
/* CHANGE OBJECT DEFINITION */
ALTER TYPE submitted_history_obj_t MODIFY ATTRIBUTE (
REVIEW_HOURS NUMBER(5,2),
CONFERENCE_HOURS NUMBER(5,2)
) CASCADE
/* EXTRACT DDL FOR OBJECT TYPE */
/* CREATE COLLECTION TYPE BASED ON OBJECT TYPE */
CREATE OR REPLACE
TYPE submitted_history_tab_t AS TABLE OF submitted_history_obj_t
/* EXTRACT DDL FOR COLLECTION TYPE */
/* CHANGE OBJECT DEFINITION */
ALTER TYPE submitted_history_obj_t MODIFY ATTRIBUTE (
REVIEW_HOURS NUMBER(6,2),
CONFERENCE_HOURS NUMBER(6,2)
) CASCADE
/* EXTRACT DDL FOR COLLECTION TYPE */
/* EXTRACT DDL FOR OBJECT TYPE (optional) */
/*
||************
|| CLEAN-UP
||************
*/
DROP TYPE submitted_history_tab_t
DROP TYPE submitted_history_obj_t
e.g.
/* DDL for object type with one evolutionary change see set-up below for original DDL */
CREATE OR REPLACE TYPE submitted_history_obj_t AS OBJECT
TYPE submitted_history_obj_t AS OBJECT
(
(
SUBMITTED_DATE DATE,
SUBMITTED_DATE DATE,
REVIEW_HOURS NUMBER(3,1),
REVIEW_HOURS NUMBER(3,1),
CONFERENCE_HOURS NUMBER(3,1)
CONFERENCE_HOURS NUMBER(3,1)
)
)
ALTER TYPE submitted_history_obj_t MODIFY ATTRIBUTE (
REVIEW_HOURS NUMBER(5,2),
CONFERENCE_HOURS NUMBER(5,2)
) CASCADE
Additionally the object type appears in the DB browser multiple times once for each revison.
If a collection type is based on an evolving object likewise it's create statement is repeated for each evolution (Note the duplicate create statement only occurs if the object evolves after the collection has been created. The object may evolve prior to the creation of the collection):
e.g.
CREATE OR REPLACE TYPE submitted_history_tab_t AS TABLE OF submitted_history_obj_t
TYPE submitted_history_tab_t AS TABLE OF submitted_history_obj_t
SAMPLE DDL:
/*
||************
|| SET-UP
||************
*/
/* CREATE OBJECT DEFINITON */
CREATE OR REPLACE
TYPE submitted_history_obj_t AS OBJECT
(
SUBMITTED_DATE DATE,
REVIEW_HOURS NUMBER(3,1),
CONFERENCE_HOURS NUMBER(3,1)
)
/* EXTRACT DDL FOR OBJECT TYPE */
/* CHANGE OBJECT DEFINITION */
ALTER TYPE submitted_history_obj_t MODIFY ATTRIBUTE (
REVIEW_HOURS NUMBER(5,2),
CONFERENCE_HOURS NUMBER(5,2)
) CASCADE
/* EXTRACT DDL FOR OBJECT TYPE */
/* CREATE COLLECTION TYPE BASED ON OBJECT TYPE */
CREATE OR REPLACE
TYPE submitted_history_tab_t AS TABLE OF submitted_history_obj_t
/* EXTRACT DDL FOR COLLECTION TYPE */
/* CHANGE OBJECT DEFINITION */
ALTER TYPE submitted_history_obj_t MODIFY ATTRIBUTE (
REVIEW_HOURS NUMBER(6,2),
CONFERENCE_HOURS NUMBER(6,2)
) CASCADE
/* EXTRACT DDL FOR COLLECTION TYPE */
/* EXTRACT DDL FOR OBJECT TYPE (optional) */
/*
||************
|| CLEAN-UP
||************
*/
DROP TYPE submitted_history_tab_t
DROP TYPE submitted_history_obj_t
