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 member function toString return varchar2
9 )
10 /
Type created.
SQL>
SQL>
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 end;
17 /
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> declare
2 l_home_address address_type;
3 l_work_address address_type;
4 begin
5 l_home_address := Address_Type( '1 Street', null,'R', 'VA', 45678 );
6 l_work_address := Address_Type( '1 Way', null,'R', 'CA', 23456 );
7
8 insert into people( name, home_address, work_address )values ( 'Tom Kyte', l_home_address, l_work_address );
9 end;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select name, p.home_address.toString()
2 from people P
3 /
NAME
----------
P.HOME_ADDRESS.TOSTRING()
--------------------------------------------------------------------------------
Tom Kyte
1 Street R, VA 45678
SQL> drop type Address_Type;
drop type Address_Type
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents
SQL>
SQL> drop table people;
Table dropped.
SQL>
SQL>
SQL>