Accessing OBJECT TYPE using PL/SQL - Oracle

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.