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

Accessing column based OBJECTs in PL/SQL - 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

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.



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