How can I check a type's dependents order to drop them and replace/modify the initial type?

I tried to modify a type using the following code and it gave me the error code: 'ORA-02303'. I don't know much about Oracle or PL/SQL but I need to solve this; so I'd appreciate any fur开发者_C百科ther help with this.

Thanks in advance. The code is just an example. But then again, I need to check its dependents first.

create or replace type A as object (
  x_ number, 
  y_ varchar2(10),
  member procedure to_upper
);
/

---

Look in DBA_DEPENDENCIES, ALL_DEPENDENCIES, or USER_DEPENDENCIES as appropriate:

SELECT OWNER, NAME, TYPE
  FROM DBA_DEPENDENCIES 
 WHERE REFERENCED_OWNER = [type owner]
   AND REFERENCED_NAME  = [type name]
   AND REFERENCED_TYPE  = 'TYPE'
/

---

Do not use DROP with FORCE, as it will automatically modify tables (delete columns) and god know what else to validate everything. Use something like:

ALTER TYPE type_name DROP ATTRIBUTE attr_name INVALIDATE;
ALTER TYPE type_name ADD ATTRIBUTE attr_name varchar2(50) CASCADE;

This will work on types with table/type dependencies.

---

If you've used the type in a table you should be able to see it through a query like :

select * from all_tab_columns
where data_type_owner not in ('SYS');

But I'd start off looking at Alex's suggestion of using ALTER TYPE

---

I'm sure it's available in the data dictionary somewhere, but not sure where off-hand; and you're likely to have lots of dependencies that aren't easy to resolve. But you may be able to modify the existing type instead: http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_4002.htm

There's also a FORCE option but that could still invalidate dependent objects.

---

This is caused by basic restrictions in Oracle, another reason not to use oracle types in the database.

For 'TYPE' dependencies you can:

1. DROP TYPE mytype FORCE;
2. Then re-create the type mytype and type body
3. Then DROP TYPE mytype_dependent FORCE;
4. Then re-create the mytype_dependent type and type body.
5. Repeat for all dependent and referenced types.

Note: The items 3..5 are required because dependent types cannot be automatically recompiled or manually compiled 'in place'.

For 'TABLE' dependencies you must:

Use the guidance in the article here, where it talks about the three relevant scenarios.

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

from the Annals of Oracle's Improbable Errors blog