ALTER PACKAGE COMPILE Statement

 This statement explicitly recompiles the specification and body of the hr.emp_mgmt package.

ALTER PACKAGE emp_mgmt COMPILE PACKAGE;

If the database encounters no compilation errors while recompiling the emp_mgmt specification and body, then emp_mgmt becomes valid. The user hr can subsequently invoke or reference all package objects declared in the specification of emp_mgmt without runtime recompilation. If recompiling emp_mgmt results in compilation errors, then the database returns an error and emp_mgmt remains invalid.

The database also invalidates all objects that depend upon emp_mgmt. If you subsequently reference one of these objects without explicitly recompiling it first, then the database recompiles it implicitly at run time.

To recompile the body of the emp_mgmt package in the schema hr, issue this statement:

ALTER PACKAGE hr.emp_mgmt COMPILE BODY;

This statement creates the specification of the emp_mgmt package.

CREATE OR REPLACE PACKAGE emp_mgmt AS 

   FUNCTION hire (last_name VARCHAR2, job_id VARCHAR2, 

      manager_id NUMBER, salary NUMBER, 

      commission_pct NUMBER, department_id NUMBER) 

      RETURN NUMBER; 

   FUNCTION create_dept(department_id NUMBER, location_id NUMBER) 

      RETURN NUMBER; 

   PROCEDURE remove_emp(employee_id NUMBER); 

   PROCEDURE remove_dept(department_id NUMBER); 

   PROCEDURE increase_sal(employee_id NUMBER, salary_incr NUMBER); 

   PROCEDURE increase_comm(employee_id NUMBER, comm_incr NUMBER); 

   no_comm EXCEPTION; 

   no_sal EXCEPTION; 

END emp_mgmt; 

This statement creates the body of the emp_mgmt package

CREATE OR REPLACE PACKAGE BODY emp_mgmt AS 

   tot_emps NUMBER; 

   tot_depts NUMBER; 

FUNCTION hire 

   (last_name VARCHAR2, job_id VARCHAR2, 

    manager_id NUMBER, salary NUMBER, 

    commission_pct NUMBER, department_id NUMBER) 

   RETURN NUMBER IS new_empno NUMBER; 

BEGIN 

   SELECT employees_seq.NEXTVAL 

      INTO new_empno 

      FROM DUAL; 

   INSERT INTO employees 

      VALUES (new_empno, 'First', 'Last','first.example@example.com',

              '(415)555-0100',

              TO_DATE('18-JUN-2002','DD-MON-YYYY'),

              'IT_PROG',90000000,00, 100,110);

      tot_emps := tot_emps + 1; 

   RETURN(new_empno); 

END; 

FUNCTION create_dept(department_id NUMBER, location_id NUMBER) 

   RETURN NUMBER IS 

      new_deptno NUMBER; 

   BEGIN 

      SELECT departments_seq.NEXTVAL 

         INTO new_deptno 

         FROM dual; 

      INSERT INTO departments 

         VALUES (new_deptno, 'department name', 100, 1700); 

      tot_depts := tot_depts + 1; 

      RETURN(new_deptno); 

   END; 

PROCEDURE remove_emp (employee_id NUMBER) IS 

   BEGIN 

      DELETE FROM employees 

      WHERE employees.employee_id = remove_emp.employee_id; 

      tot_emps := tot_emps - 1; 

   END; 

PROCEDURE remove_dept(department_id NUMBER) IS 

   BEGIN 

      DELETE FROM departments 

      WHERE departments.department_id = remove_dept.department_id; 

      tot_depts := tot_depts - 1; 

      SELECT COUNT(*) INTO tot_emps FROM employees; 

   END; 

PROCEDURE increase_sal(employee_id NUMBER, salary_incr NUMBER) IS 

   curr_sal NUMBER; 

   BEGIN 

      SELECT salary INTO curr_sal FROM employees 

      WHERE employees.employee_id = increase_sal.employee_id; 

      IF curr_sal IS NULL 

         THEN RAISE no_sal; 

      ELSE 

         UPDATE employees 

         SET salary = salary + salary_incr 

         WHERE employee_id = employee_id; 

      END IF; 

   END; 

PROCEDURE increase_comm(employee_id NUMBER, comm_incr NUMBER) IS 

   curr_comm NUMBER; 

   BEGIN 

      SELECT commission_pct 

      INTO curr_comm 

      FROM employees 

      WHERE employees.employee_id = increase_comm.employee_id; 

      IF curr_comm IS NULL 

         THEN RAISE no_comm; 

      ELSE 

         UPDATE employees 

         SET commission_pct = commission_pct + comm_incr; 

      END IF; 

   END; 

END emp_mgmt; 

This statement drops the specification and body of the emp_mgmt package.

DROP PACKAGE emp_mgmt; 

For reference Oracle Documentation.