- 10 Nov, 2021
- 0 Comments
- 4 Mins Read
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,'Ankush',
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,'Chetan',
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 Ankush 253 SN Gzb UP India
1 Ankush 234 GP Gzb UP India
1 Chetan 123 IP Gzb UP India
1 Chetan 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.
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 Ankush 253 SN Gzb UP India
1 Ankush 234 GP Gzb UP India
1 Chetan 123 IP Gzb UP India
1 Chetan 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.