Altering types


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