Alter type to add member function : Alter Type « Object Oriented « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Object Oriented
3. Alter Type

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
*