Home arrow Oracle arrow 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.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL: OBJECT and OBJECT
  2. Accessing OBJECT TYPE using PL/SQL
  3. Working with column based OBJECTs
  4. Accessing column based OBJECTs in PL/SQL
By: Jagadish Chatarji
Rating: starstarstarstarstar / 30
June 14, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More Oracle Articles          >>> More By Jagadish Chatarji
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: