HomeOracle Page 3 - Database Interaction with PL/SQL: OBJECT and OBJECT
Working with column based OBJECTs - Oracle
Jagadish Chatarji has been writing about database interactions with Oracle PL/SQL. The last part examined using TABLE, RECORD and NESTED TABLES with PL/SQL. This one now introduces OBJECT TYPE in Oracle, and explains both SQL and PL/SQL ways of working with OBJECTs. This article is the fourth in the series.
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.
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.
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.