Hi, I am trying to pass a PLSQL collection as parameter in a packaged procedure but encountering error while compiling the code. Kindly advise what I am missing to make it work.
DB version 19c and Oracle scott schema emp table can be used for the sample data.
CREATE OR REPLACE PACKAGE xxemp AS
TYPE rt_emp IS RECORD (
ename VARCHAR2(500)
, empno NUMBER
, mgr BOOLEAN
);
TYPE tt_emp IS
TABLE OF rt_emp INDEX BY PLS_INTEGER;
PROCEDURE main (
p_input IN VARCHAR2
);
PROCEDURE logic (
p_params IN tt_emp
);
END xxemp;
/
CREATE OR REPLACE PACKAGE BODY xxemp AS
PROCEDURE main (
p_input IN VARCHAR2
) AS
CURSOR c_emp IS
SELECT ename
, empno
, CASE
WHEN mgr IS NULL THEN 'N'
ELSE 'Y'
END mgr
FROM emp
WHERE ename = p_input;
idx NUMBER := 1;
l_params tt_emp;
BEGIN
FOR idx_emp IN c_emp LOOP
l_params(idx).ename := idx_emp.ename;
l_params(idx).empno := idx_emp.empno;
l_params(idx).mgr := idx_emp.mgr;
BEGIN
logic(l_params);
END;
idx := idx + 1;
EXIT WHEN c_emp%notfound;
END LOOP;
END main;
PROCEDURE logic (
p_params IN tt_emp
) AS
BEGIN
dbms_output.put_line(p_params.ename
|| chr(10)
|| p_params.empno
|| chr(10)
|| p_params.mgr);
END logic;
END xxemp;
/
Package Body XXEMP compiled
LINE/COL ERROR
--------- -------------------------------------------------------------
23/13 PL/SQL: Statement ignored
23/44 PLS-00382: expression is of wrong type
36/9 PL/SQL: Statement ignored
36/39 PLS-00302: component 'ENAME' must be declared
Errors: check compiler log