Function to Retun Multiple Values

--OBJECT TYPE: The Definition of the TYPE contains a comma separated list of attributes /properties.
--  defined in the same as Package variables, and member functions/procedures .
 
-- 1.Object Creation

CREATE OR REPLACE TYPE EMP_OBJ_TYPE
AS OBJECT
(
FNAME VARCHAR2(150),
LNAME VARCHAR2(150),
DEPT_NAME VARCHAR2(50)
);

--2. Nested table creation based on the object.

CREATE OR REPLACE TYPE EMP_TBL_TYPE
IS TABLE OF EMP_OBJ_TYPE;

--3.FUNCTION

CREATE OR REPLACE FUNCTION F_RET_VAL (P_EMP_ID NUMBER)
RETURN EMP_TBL_TYPE
IS
P_FNAME VARCHAR2(150);
P_LNAME VARCHAR2(150);
P_DEPT_NAME VARCHAR2(50);
--nested table variable Declaration and initialization
EMP_DETAILS EMP_TBL_TYPE :=EMP_TBL_TYPE();
BEGIN
--Extending Nested Table
EMP_DETAILS.extend();
select First_name , Last_name , Department_name
into P_FNAME, P_LNAME,P_DEPT_NAME
From employees e, departments d
where e.department_id = d.department_id
and e.employee_id = P_EMP_ID;

--Using an object constructor to insert the data into the nested table.
EMP_DETAILS(1) := EMP_OBJ_TYPE (P_FNAME,P_LNAME, P_DEPT_NAME);
/*SELECT EMP_OBJ_TYPE (FIRST_NAME, LAST_NAME, DEPARTMENT_NAME )
BULK COLLECT INTO EMP_DETAILS
FROM employees e , departments d
where e.department_id=d.department_id
and e.employee_id=p_emp_id;
*/
RETURN EMP_DETAILS;
END;

show error

SELECT * FROM TABLE (F_RET_VAL(100));