Accessing column based OBJECTs in PL/SQL - Oracle

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.