/******************************************************************************
* Oracle Composite data types as Objects
******************************************************************************/

drop table PERSON;
drop type  name_type;

create type name_type as object
(
    lname varchar2(20),
    fname varchar2(20)
);

create table PERSON
(
    id   int,
    name name_type
);

insert into PERSON values (1, name_type('sultan','sam'));
insert into PERSON values (2, name_type('johnson','john'));

select p.id, p.name.lname, p.name.fname 
from PERSON p;					--must have a table alias



/******************************************************************************
* Oracle Multi-valued attributes as varray
******************************************************************************/
drop table CONTACT;
drop type  phone_array;

create type phone_array as varray(10) of varchar2(15);

create table CONTACT
(
    id       number,
    name     varchar2(50),
    phones   phone_array
);

insert into CONTACT values(1, 'Sam', phone_array('123-1111', '123-2222'));
insert into CONTACT values(2, 'Jim', phone_array('123-3333', '123-4444'));

select t1.id,  t1.name,  t2.column_value
from CONTACT t1, table(phones) t2;



/******************************************************************************
* Oracle Generalization superclass/subclass
******************************************************************************/
drop table HR_EMPLOYEE;
drop type  hourly_type;
drop type  emp_type;

create type emp_type as object
(
  empNo         number,
  empName       varchar2(50),
  empAddr       varchar2(100)
) not final;     

create type hourly_type under emp_type
(
    hourlyRate	number
);

create table HR_EMPLOYEE of hourly_type;

insert into HR_EMPLOYEE values (100, 'Sam Sultan', '123 Main St',  24.50); 

select * from HR_EMPLOYEE;