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

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;

/