Oracle PL/SQL Tutorial/Object Oriented/Alter Type
Alter table to add varray type column
<source lang="sql">
SQL> SQL> create or replace type Address_Type
2 as object 3 ( street_addr1 varchar2(25), 4 street_addr2 varchar2(25), 5 city varchar2(30), 6 state varchar2(2), 7 zip_code number 8 ) 9 /
SQL> SQL> alter type Address_Type
2 REPLACE 3 as object 4 ( street_addr1 varchar2(25), 5 street_addr2 varchar2(25), 6 city varchar2(30), 7 state varchar2(2), 8 zip_code number, 9 member function toString return varchar2, 10 map member function mapping_function return varchar2 11 ) 12 /
Type altered. SQL> SQL> create or replace type body Address_Type
2 as 3 member function toString return varchar2 4 is 5 begin 6 if ( street_addr2 is not NULL ) 7 then 8 return street_addr1 || " " || 9 street_addr2 || " " || 10 city || ", " || state || " " || zip_code; 11 else 12 return street_addr1 || " " || 13 city || ", " || state || " " || zip_code; 14 end if; 15 end; 16 17 map member function mapping_function return varchar2 18 is 19 begin 20 return to_char( nvl(zip_code,0), "fm00000" ) || 21 lpad( nvl(city," "), 30 ) || 22 lpad( nvl(street_addr1," "), 25 ) || 23 lpad( nvl(street_addr2," "), 25 ); 24 end; 25 end; 26 /
Type body created. SQL> create table people
2 ( name varchar2(10), 3 home_address address_type, 4 work_address address_type 5 ) 6 /
SQL> create or replace type Address_Array_Type as varray(50) of Address_Type
2 /
SQL> SQL> alter table people add previous_addresses Address_Array_Type
2 /</source>
Alter type to add member function
<source lang="sql">
SQL> SQL> create or replace type Address_Type
2 as object 3 ( street_addr1 varchar2(25), 4 street_addr2 varchar2(25), 5 city varchar2(30), 6 state varchar2(2), 7 zip_code number 8 ) 9 /
SQL> SQL> alter type Address_Type
2 REPLACE 3 as object 4 ( street_addr1 varchar2(25), 5 street_addr2 varchar2(25), 6 city varchar2(30), 7 state varchar2(2), 8 zip_code number, 9 member function toString return varchar2, 10 map member function mapping_function return varchar2 11 ) 12 /
Type altered. SQL> SQL> create or replace type body Address_Type
2 as 3 member function toString return varchar2 4 is 5 begin 6 if ( street_addr2 is not NULL ) 7 then 8 return street_addr1 || " " || 9 street_addr2 || " " || 10 city || ", " || state || " " || zip_code; 11 else 12 return street_addr1 || " " || 13 city || ", " || state || " " || zip_code; 14 end if; 15 end; 16 17 map member function mapping_function return varchar2 18 is 19 begin 20 return to_char( nvl(zip_code,0), "fm00000" ) || 21 lpad( nvl(city," "), 30 ) || 22 lpad( nvl(street_addr1," "), 25 ) || 23 lpad( nvl(street_addr2," "), 25 ); 24 end; 25 end; 26 /
Type body created. SQL> create table people
2 ( name varchar2(10), 3 home_address address_type, 4 work_address address_type 5 ) 6 /
SQL> create or replace type Address_Array_Type as varray(50) of Address_Type
2 /
SQL> alter table people add previous_addresses Address_Array_Type
2 /
SQL> SQL> select object_type, object_name, decode(status,"INVALID","*","") status, tablespace_name
2 from user_objects a, user_segments b 3 where a.object_name = b.segment_name (+) 4 and rownum < 50 5 order by object_type, object_name 6 /
OBJECT_TYPE
OBJECT_NAME
S TABLESPACE_NAME - ------------------------------ FUNCTION CHITIME
FUNCTION IS_OVERDUE
FUNCTION RECEIVE_MESSAGE
FUNCTION RULERSTR
FUNCTION TABCOUNT
FUNCTION TO_MMSDDSYYYY_OR_NULL drop table people; OBJECT_TYPE
OBJECT_NAME
S TABLESPACE_NAME - ------------------------------ FUNCTION TO_NUMBER_OR_NULL
FUNCTION TRACEIT
FUNCTION VALUE_IN
PACKAGE CLASSPACKAGE
PACKAGE DATES
PACKAGE GENDER_TS OBJECT_TYPE
OBJECT_NAME
S TABLESPACE_NAME - ------------------------------
PACKAGE PARAMETERS
PACKAGE SCOPES
PACKAGE WORKER_TS
PACKAGE WORKER_TYPE_TS
PACKAGE WORKPLACE_TYPE_TS
PACKAGE BODY OBJECT_TYPE
OBJECT_NAME
S TABLESPACE_NAME - ------------------------------ CLASSPACKAGE
PACKAGE BODY DATES
PACKAGE BODY GENDER_TS
PACKAGE BODY PARAMETERS
PACKAGE BODY SCOPES
PACKAGE BODY WORKER_TS
OBJECT_TYPE
OBJECT_NAME
S TABLESPACE_NAME - ------------------------------ PACKAGE BODY WORKER_TYPE_TS
PACKAGE BODY WORKPLACE_TYPE_TS
PROCEDURE BULK_TEST
PROCEDURE CLOSECUR
PROCEDURE CREINDX
PROCEDURE DELETE_CUST
OBJECT_TYPE
OBJECT_NAME
S TABLESPACE_NAME - ------------------------------ PROCEDURE DROPIT
PROCEDURE DROP_IF_EXISTS
PROCEDURE FCREATE
PROCEDURE FLUSH
PROCEDURE GET_LINE
PROCEDURE P1 OBJECT_TYPE
OBJECT_NAME
S TABLESPACE_NAME - ------------------------------
PROCEDURE PRINTLN
PROCEDURE PUT_BOOLEAN
PROCEDURE P_RUN_INSERT
PROCEDURE ROW_AT_A_TIME_TEST
PROCEDURE RUNDDL
PROCEDURE OBJECT_TYPE
OBJECT_NAME
S TABLESPACE_NAME - ------------------------------ SAVESTRINGTOFILE
PROCEDURE SEND_MESSAGE
PROCEDURE WAIT
TABLE HAZARD_LEVEL_T
SYSTEM
TYPE DEBUG_O
TYPE BODY DEBUG_O
OBJECT_TYPE
OBJECT_NAME
S TABLESPACE_NAME - ------------------------------ VIEW DEPT20_V
VIEW SALES_BY_ATLAS_V
VIEW SALES_PER_PERSON_V
- </source>