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