Tuesday, June 24, 2008

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.

4 comments:

arnab mitra said...

this was a great help..thanks!!!

Anonymous said...

Or you could use the ALTER TYPE statement.

http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_4002.htm

Byte64 said...

you're right Anon,
here is a sample statement that will do the trick:

alter type column_type add attribute (col_type varchar2(255)) cascade;

Thanks for the suggestion!
Flavio

Anonymous said...

DROP TYPE column_type FORCE

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles