Package Overview

A package is an encapsulated collection of related program objects stored together in the database. Program objects are procedures, functions, variables, constants, cursors, and exceptions.

Packages have many advantages over stand-alone procedures and functions. For example, they:

• Let you organize your application development more efficiently.

• Let you grant privileges more efficiently.

• Let you modify package objects without recompiling dependent schema objects.

• Enable Oracle to read multiple package objects into memory at once.

• Let you overload procedures or functions. Overloading means creating multiple procedures with the same name in the same package, each taking arguments of different number or datatype.

• Can contain global variables and cursors that are available to all procedures and functions in the package.

Using Oracle Supplied Packages

Most Oracle supplied packaged are automatically installed when the database is created and the CATPROC.SQL script is run. For example, to create the DBMS_ALERT package, the DBMSALRT.SQL and PRVTALRT.PLB scripts must be run when connected as the user SYS. These scripts, however, are run automatically by the CATPROC.SQL script.

Certain packages are not installed automatically. Special installation instructions for these packages are documented in the individual chapters.

To call a PL/SQL function from SQL, you must either own the function or have EXECUTE privileges on the function. To select from a view defined with a PL/SQL function, you must have SELECT privileges on the view. No separate EXECUTE privileges are needed to select from the view. Instructions on special requirements for packages are documented in the individual chapters.

Creating New Packages

There are two distinct steps to creating a new package:

1. Create the package specification with the CREATE PACKAGE statement.

   You can declare program objects in the package specification. Such objects are called public objects. Public objects can be referenced outside the package, as well as by other objects in the package.

   Note: It is often more convenient to add the OR REPLACE clause in the CREATE PACKAGE statement

2. Create the package body with the CREATE PACKAGE BODY statement.

   You can declare and define program objects in the package body:

   • You must define public objects declared in the package specification.

   • You can declare and define additional package objects. Such objects are called private objects. Private objects are declared in the package body rather than in the package specification, so they can be referenced only by other objects in the package: They cannot be referenced outside the package.

     See Also: For more information on creating new packages, see PL/SQL User's Guide and Reference and Oracle8i Application Developer's Guide - Fundamentals. For more information on storing and executing packages, see Oracle8i Concepts.

Separation of Specification and Body

The specification of a package declares the public types, variables, constants, and subprograms that are visible outside the immediate scope of the package. The body of a package defines the objects declared in the specification, as well as private objects that are not visible to applications outside the package.

Oracle stores the specification and body of a package separately in the database. Other schema objects that call or reference public program objects depend only on the package specification, not on the package body. This distinction allows you to change the definition of a program object in the package body without causing Oracle to invalidate other schema objects that call or reference the program object. Oracle invalidates dependent schema objects only if you change the declaration of the program object in the package specification.

Example

The following example shows a package specification for a package named EMPLOYEE_MANAGEMENT. The package contains one stored function and two stored 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;

The body for this package defines the function and the procedures:

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

The function accepts all arguments for the fields in the employee table except for the employee number. A value for this field is supplied by a sequence. The function returns the sequence number generated by the call to this function.

       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

The procedure deletes the employee with an employee number that corresponds to the argument emp_id. If no employee is found, then an exception is raised.

   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

The procedure accepts two arguments. Emp_id is a number that corresponds to an employee number. Sal_incr is the amount by which to increase the employee's salary.

   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;

Note: If you want to try this example, then first create the sequence number emp_sequence. You can do this using the following SQL*Plus statement: SQL> EXECUTE CREATE SEQUENCE emp_sequence > START WITH 8000 INCREMENT BY 10;

Referencing Package Contents

To reference the types, items, and subprograms declared in a package specification, use the dot notation. For example:

package_name.type_name
package_name.item_name
package_name.subprogram_name