HomeOracle 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.
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).
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.
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.