Home arrow Oracle arrow Database Interaction with PL/SQL: OBJECT and OBJECT

Database Interaction with PL/SQL: OBJECT and OBJECT

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

Please note that all the examples in this series have been tested only with Oracle 10g, not with all the previous versions of Oracle.  I suggest you to refer the documentation of respective version you are using if any of the programs failed to execute.

Introduction to OBJECT TYPE:

It is worthwhile to introduce the concept of OBJECT here, as we can also work with OBJECTs in PL/SQL pleasantly.  I will not go much into the depth of OOPS with MEMBER methods etc at this moment.  My up-coming articles will look into the depth of OOPS in Oracle 10g.  For now we will just concentrate on minimum basics of OBJECT together with PL/SQL.

For the time being, just consider OBJECT type as similar to RECORD type in PL/SQL (RECORD was explained in part-2 and part-3 of my articles).  RECORD type works only in PL/SQL.  But OBJECT type gets stored in database and can be used in both SQL and PL/SQL (without redefining it in PL/SQL).

Let us consider the following example.

CREATE TYPE t_experience AS OBJECT
(
            Ename          varchar2(20),
            CompanyName    varchar2(20),
            Position       varchar2(20),
            NoOfYears      number(2)
);
/

The above script just creates only an OBJECT TYPE (not a table).  Remember it is TYPE (which means something like a datatype).  The OBJECT TYPE can be used to create a table based on its definition.

CREATE TABLE Employees OF t_experience;

The above statement creates a new table named ‘Employees’ with exactly the same structure of ‘t_experience’.  The following statement inserts a row based on the OBJECT TYPE structure.

insert into employees values
 
('jag','xyz company','software engineer',5);

I don’t think you would find any difference between above statement and ordinary INSERT, as they work the same way.  Even though the above INSERT is valid, for better readability, it is always suggested to issue the above statement as follows:

insert into employees values (t_experience('jag','xyz
  company','software engineer',5));

The only difference is that we are enclosing all the values into the specification of OBJECT TYPE ‘t_experience’.  All the other DML commands (INSERT, UPDATE, DELETE and SELECT) can be issued just like ordinary SQL statements without any difference.



 
 
>>> 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: