Recent versions of Oracle indicate that you can alter a type on the fly without having to recreate it. Here's a simple example of how to do it, because as you can see from the first demo below, its the perhaps intutive syntax of CREATE OR REPLACE
SQL> create type T1 as object ( x number, y number ) not final; 2 / Type created. SQL> create type T2 as table of T1; 2 / Type created. SQL> create or replace type T1 as object ( x number, y number, z number ); 2 / create or replace type T1 as object ( x number, y number, z number ); * ERROR at line 1: ORA-02303: cannot drop or replace a type with type or table dependents
what you need is the ALTER TYPE clause
SQL> alter type T1 add attribute ( z number ); alter type T1 add attribute ( z number ) * ERROR at line 1: ORA-22312: must specify either CASCADE or INVALIDATE option SQL> alter type T1 add attribute ( z number ) cascade; Type altered.
Update: Thanks to Camilo for this additional information
Hi,
There's an issue with altering type though -- your example of how to alter a type withour dropping and recreating it.
I ran into the same problem with 9ir2 and in 10g (Release 10.2.0.1.0), and it seems we need to reconnect to go around the problem.
Regards,
Milo
SQL> create type T1 as object ( x number, y number ) not final; 2 / Type created. SQL> desc t1; t1 is NOT FINAL Name Null? Type ----------------------------------------- -------- ---------------------------- X NUMBER Y NUMBER SQL> alter type t1 add attribute (z number) cascade; Type altered. SQL> desc t1; ERROR: ORA-22337: the type of accessed object has been evolved SQL> create type T2 as table of T1; 2 / Type created. SQL> desc t2; t2 TABLE OF T1 ERROR: ORA-22337: the type of accessed object has been evolved -- Check the error. SQL> !oerr ora 22337 22337, 00000, "the type of accessed object has been evolved" // *Cause: The type of the accessed object has been altered and the client's // object is based on an earlier type definition. // *Action: The user needs to exit application and modify application to // accommodate the type change. // From SQL/PLUS, reconnect and resubmit statement. -- Reconnect SQL> connect scott/tiger Connected. SQL> desc t1; t1 is NOT FINAL Name Null? Type ----------------------------------------- -------- ---------------------------- X NUMBER Y NUMBER Z NUMBER SQL> desc t2; t2 TABLE OF T1 T1 is NOT FINAL Name Null? Type ----------------------------------------- -------- ---------------------------- X NUMBER Y NUMBER Z NUMBER