oracle-databasealteruser-defined-types

Need to update the ALTER TYPE object


I have created an TYPE object named example1 using following query.

create or replace type example1 as  object
                          (emp_name varchar2(32)
                          ,emp_location varchar2(32)
                          )

Later I tried to change the length of the variable emp_location to varchar(36) and I tried multiple ways

ALTER TYPE Point ADD ATTRIBUTE emp_location varchar2(36) CASCADE;

and I end with the following object.

create or replace type example1 as  object
                          (emp_name varchar2(32)
                          ,emp_location varchar2(32)
                          )

 Alter type example1 modify attribute emp_location varchar(36) cascade
 Alter type example1 modify attribute emp_location varchar2(37) cascade
 ALTER TYPE example1 DROP ATTRIBUTE emp_location INVALIDATE
 ALTER TYPE example1 ADD ATTRIBUTE emp_location varchar2(38) CASCADE

Now I want to remove the following

 Alter type example1 modify attribute emp_location varchar(36) cascade
 Alter type example1 modify attribute emp_location varchar2(37) cascade
 ALTER TYPE example1 DROP ATTRIBUTE emp_location INVALIDATE
 ALTER TYPE example1 ADD ATTRIBUTE emp_location varchar2(38) CASCADE

How can I do that ?


Solution

  • " I wanted to change the emp_location varchar2(32) to emp_location varchar2(36)"

    Well one of the lines of code you posted does that (after correcting the Type name):

    SQL> create or replace type example1 as  object
      2                       (emp_name varchar2(32)
      3                       ,emp_location varchar2(32)
      4                       )   
      5  /
    
    Type created.
    
    SQL> alter type example1 modify attribute emp_location varchar2(36) cascade
      2  /
    
    Type altered.
    
    SQL> desc example1
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     EMP_NAME                                           VARCHAR2(32)
     EMP_LOCATION                                       VARCHAR2(36)
    
    SQL>