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.

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.

{mospagebreak title=Accessing OBJECT TYPE using PL/SQL}

We created OBJECT TYPE in the previous section.  Now let us see how we can access the information of OBJECT TYPE using PL/SQL.

Let us consider the following example.

declare
    v_experience    t_experience;
    v_ename             varchar2(20) := ‘&ename';
begin
    select value(e) into v_experience
    from employees e where ename = v_ename;
    dbms_output.put_line(‘Company: ‘ || v_experience.companyname);
    dbms_output.put_line(‘Years: ‘ || v_experience.NoOfYears);
end;

From the above program, ‘v_experience’ is declared based on the OBJECT TYPE ‘t_experience’.  The most important statement to understand from the above program is the following:

select value(e) into v_experience
from employees e where ename = v_ename;

The table ‘employees’ is aliased as ‘e’ and we are using ‘value’ (in combination with alias) function to return values in the form of an OBJECT (instead of values).  The OBJECT returned by VALUE is placed into ‘v_experience’ variable and I hope the rest is same.

Another important issue to remember is that, it is not compulsory to return VALUE in the form of object.  We can also use individual variables as the following:

declare
    v_CompanyName    varchar2(20);
   
v_NoOfYears      number(4);
    v_ename          varchar2(20) := ‘&ename';
begin
    select CompanyName, NoOfYears into v_CompanyName, v_NoOfYears
    from employees e where ename = v_ename;
    dbms_output.put_line(‘Company: ‘ || v_CompanyName);
    dbms_output.put_line(‘Years: ‘ || v_NoOfYears);
end;
/

The above can also be rewritten using TYPE with RECORD to retrieve OBJECT based TABLE information as following:

declare
    TYPE t_experience is RECORD
    (
        CompanyName    varchar2(20),
        Years          varchar2(20)
    );
    v_experience    t_experience;
   
v_ename           varchar2(20) := ‘&ename';
begin
    select CompanyName,NoOfYears  into v_experience
    from employees where ename = v_ename;
    dbms_output.put_line(‘Company: ‘ || v_experience.CompanyName);
    dbms_output.put_line(‘Years: ‘ || v_experience.Years);
end;
/

Till now, all the above programs are working with only single rows of OBJECTs.  What about more number of rows?  We can follow the same approach explained in Part-2 or Part-3 of this series and modify a bit as following:

declare
    type tbl_experience is table of t_experience;
    v_emptbl          tbl_experience;
    i                       integer;
begin
    select value(e) bulk collect into v_emptbl from employees e;
    i := v_emptbl.first;
    while i is not null
    loop
        dbms_output.put_line(v_emptbl(i).CompanyName);
        i := v_emptbl.next(i);
    end loop;
end;
/

We must understand that there exists several number of ways to retrieve the OBJECT based TABLE information.  We need to follow certain methods appropriate to the situation rather than confusing ourselves with all of them.

{mospagebreak title=Working with column based OBJECTs}

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.

{mospagebreak title=Accessing column based OBJECTs in PL/SQL}

We will use the same concept in previous section, but with PL/SQL.  Let us consider the following code.

declare
    v_OffAddress   t_Address;
    v_ename        varchar2(20) := ‘jag';
begin
    select OffAddress into v_OffAddress
    from employees where ename = v_ename;
    dbms_output.put_line(v_OffAddress.city);
end;
/

I hope the above program looks very much familiar to that of PL/SQL RECORD fetching.  In this case, VALUE is not necessary as, we are not converting the whole row as OBJECT.  Let us consider another example:

declare
    TYPE tbl_Address is TABLE of t_Address index by pls_integer;
    v_tblAddress    tbl_Address;
    v_ename                       varchar2(20) := ‘jag';
begin
    select OffAddress, ResAddress into v_tblAddress(0), v_tblAddress(1)
    from employees where ename = v_ename;
    dbms_output.put_line(v_tblAddress(0).city);
    dbms_output.put_line(v_tblAddress(1).city);
end;
/

The above program introduces several new concepts (even though it looks almost the same as previous examples).  Let me explain part by part.

TYPE tbl_Address is TABLE of t_Address index by pls_integer;

Till now, I never explained the part ‘index by…’.  According to the above statement, I am creating a new data type ‘tbl_address’ which can hold any number of records (just in the form of a table) based on the OBJECT TYPE ‘t_Address’.  The extension ‘index by pls_integer’ creates an index based table.  This means the PL/SQL table gets indexed based on the records populated.  The ‘pls_integer’ can also be replaced using ‘binary_integer’.  Now each and every row within the table are accessible using an index (or counter).

v_tblAddress    tbl_Address;

I hope you can understand this.  We are just creating a variable ‘v_tblAddress’ based on the PL/SQL TABLE type ‘tbl_Address’. 

select OffAddress, ResAddress into v_tblAddress(0), v_tblAddress(1)
from employees where ename = v_ename;

The above SELECT statement retrieves the objects available in the two columns (OffAddress and ResAddress) and places them within ‘v_tblAddress’ at two different locations i.e., 0 and 1.

dbms_output.put_line(v_tblAddress(0).city);
dbms_output.put_line(v_tblAddress(1).city);

Finally, we display the cities of both addresses as shown above.

It is not exactly necessary to follow the same method as above.  But this way I can introduce the index based PL/SQL tables along with OBJECTs together.  If necessary we can also use BULK COLLECT to retrieve more than one row.  I leave this to you, the readers, as an exercise.

[gp-comments width="770" linklove="off" ]

chat