Oracle provide PL/SQL packages let you extend the database functionality . You can write you own package or utilize the packages shipped with Oracle release.Oracle supplies all kinds of packages, familiar with these packages is a must-have skill of DBA.

Packages is a collection of different program objects include procedures, functions, variables, constants, cursors, exceptions etc.

Benefits of packages

  • Use global variables shared by procedures and functions in package
  • Organize related objects in same logical package
  • Can create multiple procedures with the same name in the same package

Structure of package

A package consist of package specification and package body:

 
CREATE PACKAGE name AS 
  -- pubic type declarations
END [name];
 
CREATE PACKAGE BODY name AS 
  -- private type declarations
[BEGIN]
 
END [name];
 

The public declaration is visible to your application , private is hidden to your application. Public provide the interface of the package, you can replace the body without changing the interface.

Install packages

The built-in package automatically created whenever a new database is created. The script CATPROC.SQL is run.

Some package are not automatically installed you can install it manually according the documentation.

And of course you can define your own package by "CREATE PACKAGE and CREATE PACKAGE BODY" command.

Schema objects and Program objects

Here Schema objects are that objects outside of packages and can call or reference objects in packages, we call these Program objects. Schema objects can call the program objects only through the package specification defined in the public declaration. So the changes of the private definition won't cause Oracle to invalidate the schema objects that call or reference the program objects.

Creating package example

The package defines one function and two procedures:

 
CREATE PACKAGE employee_management AS
   FUNCTION hire_emp (name VARCHAR2, job VARCHAR2,
      mgr NUMBER, hiredate DATE, sal NUMBER, comm NUMBER,
      deptno NUMBER) RETURN NUMBER;
   PROCEDURE fire_emp (emp_id NUMBER);
   PROCEDURE sal_raise (emp_id NUMBER, sal_incr NUMBER);
END employee_management;
 
CREATE PACKAGE BODY employee_management AS
   FUNCTION hire_emp (name VARCHAR2, job VARCHAR2,
      mgr NUMBER, hiredate DATE, sal NUMBER, comm NUMBER,
      deptno NUMBER) RETURN NUMBER IS
      new_empno    NUMBER(10);
   BEGIN
      SELECT emp_sequence.NEXTVAL INTO new_empno FROM dual;
      INSERT INTO emp VALUES (new_empno, name, job, mgr,
         hiredate, sal, comm, deptno);
      RETURN (new_empno);
   END hire_emp;
   PROCEDURE fire_emp(emp_id IN NUMBER) AS
   BEGIN
      DELETE FROM emp WHERE empno = emp_id;
      IF SQL%NOTFOUND THEN
      raise_application_error(-20011, 'Invalid Employee
         Number: ' || TO_CHAR(emp_id));
   END IF;
END fire_emp;
PROCEDURE sal_raise (emp_id IN NUMBER, sal_incr IN NUMBER) AS
   BEGIN
   -- If employee exists, then update salary with increase.
      UPDATE emp
         SET sal = sal + sal_incr
         WHERE empno = emp_id;
      IF SQL%NOTFOUND THEN
         raise_application_error(-20011, 'Invalid Employee
            Number: ' || TO_CHAR(emp_id));
      END IF;
   END sal_raise;
END employee_management;
 
 

To run this script you should define the SEQUENCE emp_sequence:

 
SQL> CREATE SEQUENCE emp_sequence
   > START WITH 8000 INCREMENT BY 10;