Database Interaction with PL/SQL, Working with TABLE in Sub-programs, Parameter Modes

Please note that all of the examples in this series have been tested only with Oracle 10g.  I didn’t really test them with all the previous versions of Oracle.  I suggest you refer to the documentation of the respective version you are using, if any of the programs failed to execute.

Passing PL/SQL TABLE to PROCEDURE

In my previous article, I stopped with a simple example of working on a PL/SQL TABLE together with sub programs.  Now, this will be a continuation of the same.  In this section, we will pass a PL/SQL TABLE itself as an argument to the procedure.  Let us consider the following program:

declare

Let me explain the above program part by part.

type t_emptbl is table of emp%rowtype;

The above statement defines a new data type ‘t_emptbl’ as an in-memory table representation.  This means it can store any number of rows (just like a table) from the table ‘emp’ (as we included ‘emp%rowtype’) in memory (without having any relation to the physical database).  Make sure that it is only a data type.  If you know the C language, you can think of it as a structured array.

v_emptbl          t_emptbl;

The above statement declares a variable ‘v_emptbl’ which is authorized to store information based on the table type defined by ‘t_emptbl’.  The above declarations can be used globally in the entire program, including sub-programs.

select * bulk collect into v_emptbl from emp;

This is the most important statement; it does all the operations.  If you remove BULK COLLECT, it would raise an error, because as you cannot work with more than one row at a time.  The BULK COLLECT lets you fetch any number of rows based on the SELECT statement issued, and store all of them in a TABLE typed variable.  From the above statement, it is quite clear that we are retrieving all rows from the table ‘emp’ and collecting them into the TABLE typed variable ‘v_emptbl’. 

Now the next issue is to fetch all those rows of values and display them back on the screen.  In general, each and every row in the TABLE typed variable is indexed starting from one (just like an array).  We retrieve each and every row with that index by using a FOR loop.

            procedure dispEmp(p_emptbl t_emptbl) is

         begin

             for i in p_emptbl.first .. p_emptbl.last

                 loop

                     dbms_output.put_line(p_emptbl(i).ename ||
                     ' earns ' || p_emptbl(i).sal);

                 end loop;

         end;

The above part is a procedure named ‘dispEmp’ containing a FOR loop, which starts from the first index of the TABLE typed variable ‘p_emptbl’ and ends at the last index of the same.  An entire table gets copied from the main program to the parameter ‘p_emptbl’ (which is actually TABLE type).  Here ‘first’ and ‘last’ are keywords which can be used with any TABLE typed variables.  We retrieve each and every field of information by using the notation TABLE(INDEX).FIELDNAME (which is implemented using DBMS_OUTPUT statement).