Forced Replacement of Types in 11g release 2

If you have used types, you must have realized how powerful they can be. You can define your own data type that can be a composite of various other data types, or they can be records to group related pieces of data together, even to match a complete table row. Here is an example of a type called TY_TRANS that defines the elements of a transaction:

create or replace type ty_trans
as object
(
    trans_id    number(2),
    trans_amt   number(10)
)
/

Next, you can a type to hold sales information. Since every sale will have a transaction, you can define a column of type ty_trans, shown below:

create or replace type ty_sales
as object
(
    sales_id    number(2),
    trans_rec   ty_trans
)
/

Once you define the structures this way, TY_SALES becomes a dependent of TY_TRANS. You can confirm that by querying USER_DEPENDENCIES view:

SQL> select referenced_name, dependency_type
  2  from user_dependencies
  3  where name = 'TY_SALES'
  4  /

REFERENCED_NAME       DEPE
---------------------           ----
STANDARD              HARD
TY_TRANS              HARD

This shows that TY_SALES has a “hard” dependency on the type TY_TRANS.

Now, let’s look at a real world possibility. What if you made a mistake in defining the types and defined an attribute with a wrong precision or just want to change the precision keeping with the business needs? Well, not a problem – you simply use the CREATE OR REPLACE statement to recreate that type:

create or replace type ty_sales
as object
(
    sales_id    number(3),
    trans_rec   ty_trans
)
/

Here you recreated the type with the one of the attributes as number(3) instead of number(2), as it was previously. While this operation was successful for this type, what if you had to do the same for ty_trans?

SQL> create or replace type ty_trans
  2  as object
  3  (
  4      trans_id    number (4),
  5      trans_amt   number
  6  )
  7  /
create or replace type ty_trans
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents

The error message says it all – you can’t alter this type since it has a dependent, as we saw earlier from the user_dependencies view. It’s sort of a parent-child relationship between the types. If there is at least one child, you can’t drop the parent. So, what are your options for changing the “parent” type definition?

Until Oracle Database 11g Release 2, the only option for modifying that type is the MODIFY ATTRIBUTE clause of ALTER TYPE statement, which is an expensive and potentially error prone proposition. In Release 2, there is a very convenient FORCE clause to replace the type forcibly. Using this, we can alter the type TY_TRANS as:

create or replace type ty_trans
force
as object
(
    trans_id    number (4),
    trans_amt   number
)
/

This will execute successfully and the type will be created, due to the FORCE clause shown in bold above. This makes it very convenient when you deploy applications – you don’t have to worry about which specific attributes have changed; rather, a full replace type takes care of the type definition, changed or not.