Returning PL/SQL TABLE from a FUNCTION to a PROCEDURE - Oracle

In the previous section of this article, I used a PL/SQL TABLE as a parameter for the PROCEDURE.  Now, I shall revise the above stated program in such a way that I will use both FUNCTION and PROCEDURE together, communicating with PL/SQL TABLEs.  Let us consider the following example:

declare

    type t_emptbl is table of emp%rowtype;
    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;

    function getEmployeeList return t_emptbl is

        v_emptbl          t_emptbl;                    

        begin

            select * bulk collect into v_emptbl from emp;

            return v_emptbl;

        end;

     BEGIN

        dispEmp(getEmployeeList);

     END;

Let us examine the two sub-programs separately.

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;

It just receives PL/SQL TABLE as parameter and displays all the information available in it.

function getEmployeeList return t_emptbl is

         v_emptbl          t_emptbl;                     

    begin

         select * bulk collect into v_emptbl from emp;
         return v_emptbl;

     end;

The above function ‘getEmployeeList’ executes the SELECT statement and the output is gathered into ‘v_emptbl’, which is returned back (or pushed out).

dispEmp(getEmployeeList);

The above is the most important statement in the program.  It looks very simple, but a lot of logic is embedded in it.  First of all, it executes the FUNCTION ‘getEmployeeList’.  That FUNCTION returns a PL/SQL TABLE, which will be sent as a parameter to the procedure ‘dispEmp’ automatically.  We need not actually store the PL/SQL TABLE returned by the FUNCTION.