Mega Code Archive
Query table column with user defined type
<![CDATA[
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> 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> column "HOME_ADDRESS.STATE" format a20
SQL> column "WORK_ADDRESS.STATE" format a20
SQL> select name, P.home_address.state, P.work_address.state from people P
2 /
NAME HOME_ADDRESS.STATE WORK_ADDRESS.STATE
---------- -------------------- --------------------
Tom Kyte VA CA
SQL> drop table people;
Table dropped.
SQL> drop type Address_Type;
Type dropped.
]]>