Database Interaction with PL/SQL: OBJECT and OBJECT

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.