ORA-02303: cannot drop or replace a type with type or table dependents

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Updated june 8, 2009: see the comments section for an example of ALTER TYPE statement that works on Oracle 9i or better.

In spite of all enhancements that oracle database underwent over the years, when it comes to replacing an existing user defined object type referenced by other objects with a newer version containing different attributes, the developer or the DBA is basically left to his own fate, so he/she must take appropriate actions in order to successfully carry out the upgrade.
Failure to do so, will most likely lead to the following error message:

ORA-02303: cannot drop or replace a type with type or table dependents

Let's build a simple test case to see what happens and how we can circumvent the problem.
By the way, this is also a good opportunity to see the famous CAST/MULTISET functions at work.

Initially we'll build a single table containing the table names in the schema along with their column names and column comments, stored as a nested table.

create or replace
type column_type as object (
col_name     varchar2(30),
col_comment  varchar2(4000)
);
/
create or replace type tab_column_type as table of column_type;
/
create table test_tab (
table_name        varchar2(30),
table_columns     tab_column_type)
nested table table_columns store as nested_tab return as value;

comment on column test_tab.table_name is 'name of table';
comment on column test_tab.table_columns is 'nested table containing all columns';


insert into test_tab
select
table_name,
cast(multiset(select cc.column_name, cc.comments
        from user_col_comments cc
        where cc.table_name = t.table_name)
as tab_column_type)
as table_columns
from user_tables t;

commit;

Now, you can query the custom table we just created to see what's in there using the classical nested table unnesting technique:

select t.table_name, tc.col_name, tc.col_comment
from test_tab t, table(t.table_columns) tc
where t.table_name = 'TEST_TAB';

TABLE_NAME    COL_NAME             COL_COMMENT                      
------------- -------------------- ---------------------------------------
TEST_TAB      TABLE_NAME           name of table                    
TEST_TAB      TABLE_COLUMNS        nested table containing all columns

2 rows selected

Ok, imagine that we want to add one more attribute to the column_type object (in green color).

create or replace
type column_type as object (
col_name     varchar2(30),
col_comment  varchar2(4000),
col_type     varchar2(255)
);
/

ORA-02303: cannot drop or replace a type with type or table dependents

As you see, Oracle complaints about the fact that there is nested table based on an object type (tab_column_type) that depends on column_type.

How do we move forward in this case?
There are at least two possibilities basing on the specific situation:

1. we don't need to migrate the existing data, we can rebuild the information from scratch. In this case we can drop the column containing the nested table and the dependent object type, then we can extend column_type, rebuild the nested table type and add it to the existing table.
2. we need to partially migrate the existing data as we cannot truncate the parent table containing the nested table, but we can rebuild the content of the nested table basing on the parent key.
   
3. we do need to migrate the existing data as we cannot afford to loose the current values. In this case we need to find a way to migrate the nested table content somewhere.

Let's begin with the simplest case (#1):

truncate table test_tab;

alter table test_tab drop column table_columns;

drop type tab_column_type;

-- extend the original type definition

create or replace
type column_type as object (
col_name     varchar2(30),
col_comment  varchar2(4000),
col_type     varchar2(255)
);
/

create or replace type tab_column_type as table of column_type;
/

-- create a new nested table column in the existing table

alter table test_tab
add (table_columns tab_column_type)
nested table table_columns store as nested_tab return as value;

comment on column test_tab.table_columns is 'nested table containing all columns';

insert into test_tab
select
table_name,
cast(multiset(select cc.column_name, cc.comments, tc.data_type
              from user_col_comments cc, user_tab_columns tc
              where tc.table_name = t.table_name
                and tc.table_name = cc.table_name
                and tc.column_name = cc.column_name) as tab_column_type)
as table_columns
from user_tables t;

commit;

select t.table_name, tc.col_name, tc.col_comment, tc.col_type
from test_tab t, table(t.table_columns) tc
where t.table_name = 'TEST_TAB';

