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