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

ORA-02303: cannot drop or replace a type with type or table dependents
Cause: An attempt was made to drop or replace a type that has type or table dependents.
Action: For DROP TYPE, drop all type(s) and table(s) depending on the type and then retry the operation, or use the FORCE option. For CREATE TYPE, drop all type(s) and table(s) depending on the type and then retry the operation, or drop all table(s) depending on the type and retry with the FORCE option.


Little Background:
When we try to replace an existing user defined object type which is being referenced by other objects, the developer must take appropriate actions in order to successfully carry out the upgrade. The newer version of user defined object type, even with one simple change, can not replace existing one and leads to ORA-02303.

I faced ORA-02303 in our one of the development environment, when a user defined object type was getting replace with some changes and the existing type was being referenced in some of the objects.


Reproduce ORA-02303
Lets first try create a type and a table which is dependent on it. We will also insert some records as there always will be records in production environment.

SQL> create or replace type
  2  type_address as object(
  3    address  varchar2(2000),
  4    city     varchar2(100),
  5    state    varchar2(100),
  6    country  varchar2(100)
  7  );
  8  /
Type created.

SQL> create or replace type type_address_tab as table of type_address;
  2  /
Type created.

SQL> create table employee (
  2  empid   number(10),
  3  ename   varchar2(100),
  4  address type_address_tab
  5  ) nested table address store as emp_address_tab;
Table created.

SQL> insert into employee values
  2    (1,'Nimish',
  3      type_address_tab(
  4        type_address('253 SN','Gzb','UP','India'),
  5        type_address('234 GP','Gzb','UP','India')
  6      )
  7    );
1 row created.

SQL> insert into employee values
  2    (1,'Rahul',
  3      type_address_tab(
  4        type_address('123 IP','Gzb','UP','India'),
  5        type_address('987 UN','Delhi','Delhi','India')
  6      )
  7    );
1 row created. 

SQL> select e.empid, e.ename, a.address, a.city, a.state, a.country
  2  from
  3  employee e, table(e.address) a;

EMPID ENAME      ADDRESS    CITY       STATE      COUNTRY
----- ---------- ---------- ---------- ---------- ----------
    1 Nimish     253 SN     Gzb        UP         India
    1 Nimish     234 GP     Gzb        UP         India
    1 Rahul      123 IP     Gzb        UP         India
    1 Rahul      987 UN     Delhi      Delhi      India

Now we have all the setup. What if the requirement comes to just add a column for PIN in our type. A straight "create or replace type" command will lead us to oracle exception ORA-02303.

SQL> create or replace type
  2  type_address as object(
  3    address  varchar2(2000),
  4    city     varchar2(100),
  5    state    varchar2(100),
  6    country  varchar2(100),
  7    pin      varchar2(10),
  8  );
  9  /
create or replace type
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents

Solution of ORA-02303:
Thanks to Google and blog (dont want to mention the name), they guided us to following set actions in order to successfully carry out the upgrade of Type if type is table dependent:

1) Create a temporary object type and its table object same as type_address and type_address_tab
2) Create a temporary table with same structure as "employee" with temporary table type column.
3) Migrate the data to the temporary table
4) Drop column "address" from "employee" table
5) Drop "type_address_tab" table type object
6) Create or Replace "type_address" type with necessary changes
7) Create type "type_address_tab" table type object again
8) Add column "address" in "employee" table of "type_address_tab" type
9) Put back the original data in "employee" from temporary table
10) Drop all temporary created objects and table


I was wondering why Oracle requires so much time consuming and irritating steps for such a simple task. So I read some Oracle Documentation and found From Oracle 11g [not sure about 10g], Oracle provided "cascade" option which works perfectly for these kind of scenarios.

Reference: Oracle Documentation


Direct & Simple Solution:
So we can now directly add/change an ATTRIBUTE of a TYPE even if type is table dependents. Here I am adding an attribute PIN in my type_address and then modifying that ATTRIBUTE to change the size.

SQL> alter type type_address add ATTRIBUTE (pin number(1)) cascade;
Type altered.

SQL> insert into employee values
  2  (1,'XX',
  3    type_address_tab(
  4    type_address('123 IP','Gzb','UP','India',1),
  5    type_address('987 UN','Delhi','Delhi','India',2)
  6    )
  7  );
1 row created.

SQL> commit;
Commit complete.

SQL> alter type type_address modify ATTRIBUTE (pin number(10)) cascade;
Type altered.

SQL> insert into employee values
  2  (1,'YY',
  3    type_address_tab(
  4    type_address('123 IP','Gzb','UP','India',10000),
  5    type_address('987 UN','Delhi','Delhi','India',20000)
  6    )
  7  );
1 row created.

SQL> commit;
Commit complete.

SQL> select e.empid, e.ename, a.address, a.city, a.state, a.country, a.pin
  2  from
  3  employee e, table(e.address) a;

EMPID ENAME      ADDRESS    CITY       STATE      COUNTRY           PIN
----- ---------- ---------- ---------- ---------- ---------- ----------
    1 Nimish     253 SN     Gzb        UP         India
    1 Nimish     234 GP     Gzb        UP         India
    1 Rahul      123 IP     Gzb        UP         India
    1 Rahul      987 UN     Delhi      Delhi      India
    1 XX         123 IP     Gzb        UP         India               1
    1 XX         987 UN     Delhi      Delhi      India               2
    1 YY         123 IP     Gzb        UP         India           10000
    1 YY         987 UN     Delhi      Delhi      India           20000
8 rows selected.

So here is just want to re-phase what all Oracle Gurus say, Oracle Documentation is best source to learn Oracle Database. Blogs may give you some direct solution but they may be outdated or author may have missed some simple solution.

I Hope you have enjoyed reading this article about Types and ORA-02303.


Related Posts:
- ORA-01460 unimplemented or unreasonable conversion requested
- ORA-01439: column to be modified must be empty to change datatype
- ORA-01489: result of string concatenation is too long
- ORA-01723: zero-length columns are not allowed
- ORA-01830 date format picture ends before converting entire input string