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> create table people
2 ( name varchar2(10),
3 home_address address_type,
4 work_address address_type
5 )
6 /
Table created.
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> set echo on
SQL>
SQL> declare
2 l_prev_addresses address_Array_Type;
3 begin
4 select p.previous_addresses into l_prev_addresses from people p
5 where p.name = 'Tom Kyte';
6
7 l_prev_addresses.extend;
8 l_prev_addresses(l_prev_addresses.count) := Address_Type( '1 Street', null,'Reston', 'VA', 45678 );
9
10 update people set previous_addresses = l_prev_addresses where name = 'Tom Kyte';
11 end;
12 /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4
SQL>
SQL> select name, prev.city, prev.state, prev.zip_code from people p, table( p.previous_addresses ) prev
2 /
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.