How can I pass a pl/sql table as a parameter using PL/SQL language?

By: John Kazerooni.

The following is a package named "DISPLAY_EMP" that contains a procedure named "RECS." The procedure will populate a pl/sql table and then pass the table as an argument parameter.

The following is the package specification that contains a pl/sql table.  

CREATE OR REPLACE PACKAGE display_emp

 AS

             TYPE px_emprec

             IS RECORD

                         (DEPARTMENT             CHAR(5),

                          EMPLOYEE     NUMBER(9),

                          FIRST_NAME   CHAR(15),

                          LAST_NAME    CHAR(30),

                          POSITION        CHAR(12),

                          PROCESS_LEVEL       CHAR(5),

                          SUPERVISOR  CHAR(10),

                          SUPERVISOR_IND       CHAR(10),

                          email_address  CHAR(50));

            TYPE px_emptab

             IS TABLE OF px_emprec

                         INDEX BY BINARY_INTEGER;

            PROCEDURE recs

             (p_emptab OUT px_emptab,

              p_error OUT VARCHAR2);

 END;

 /

This package contains a procedure that populates the table and pass it as a parameter.

 CREATE OR REPLACE PACKAGE BODY display_emp

 AS

 PROCEDURE recs

             (p_emptab OUT px_emptab,

              p_error OUT VARCHAR2)

 as

             -- This stored procedure will read the following

             -- queries and populated the reasult in a

             -- parameter table so can be called from

             -- any programming languages.

            -- declare the cursor

             CURSOR c_emp IS

             SELECT lawson8.employee.DEPARTMENT,                   

                         lawson8.employee.EMPLOYEE,

                         lawson8.employee.FIRST_NAME,                      

                         lawson8.employee.LAST_NAME,

                         lawson8.employee.POSITION,               

                         lawson8.employee.PROCESS_LEVEL,

                         lawson8.employee.SUPERVISOR,                     

                         lawson8.employee.SUPERVISOR_IND,

                         lawson8.ZZUSERID.email_address

             FROM lawson8.employee, lawson8.ZZUSERID

             WHERE lawson8.employee.employee = lawson8.ZZUSERID.empid

             AND (   lawson8.employee.EMP_STATUS = 'AF' OR

                         lawson8.employee.EMP_STATUS = 'AP' OR

                         lawson8.employee.EMP_STATUS = 'AT')

             AND NOT lawson8.employee.LAST_NAME LIKE '%VACANT%'

             ORDER BY lawson8.employee.DEPARTMENT,

                         lawson8.employee.SUPERVISOR,

                         lawson8.employee.LAST_NAME,

                         lawson8.employee.FIRST_NAME;

-- declare the error message.

 v_error   VARCHAR2(30);

-- declare and initialize the index of the table.

 v_counter          BINARY_INTEGER :=0;

BEGIN

             -- initial counter..

             v_counter :=0;

             FOR this IN c_emp LOOP

                         -- populate the table.

                         v_counter := v_counter +1;

                         p_emptab(v_counter).department := this.department;

                         p_emptab(v_counter).employee := this.employee;

                         p_emptab(v_counter).first_name := this.first_name;

                         p_emptab(v_counter).last_name := this.last_name;

                         p_emptab(v_counter).position := this.position;

                         p_emptab(v_counter).process_level := this.process_level;

                         p_emptab(v_counter).supervisor := this.supervisor;

                         p_emptab(v_counter).supervisor_ind := this.supervisor_ind;

                         p_emptab(v_counter).email_address := this.email_address;

                         -- for test only...

                         --dbms_output.put_line (v_emptab(v_counter).department);

             -- end of the loop

             END LOOP;

             -- indicate a successful transaction.

             p_error := 'Okay';

EXCEPTION

 -- no data found

 WHEN no_data_found THEN

             v_error := 'No data found.';

             p_error := v_error;

-- Invalid input

WHEN invalid_number THEN

            v_error := 'Invalid number';

            p_error := v_error;

-- Anything else.

WHEN others THEN

            v_error := 'Other problem.';

            p_error := v_error;

END recs;

END;

/

This is a test to make sure that the package is working.

DECLARE

v_error               VARCHAR2(50);

v_counter          BINARY_INTEGER := 1;

v_mytable          display_emp.px_emptab;

BEGIN

            display_emp.recs(v_mytable, v_error);

            -- see the table was populated...

            dbms_output.put_line (v_error);   

            v_counter := 1;

            dbms_output.put_line (v_mytable.COUNT);

            WHILE v_counter <= v_mytable.count LOOP

            dbms_output.put_line (v_mytable(v_counter).department);

                        v_counter := v_counter + 1;

            END LOOP;

END;

/