Alter table to add a user-defined type column : Object Table « Object Oriented « Oracle PL/SQL Tutorial

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  /

Type created.

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>
SQL>
SQL> create table people
  2  ( name           varchar2(10),
  3    home_address   address_type,
  4    work_address   address_type
  5  )
  6  /

Table created.

SQL>
SQL>
SQL> create or replace type Address_Array_Type
  2  as varray(50) of Address_Type
  3  /

Type created.

SQL>
SQL> alter table people add previous_addresses Address_Array_Type
  2  /

Table altered.

SQL> select name, prev.city, prev.state, prev.zip_code
  2    from people p, table( p.previous_addresses ) prev
  3   where prev.state = 'PA';

no rows selected

SQL> /

no rows selected

SQL>
SQL> drop table people;

Table dropped.

SQL> drop type Address_Array_Type;

Type dropped.

SQL> drop type address_type;

Type dropped.

32.9.Object Table
	32.9.1.	You can also use an object type to define an entire table, and the table is known as an object table.
	32.9.2.	Inserting a row into an object table
	32.9.3.	One-step INSERTs into a Table that Contains Row Objects
	32.9.4.	INSERT Values into a Table that Contains Row Objects (TCRO)
	32.9.5.	Insert data just as one would ordinarily do with a common SQL table:
	32.9.6.	SELECTing Individual Columns in Table that Contains Row Objects
	32.9.7.	Selecting Rows from the object Table
	32.9.8.	SELECT from the object-oriented table
	32.9.9.	Updating a Row in the object Table
	32.9.10.	UPDATE a Table that Contains Row Objects (TCRO)
	32.9.11.	Accessing the object table by reference
	32.9.12.	Create a table based on an Object with methods
	32.9.13.	Alter table to add a user-defined type column