I am trying to write a simple procedure for getting a better understanding of Associative Arrays and all I am doing is to extract the employee names from the employees table.
Schema used: HR schema given by Oracle
Here's my packaged procedure for this:
CREATE OR REPLACE PACKAGE xtractempdetails
IS
TYPE empspecificfirstname_rt IS TABLE OF EMPLOYEES.first_name%TYPE INDEX BY BINARY_INTEGER;
empspecificfname_aa empspecificfirstname_rt;
PROCEDURE getempdetails(p_InNumempid IN EMPLOYEES.employee_id%TYPE,
p_Outtypeempfname OUT empspecificfirstname_rt
--, p_Outtypeempfname OUT empspecificfname_aa ====> gives me an error: PLS-00488:EMPSPECIFICFNAME_AA must be a type
);
END xtractempdetails;
CREATE OR REPLACE PACKAGE BODY xtractempdetails IS
PROCEDURE getempdetails(p_InNumempid IN EMPLOYEES.employee_id%TYPE,
p_Outtypeempfname OUT empspecificfirstname_rt
)
IS
CURSOR cur_empfirst_name IS
SELECT first_name
FROM employees
WHERE employee_id = p_InNumempid;
lv_NumCount NUMBER(3);
BEGIN
OPEN cur_empfirst_name;
FETCH cur_empfirst_name INTO p_Outtypeempfname;
lv_NumCount:= p_Outtypeempfname.COUNT;
DBMS_OUTPUT.put_line('Total Number of Employees are : ' ||lv_NumCount);
FOR indx IN 1..lv_NumCount
LOOP
p_Outtypeempfname(indx).first_name:=p_Outtypeempfname(indx).first_name;
END LOOP;
CLOSE cur_empfirst;
END;
When I try to compile I keep getting an error:
PLS-00597:expression 'p_Outtypeempfname' in the INTO list is of wrong type.
Any reason why? Is it that I need to use BULK COLLECT for this? Can I not do this with an explicit cursor?