CREATE OR REPLACE PACKAGE HR.xtractempdetails 
IS

    TYPE empspecificfirstname_rt IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;--
    -- TYPE empspecificfirstname_rt IS TABLE OF EMPLOYEES.first_name%TYPE INDEX BY BINARY_INTEGER;
     empspecificfname_aa     empspecificfirstname_rt;


-- Public Procedures

/***********************************************************************
NAME      :   getempdetails
TYPE      :   PROCEDURE
INPUT     :   Employee id
OUTPUT    :   All employee details for a specific employee id delcared 
              in empsepecificdetails_rt(empsepecificdetails_aa)
DESCRIPTION : This procedure gets all employee details from HR.employees
              table for all employees


************************************************************************/

      
PROCEDURE getempdetails(p_InNumempid        IN EMPLOYEES.employee_id%TYPE,
                        p_Outtypeempfname   OUT empspecificfirstname_rt
                        );
                        --p_Outtypeemplname   OUT empspecificlastname_rt,
                       -- p_Outtypeempemailid OUT empspecificemail_rt);      
END xtractempdetails;
/

CREATE OR REPLACE PACKAGE BODY HR.xtractempdetails
IS

PROCEDURE getempdetails(p_InNumempid        IN EMPLOYEES.employee_id%TYPE,
                        p_Outtypeempfname   OUT empspecificfirstname_rt
                        )
                        
IS

BEGIN

         SELECT first_name
         BULK COLLECT INTO  p_Outtypeempfname 
          FROM  employees 
          WHERE employee_id=p_InNumempid;
          
        FOR idx IN p_Outtypeempfname.FIRST..p_Outtypeempfname.LAST
        LOOP
            p_Outtypeempfname(idx).first_name;
        
        END LOOP;       
          


END xtractempdetails;
/