Working with column based OBJECTs - Oracle

In the previous two sections, we were actually working with rows of OBJECTs within a table.  This means that every row within the table is considered an OBJECT (not individual values within columns).  But they appear as if they directly got stored in the form of column values.  That is the beauty of OBJECT based tables!

In this section, we will store OBJECTs in columns (rather than in the form of rows).  Let us prepare our scripts accordingly.  Consider the following:

CREATE or REPLACE TYPE t_Address AS OBJECT
(
    street    varchar2(20),
    city      varchar2(20),
    state     varchar2(20),
    country   varchar2(20)
);
/

The above script creates an OBJECT type named ‘t_Address’ with fields called street, city, state and country.

create table employees
(
    ename           varchar2(20),
    OffAddress      t_Address,
    ResAddress      t_Address
);

The above script creates new table employees (make sure you delete the old one) with three columns namely ename, OffAddress and ResAddress.  The most important are the last two columns which are based on the OBJECT TYPE t_Address.  This means each of the two columns can have their own grouped information of street, city, state and country without having any relation between them.  They will be treated as two different unrelated columns of information which will never shared data.  But they got derived from the same OBJECT TYPE.  You can issue the following type of INSERT statements for the table created above.

insert into employees
values
(
    'jag',
    t_address('street 1','Kangar','Perlis','Malaysia'),
    t_address('street 2','BVRM','Andhra Pradesh','India')
);

Instead of just simply giving ‘SELECT * FROM employees’, I suggest you to issue in the following manner:

select e.ename, e.OffAddress.city, e.ResAddress.city from employees e

Observe the beauty of table alias together with column name and finally the member within the column (city).  In that way you can access any member of object with the respective column name together with alias.  In the same manner, you can also practice other DML commands with the same table.