TABLE_NAME      COL_NAME       COL_COMMENT                  COL_TYPE
--------------- -------------- ---------------------------- -------------
TEST_TAB        TABLE_NAME     name of table                VARCHAR2
TEST_TAB        TABLE_COLUMNS  nested table containing ...  TAB_COLUMN_TYPE

2 rows selected

Let's address case #2 now:

alter table test_tab drop column table_columns;

drop type tab_column_type;

create or replace
type column_type as object (
col_name     varchar2(30),
col_comment  varchar2(4000),
col_type     varchar2(255)
);
/

create or replace type tab_column_type as table of column_type;
/

alter table test_tab
add (table_columns tab_column_type)
nested table table_columns store as nested_tab return as value;

comment on column test_tab.table_columns is 'nested table containing all columns';

update test_tab t
set table_columns =
  cast(
   multiset(
     select cc.column_name, cc.comments, tc.data_type
       from user_col_comments cc, user_tab_columns tc
      where tc.table_name = t.table_name
        and tc.table_name = cc.table_name
        and tc.column_name = cc.column_name)
   as tab_column_type);

commit;

select t.table_name, tc.col_name, tc.col_comment, tc.col_type
from test_tab t, table(t.table_columns) tc
where t.table_name = 'TEST_TAB';

TABLE_NAME      COL_NAME       COL_COMMENT                  COL_TYPE
--------------- -------------- ---------------------------- -------------
TEST_TAB        TABLE_NAME     name of table                VARCHAR2
TEST_TAB        TABLE_COLUMNS  nested table containing ...  TAB_COLUMN_TYPE

2 rows selected

Finally, let's tackle the most complex scenario (#3):

create or replace
type old_column_type as object (
col_name     varchar2(30),
col_comment  varchar2(4000)
);
/

create or replace type old_tab_column_type as table of old_column_type;
/

create table mig_test_table (
table_name        varchar2(30),
table_columns     old_tab_column_type)
nested table table_columns store as old_nested_tab return as value;

-- migrate the data to the staging table

insert into mig_test_table
select
t.table_name,
cast(
 multiset(
  select col_name, col_comment
    from table(t.table_columns))
 as old_tab_column_type)
from test_tab t;

alter table test_tab drop column table_columns;

drop type tab_column_type;

create or replace
type column_type as object (
col_name     varchar2(30),
col_comment  varchar2(4000),
col_type     varchar2(255)
);
/

create or replace type tab_column_type as table of column_type;
/

alter table test_tab
add (table_columns tab_column_type)
nested table table_columns store as nested_tab return as value;

comment on column test_tab.table_columns is 'nested table containing all columns';

-- put back the original data and update the new attribute at the same time

update test_tab t
set table_columns =
  cast(
   multiset(
     select cc.col_name, cc.col_comment, tc.data_type
       from mig_test_table m, table(m.table_columns) cc, user_tab_columns tc
      where m.table_name = t.table_name
        and m.table_name = tc.table_name
        and cc.col_name = tc.column_name)
   as tab_column_type);

commit;

select t.table_name, tc.col_name, tc.col_comment, tc.col_type
from test_tab t, table(t.table_columns) tc
where t.table_name = 'TEST_TAB';

TABLE_NAME      COL_NAME       COL_COMMENT                  COL_TYPE
--------------- -------------- ---------------------------- -------------
TEST_TAB        TABLE_NAME     name of table                VARCHAR2
TEST_TAB        TABLE_COLUMNS  nested table containing ...  TAB_COLUMN_TYPE

2 rows selected

Final considerations:
in these examples i did not address problems like invalid triggers or depending procedures that become invalid when the column is dropped.
In a real life scenario this is very likely to happen and in most cases, i guess you'll have to put the database in restricted mode just to be sure that nobody is accessing the objects while the migration is underway.
Clearly you'll have to check also any dependent objects and fix the failing SQL statements or extend them to support the newly added object attribute(s) or remove the references to the old ones.

See message translations for ORA-02303 and search additional resources.