Tom,

I have a situation where I need to compile the objects
in the order. Please consider the following example,

SQL > CREATE OR REPLACE TYPE object_type1
AS OBJECT ( col1 NUMBER )
/
Type created.

SQL > CREATE OR REPLACE TYPE nested_table1
AS TABLE OF ( col1 object_type1 )
/
Type created.

SQL > CREATE OR REPLACE TYPE object_type2
AS OBJECT ( col1 nested_table )
/
Type created.


For example, if I want to do any modification to the
object_type1 object..

SQL > CREATE OR REPLACE TYPE object_type1 (col1 number(4))
/
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents

So I have to drop all the dependents and recreate all of them in the order.

Or

SQL > DROP TYPE object_type FORCE
/
Type Dropped.

SQL > CREATE OR REPLACE TYPE object_type1 (col1 number(4))
/
Type created.

If I drop the object using FORCE option, and re-creating that object alone gives internal error problem when the dependent objects are accessed from the XML application.

1) Is there any way to compile a object type which has dependent objects without droping.

2) Do you have a sample sql which can get all the nested dependents in the for a given object. (ie object_type3 is dependent on nested_table1 which is dependent on object_type1.)

Thanks
Pushparaj

Tom,

When I download the script, run against Sys, then try and use from another schema, I get:

SQL> exec sys.compile_schema('ABC');
alter PACKAGE BODY DW.ABC compile body
ORA-00922: missing or invalid option

(repeated for a whole series of packages)

I suspect its because the script selects the object type, when for packages it should just be:

alter PACKAGE DW.ABC compile body ???

I modified the script to do a decode on object type up-front and if it is package body replace it with package.

Did I miss something - that seems to work now ? 

Tom,

You have mentioned in the followup:
--
1) upgrade to 9i where types are modifiable (type evolution is fully supported).
--

Could you please share the syntax/documentation URL? Would it work in 9.0.2?

I would like to avoid the error message "ORA-02303" while using CREATE OR REPLACE.

Thanks in advance,

JD