Oracle PL/SQL/Object Oriented Database/Alter Type

Alter a user-defined type

   <source lang="sql">

SQL> SQL> create or replace

 2  type person as object(
 3   first_name varchar2(100),
 4   last_name varchar2(100) )
 5  /

Type created. SQL> SQL> SQL> SQL> alter type person

 2  add attribute dob date
 3  cascade not including table data
 4  /

Type altered. SQL>

</source>
   
  

ALTER TYPE to add more methods and attributes

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE TYPE Dummy AS OBJECT (

 2    f1 NUMBER,
 3    f2 NUMBER,
 4    MEMBER PROCEDURE Method1(x IN VARCHAR2),
 5    MEMBER FUNCTION Method2 RETURN DATE
 6  );
 7  /

Type created. SQL> SQL> CREATE OR REPLACE TYPE BODY Dummy AS

 2    MEMBER PROCEDURE Method1(x IN VARCHAR2) IS
 3    BEGIN
 4      NULL;
 5    END Method1;
 6
 7    MEMBER FUNCTION Method2 RETURN DATE IS
 8    BEGIN
 9      RETURN SYSDATE;
10    END Method2;
11  END;
12  /

Type body created. SQL> SQL> COLUMN object_name FORMAT a20 SQL> SQL> SELECT object_name, object_type, status

 2    FROM user_objects
 3    WHERE object_name = "DUMMY";

OBJECT_NAME OBJECT_TYPE STATUS

---

------------------- -------

DUMMY TYPE VALID DUMMY TYPE BODY VALID SQL> SQL> ALTER TYPE Dummy REPLACE AS OBJECT (

 2    f1 NUMBER,
 3    f2 NUMBER,
 4    MEMBER PROCEDURE Method1(x IN VARCHAR2),
 5    MEMBER FUNCTION Method2 RETURN DATE,
 6    MEMBER PROCEDURE Method3
 7  );

Type altered. SQL> SQL> SQL> SELECT object_name, object_type, status

 2    FROM user_objects
 3    WHERE object_name = "DUMMY";

OBJECT_NAME OBJECT_TYPE STATUS

---

------------------- -------

DUMMY TYPE VALID DUMMY TYPE BODY INVALID SQL> SQL>

</source>
   
  

Alter user-defined type to add a new attribute

   <source lang="sql">

SQL> SQL> SQL> create or replace type person as object(

 2   first_name varchar2(100),
 3   last_name varchar2(100) );
 4  /

SQL> SQL> SQL> alter type person add attribute ssn varchar2(11) INVALIDATE; Type altered. SQL> SQL> drop type person; Type dropped. SQL> SQL>

</source>