create or replace PACKAGE test.compensation_rules
IS
   FUNCTION adjusted_compensation (
      employee_id_in IN 
employees.employee_id%TYPE,
      pct_increase_in IN NUMBER)
   RETURN 
 employees.salary%TYPE
   ;
END compensation_rules;
/

show errors;

create      or replace PROCEDURE test.change_salary_for (
   dept_in             IN employees.department_id%TYPE
 , pct_increase_in  IN NUMBER
 , fetch_limit_in    IN PLS_INTEGER
)
IS
   bulk_errors exception;
   PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
   CURSOR employees_cur
   IS
      SELECT employee_id, salary FROM employees WHERE department_id = dept_in;

   TYPE employee_tt   IS      TABLE OF employees.employee_id%TYPE INDEX BY BINARY_INTEGER;

   employee_ids   employee_tt;

   TYPE salary_tt   IS      TABLE OF employees.salary%TYPE INDEX BY BINARY_INTEGER;

   salaries       salary_tt;

   PROCEDURE log_errors   IS      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      FOR error_rec IN (SELECT * FROM err$_employees)
      LOOP
         q$error_manager.register_error (
            error_code_in   => error_rec.ora_err_number$
          , name1_in        => 'EMPLOYEEE_ID'
          , value1_in        => error_rec.employee_id
          , name2_in        => 'PCT_INCREASE'
          , value2_in        => pct_increase_in
          , name3_in        => 'NEW_SALARY'
          , value3_in        => error_rec.salary
         );
      END LOOP;

      DELETE FROM err$_employees;

      COMMIT;
   END log_errors;
BEGIN
   OPEN employees_cur;

   LOOP
      FETCH employees_cur
         BULK COLLECT INTO employee_ids, salaries LIMIT fetch_limit_in;

      FOR indx IN 1 .. employee_ids.COUNT
      LOOP
         salaries (indx) := compensation_rules.adjusted_compensation (
            employee_id_in    => employee_ids (indx)
          , pct_increase_in    => pct_increase_in
         );
      END LOOP;

      FORALL indx IN 1 .. employee_ids.COUNT()
             UPDATE employees SET salary = salaries (indx) WHERE employee_id = employee_ids (indx)

         LOG ERRORS REJECT LIMIT UNLIMITED;

      log_errors ();

      EXIT WHEN employee_ids.COUNT() < fetch_limit_in;
   END LOOP;
END change_salary_for;


/
show errors;