"Patience
is the companion of wisdom."
-Saint
Augustine (354-430)
|
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;
/
|