You can make a simple change to a type by adding one attribute and dropping another.

Example 8-5makes such a change to person_type. The CASCADE keyword propagates the type change to dependent types and tables, but the phrase NOT INCLUDING TABLE DATA prevents conversion of the related data.

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)

During 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 ALTER TABLE with the UPGRADE INCLUDING DATA. For example:

ALTER TYPE person_typ ADD ATTRIBUTE (photo BLOB)
  CASCADE NOT INCLUDING TABLE DATA;
ALTER TABLE person_obj_table UPGRADE INCLUDING DATA;

The ALTER TABLE line converts only the table listed. The CASCADE option prevents conversion of other tables or dependents.

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.

Initial Schema

-- 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, tasks_nttab.

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 people_typ.

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 CASCADE option removes the method from the target type and all subtypes. However, if a method is redefined in a subtype, the CASCADE will fail and roll back. For the CASCADE to 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_DEPENDENCIES table to find all the schema objects, including types, that depend on a given type. You can also run the DBMS_UTILITY.GET_DEPENDENCY utility to find the dependencies of a type.

• You can use the INVALIDATE option 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.

  Unlike CASCADE, INVALIDATE bypasses 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 INSTANTIABLE to NOT INSTANTIABLE is allowed only if the type has no table dependents.

• Altering an object type from NOT INSTANTIABLE to INSTANTIABLE is allowed anytime. This change does not affect tables.

Modifying the FINAL Property

• Altering an object type from NOT FINAL to FINAL is only allowed if the target type has no subtypes.

• When you alter an object type from FINAL to NOT FINAL or vice versa, you must use CASCADE to convert data in dependent columns and tables immediately. You may not use the CASCADE option NOT INCLUDING TABLE DATA to defer converting data.

  • From NOT FINAL to FINAL, you must use CASCADE INCLUDING TABLE DATA.

  • From FINAL to NOT FINAL, you may use either CASCADE INCLUDING TABLE DATA or CASCADE CONVERT TO SUBSTITUTABLE.

    When you alter a type from FINAL to NOT FINAL, select the CASCADE option 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 FINAL to NOT FINAL enables 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 NOT SUBSTITUTABLE AT ALL 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 NOT FINAL in a way that makes existing columns and tables of the type substitutable (assuming that they are not marked NOT SUBSTITUTABLE), use the CASCADE option CONVERT TO SUBSTITUTABLE.

  The following example shows the use of CASCADE with 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;

  This CASCADE option marks each existing column as SUBSTITUTABLE AT ALL LEVELS and 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.

Table 8-1 lists some of the important options in the ALTER TYPE and ALTER TYPE...CASCADE statements for altering the attribute or method definition of a type.

You can use ALTER 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".