Type evolution is the process of changing a object type.
8.3.1 About Type Evolution
You can make the following changes to evolve an object type:
Add and drop attributes
Add and drop methods
Modify a numeric attribute to increase its length, precision, or scale
Modify a varying length character attribute to increase its length
INSTANTIABLEproperties of a type
Modify limit and size of
Modify length, precision, and scale of collection elements
Changes to a type affect things that reference the type. For example, if you add a new attribute to a type, data in a column of that type must be presented so as to include the new attribute.
8.3.2 Type Evolution and Dependent Schema Objects
Dependent schema objects of a type are objects that directly or indirectly reference the type and are affected by a change to it.
A type can have these kinds of dependent schema objects: tables; types or subtypes; program units (PL/SQL blocks) such as procedures, functions, packages, and triggers; indextypes; views (including object views); function-based indexes; and operators.
How a dependent schema object is affected by a change to a type depends on the object and on the nature of the change.
Dependent program units, views, operators, and indextypes are marked invalid when the type is modified. The next time one of these invalid schema objects is referenced, it is revalidated using the new type definition. If the object recompiles successfully, it becomes valid and can be used again.
Dependent function-based indexes may be dropped or disabled, depending on the type change, and must be rebuilt.
Dependent tables have one or more internal columns added for each attribute added to the type, depending on the attribute type. New attributes are added with
NULLvalues. For each dropped attribute, the columns associated with that attribute are dropped. For each modified attribute, the length, precision, or scale of its associated column is changed accordingly.
These changes mainly involve updating the metadata of the tables and can be performed quickly. However, the data in those tables must be updated to the format of the new type version as well, as discussed in "Options for Updating Data".
8.3.3 Options for Updating Data
Depending on the amount of data, updating can be time-consuming, so the
ALTER TYPE command has options to let you choose whether to convert all dependent table data immediately or to leave it in the old format to be converted piecemeal as it is updated in the course of business.
CASCADE option for
ALTER TYPE propagates a type change to dependent types and tables.
CASCADE itself has the following options that let you choose whether or not to convert table data to the new type format as part of the propagation:
INCLUDING TABLE DATA: converts the data (default)
NOT INCLUDING TABLE DATA: does not convert data
By default, the
CASCADE option converts the data. In either case, table data is always returned in the format of the latest type version. If the table data is stored in the format of an earlier type version, the database converts the data to the format of the latest version before returning it, even though the format in which the data is actually stored is not changed until the data is rewritten.
You can retrieve the definition of the latest type from the system view
USER_SOURCE. You can view definitions of all versions of a type in the
8.3.4 Effects of Structural Changes to Types
Structural changes to a type affect dependent data and require the data to be converted. This is not true for changes that are confined to method definitions or behavior (implementation) of the type.
These possible changes to a type are structural:
Add or drop an attribute
Modify the length, precision, or scale of an attribute
Change the finality of a type from
NOT FINALor the reverse
These changes result in new versions of the altered type and all its dependent types and require the system to add, drop, or modify internal columns of dependent tables as part of the process of converting to the new version.
When you make any of these kinds of changes to a type that has dependent types or tables, the effects of propagating the change are not confined only to metadata but also affect data storage arrangements and require data conversion.
Besides converting data, you may also need to make other changes. For example, if a new attribute is added to a type, and the type body invokes the constructor of the type, then each constructor in the type body must be modified to specify a value for the new attribute. Similarly, if a new method is added, then the type body must be replaced to add the implementation of the new method. The type body can be modified by using the
CREATE OR REPLACE TYPE BODY statement.
8.3.5 Altering a Type by Adding and Dropping Attributes
You can make a simple change to a type by adding one attribute and dropping another.
Example 8-5makes such a change to
CASCADE keyword propagates the type change to dependent types and tables, but the phrase
DATA prevents conversion of the related data.
Example 8-5 Altering an Object Type by Adding and Dropping an Attribute
-- Drop person_typ and person_obj_table if they exist CREATE TYPE person_typ AS OBJECT ( idno NUMBER, name VARCHAR2(30), phone VARCHAR2(20)); / CREATE TABLE person_obj_table OF person_typ; INSERT INTO person_obj_table VALUES (person_typ(12, 'Bob Jones', '650-555-0130')); SELECT value(p) FROM person_obj_table p;
VALUE(P)(IDNO, NAME, PHONE) -------------------------------------------- PERSON_TYP(12, 'Bob Jones', '650-555-0130')
You can add the email attribute and drop the phone attribute as follows:
ALTER TYPE person_typ ADD ATTRIBUTE (email VARCHAR2(80)), DROP ATTRIBUTE phone CASCADE NOT INCLUDING TABLE DATA;
Then you can disconnect and reconnect to accommodate the type change:
connect oe/oe; connect hr/<password>; ALTER SESSION SET PLSQL_WARNINGS = 'enable:all'; -- The data of table person_obj_table has not been converted yet, but -- when the data is retrieved, Oracle returns the data based on -- the latest type version. The new attribute is initialized to NULL. SELECT value(p) FROM person_obj_table p;
VALUE(P)(IDNO, NAME, EMAIL) --------------------------------- PERSON_TYP(12, 'Bob Jones', NULL)
SELECT statements, even though column data may be converted to the latest type version, the converted data is not written back to the column. If you retrieve a particular user-defined type column in a table often, consider converting that data to the latest type version to eliminate redundant data conversions. Converting is especially beneficial if the column contains
VARRAY attributes which typically take more time to convert than objects or nested table columns.
You can convert a column of data by issuing an
UPDATE statement to set the column to itself, as indicated in the following code snippet, which is unrelated to previous code.
UPDATE dept_tab SET emp_array_col = emp_array_col;
You can convert all columns in a table by using
TABLE with the
DATA. For example:
ALTER TYPE person_typ ADD ATTRIBUTE (photo BLOB) CASCADE NOT INCLUDING TABLE DATA; ALTER TABLE person_obj_table UPGRADE INCLUDING DATA;
TABLE line converts only the table listed. The
CASCADE option prevents conversion of other tables or dependents.
8.3.6 Altering a Type by Adding a Nested Table Attribute
You can add a nested table attribute to an object type that is included in a nested table.
The following steps are required to make this complex change to a type.
The steps require this initial schema which is then altered by the code in Step 1.
-- Drop existing person_typ, department_type, people_typ objects or tables CREATE TYPE person_typ AS OBJECT ( idno NUMBER, name VARCHAR2(30), phone VARCHAR2(20)); / -- creating a nested table type CREATE TYPE people_typ AS TABLE OF person_typ;/ CREATE TYPE department_typ AS OBJECT ( manager person_typ, employee people_typ); // a nested table/ CREATE TABLE department OF department_typ NESTED TABLE employee STORE AS employee_store_nt;
The code example in Step 1 starts by creating a new object
tasks_typ and a nested table type to hold it,
The following steps, both in the code example in Step 1, and in other programs, are necessary to add the nested table
tasks as an attribute to the object type
person_typ, which is already included in the nested table
Altering an Object Type by Adding a Nested Table Attribute
-- Requires Ex. 8-6 CREATE TYPE tasks_typ AS OBJECT ( priority VARCHAR2(2), description VARCHAR2(30)); / CREATE TYPE tasks_nttab AS TABLE OF tasks_typ; / -- Propagate the change to employee_store_nt -- Specify a storage name for the new nested table ALTER TABLE employee_store_nt UPGRADE NESTED TABLE tasks STORE AS tasks_nt;
CREATE OR REPLACE TYPE BODYfor
person_typto update the corresponding type body to make it current with the new type definition, if necessary.
- Upgrade the dependent tables to the latest type version and convert the data in the tables. This validates the table and allow for data access again.
ALTER TABLE department UPGRADE INCLUDING DATA;
- Alter dependent PL/SQL program units as needed to take account of changes to the type.
- Use Oracle Type Translator (OTT) to generate new header files for applications, depending on whether the application is written in C or Java.
Adding a new attribute to a supertype also increases the number of attributes in all its subtypes because these inherit the new attribute. Inherited attributes always precede declared (locally defined) attributes, so adding a new attribute to a supertype causes the ordinal position of all declared attributes of any subtype to be incremented by one recursively.
You must update the mappings of the altered type to include the new attributes. Use Oracle Type Translator (OTT) to do this. If you use another tool, you must be sure that the type headers are properly synchronized with the type definition in the server; otherwise, unpredictable behavior may result.
- Modify application code as needed and rebuild the application.
8.3.7 About Validating Types That Have Been Altered
When the system executes an
ALTER TYPE statement, it first validates the requested type change syntactically and semantically to make sure it is legal.
The system performs the same validations as for a
CREATE TYPE statement plus some additional ones. If the new specification of the target type or any of its dependent types fails the type validations, the
ALTER TYPE statement aborts. No new type version is created, and all dependent objects remain unchanged.
If dependent tables exist, further checking ensures that restrictions relating to the tables and indexes are observed. For example, it ensures that an attribute being dropped is not used as a partitioning key. Again, if the
ALTER TYPE statement fails the check of table-related restrictions, then the type change is aborted, and no new version of the type is created.
When a single
ALTER TYPE statement adds multiple attributes, it is done in the order specified. Multiple type changes can be specified in the same
ALTER TYPE statement, but no attribute name or method signature can be specified more than once in the statement. For example, adding and modifying the same attribute in a single statement is not allowed.
The following sections contain other notes on type changes including:
Dropping an Attribute
Dropping all attributes from a root type is not allowed. Instead, you must drop the type. Because a subtype inherits all the attributes from its supertype, dropping all the attributes from a subtype does not reduce its attribute count to zero; therefore, dropping all attributes declared locally in a subtype is allowed.
Only an attribute declared locally in the target type can be dropped. You cannot drop an inherited attribute from a subtype. Instead, drop the attribute from the type where it is locally declared.
Dropping an attribute which is part of a table partitioning or sub-partitioning key in a table is not allowed.
When an attribute is dropped, the column corresponding to the dropped attribute is dropped.
When an attribute is dropped, any indexes, statistics, constraints, and referential integrity constraints that reference it are removed.
Modifying the Length, Precision, or Scale of an Attribute Type
You are not allowed to expand the length of an attribute referenced in a function-based index, clustered key or domain index on a dependent table.
You are not allowed to decrease the length, precision, or scale of an attribute.
Dropping a Method
You can only drop a method from the type in which the method is defined (or redefined): You cannot drop an inherited method from a subtype, and you cannot drop an redefined method from a supertype.
If a method is not redefined, dropping it using the
CASCADEoption removes the method from the target type and all subtypes. However, if a method is redefined in a subtype, the
CASCADEwill fail and roll back. For the
CASCADEto succeed, you must first drop each redefined method from the subtype that defines it and then drop the method from the supertype.
You can consult the
USER_DEPENDENCIEStable to find all the schema objects, including types, that depend on a given type. You can also run the
DBMS_UTILITY.GET_DEPENDENCYutility to find the dependencies of a type.
You can use the
INVALIDATEoption to drop a method that has been redefined, but the redefined versions in the subtypes must still be dropped manually. The subtypes will remain in an invalid state until they are explicitly altered to drop the redefined versions. Until then, an attempt to recompile the subtypes for revalidation will produce the error
Method does not override.
INVALIDATEbypasses all the type and table checks and simply invalidates all schema objects dependent on the type. The objects are revalidated the next time they are accessed. This option is faster than using
CASCADE, but you must be certain that no problems occur when revalidating dependent types and tables. Table data cannot be accessed while a table is invalid; if a table cannot be validated, its data remains inaccessible.
Modifying the INSTANTIABLE Property
Altering an object type from
NOT INSTANTIABLEis allowed only if the type has no table dependents.
Altering an object type from
INSTANTIABLEis allowed anytime. This change does not affect tables.
Modifying the FINAL Property
Altering an object type from
FINALis only allowed if the target type has no subtypes.
When you alter an object type from
NOT FINALor vice versa, you must use
CASCADEto convert data in dependent columns and tables immediately. You may not use the
NOT INCLUDING TABLE DATAto defer converting data.
FINAL, you must use
CASCADE INCLUDING TABLE DATA.
NOT FINAL, you may use either
CASCADE INCLUDING TABLE DATAor
CASCADE CONVERT TO SUBSTITUTABLE.
When you alter a type from
FINAL, select the
CASCADEoption based on whether or not you want to insert new subtypes of the altered types into existing columns and tables.
By default, altering a type from
FINALenables you to create new substitutable tables and columns of that type, but it does not automatically make existing columns (or object tables) of that type substitutable. In fact, just the opposite happens: existing columns and tables of the type are marked
LEVELS. If any embedded attribute of these columns is substitutable, an error is generated. New subtypes of the altered type cannot be inserted into these preexisting columns and tables.
To alter an object type to
FINALin a way that makes existing columns and tables of the type substitutable (assuming that they are not marked
SUBSTITUTABLE), use the
CONVERT TO SUBSTITUTABLE.
The following example shows the use of
CASCADEwith the option
CONVERT TO SUBSTITUTABLE:
CREATE TYPE shape AS OBJECT ( name VARCHAR2(30), area NUMBER) FINAL;/ ALTER TYPE shape NOT FINAL CASCADE CONVERT TO SUBSTITUTABLE;
CASCADEoption marks each existing column as
SUBSTITUTABLE AT ALL LEVELSand causes a new, hidden column to be added for the TypeId of instances stored in the column. The column can then store subtype instances of the altered type.
8.3.8 ALTER TYPE Statement for Type Evolution
Table 8-1 lists some of the important options in the
...CASCADE statements for altering the attribute or method definition of a type.
Table 8-1 ALTER TYPE Options for Type Evolution
Propagates the type change to dependent types and tables. The statement aborts if an error is found in dependent types or tables unless the
Converts data stored in all user-defined columns to the most recent version of the column type.
For each new attribute added to the column type, a new attribute is added to the data and is initialized to
Leaves column data as is, does not change type version. If an attribute is dropped from a type referenced by a table, the corresponding column of the dropped attribute is not removed from the table. However, the metadata of the column is marked unused. If the dropped attribute is stored out-of-line (for example,
This option is useful when you have many large tables and may run out of rollback segments if you convert them all in one transaction. This option enables you to convert the data of each dependent table later in a separate transaction (using an
Specifying this option speeds up the table upgrade because the table data remains in the format of the old type version. However, selecting data from this table requires converting the images stored in the column to the latest type version. This is likely to affect performance during subsequent
Because this option only requires updating the table metadata, it does not require that all tablespaces be on-line in read/write mode for the statement to succeed.
Forces the system to ignore errors from dependent tables and indexes. Errors are logged in a specified exception table so that they can be queried afterward. Use this option with caution because dependent tables can become inaccessible if some table errors occur.
For use when altering a type from
If the type is altered to
Oracle Database SQL Language Reference for further information about
8.3.9 ALTER TABLE Statement for Type Evolution
You can use
TABLE to convert table data to the latest version of referenced types. For an example, see "Altering a Type by Adding a Nested Table Attribute".