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

Accessing OBJECT TYPE using 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.

  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



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.

    v_experience    t_experience;
    v_ename             varchar2(20) := '&ename';
    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);

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:

    v_CompanyName    varchar2(20);
v_NoOfYears      number(4);
    v_ename          varchar2(20) := '&ename';
    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);

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

    TYPE t_experience is RECORD
        CompanyName    varchar2(20),
        Years          varchar2(20)
    v_experience    t_experience;
v_ename           varchar2(20) := '&ename';
    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);

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:

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

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.

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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


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