PASSING A TABLE OF RECORDS AS PARAMETER TO A ORACLE PROCEDURE

--Creating the table type to pass as parameter(inside a package)

CREATE OR REPLACE PACKAGE package_test

IS
  
     --> Declaring a RECORD type with our datatypes 
     TYPE typeEmpRec IS RECORD (ID EMP.EMPNO%TYPE, EMP.ENAME%TYPE); 

     -->a table of type record(to pass mutiple records of above type)
     TYPE typeEmpTab IS TABLE OF typeEmpRec;   

     --> Proc to fill the collection type to send as parameter                  PROCEDURE getEmpRecords;

     --> Proc that accepts the collection as parameter                          PROCEDURE fetchRecords(emp_table typeEmpTab); 
  
END package_test;

 
CREATE OR REPLACE PACKAGE BODY package_test
IS
     employees_tab typeEmpTab;
     PROCEDURE getEmpRecords
     IS
     BEGIN
         --populate the collection
         SELECT EMPNO, ENAME BULK COLLECT INTO 
         employees_tab
         FROM EMP;
         --and pass as parameter to below procedure
         fetchRecords(employees_tab);
     END getEmpRecords;

     PROCEDURE fetchRecords(emp_table in typeEmpTab)
     IS
     BEGIN
         -- perform all you want here...
        FOR I IN EMP_TABLE.FIRST..EMP_TABLE.LAST LOOP
            DBMS_OUTPUT.PUT_LINE ('Employee ID: '||emp_table(i).ID||                                       '  And Name: '||emp_table(i).NAME);

        END LOOP;
     END fetchRecords;
  
END package_test;


--To Test the above
BEGIN
    package_test.getEmpRecords;
END;