PL/SQL User's Guide and Reference

The Package Specification

The package specification contains public declarations. The scope of these declarations is local to your database schema and global to the package. So, the declared objects are accessible from your application and from anywhere in the package. Figure 8 - 2 illustrates the scoping.

Figure 8 - 2. Package Scope

The specification lists the package resources available to applications. All the information your application needs to use the resources is in the specification. For example, the following declaration shows that the function named fac takes one argument of type INTEGER and returns a value of type INTEGER:

FUNCTION fac (n INTEGER) RETURN INTEGER;  -- returns n!

That is all the information you need to call the function. You need not consider the underlying implementation of fac (whether it is iterative or recursive, for example).

Only subprograms and cursors have an underlying implementation or definition. So, if a specification declares only types, constants, variables, and exceptions, the package body is unnecessary. Consider the following bodiless package:

-- a bodiless package
CREATE PACKAGE trans_data AS
   TYPE TimeTyp IS RECORD (
      minute SMALLINT,
      hour   SMALLINT);

   TYPE TransTyp IS RECORD (
      category VARCHAR2,
      account  INTEGER,
      amount   REAL,
      time     TimeTyp);
   minimum_balance    CONSTANT REAL := 10.00;
   number_processed   INTEGER;
   insufficient_funds EXCEPTION;
END trans_data;

The package trans_data needs no body because types, constants, variables, and exceptions do not have an underlying implementation. Such packages let you define global variables--usable by subprograms and database triggers--that persist throughout a session.

Referencing Package Contents

To reference the types, objects, and subprograms declared within a package specification, you use dot notation, as follows:

package_name.type_name
package_name.object_name
package_name.subprogram_name

You can reference package contents from a database trigger, a stored subprogram, an Oracle Precompiler application, an OCI application, or an Oracle tool such as SQL*Plus. For example, you might call the packaged procedure hire_employee from SQL*Plus, as follows:

SQL> EXECUTE emp.actions.hire_employee('TATE', 'CLERK', ...);

In the following example, you call the same procedure from an anonymous PL/SQL block embedded in a Pro*C program:

EXEC SQL EXECUTE
   BEGIN
      emp_actions.hire_employee(:name, :title, ...);
   END;
END-EXEC;

The actual parameters name and title are host variables.

Restriction

You cannot reference remote packaged variables directly or indirectly. For example, you cannot call the following procedure remotely because it references a packaged variable in a parameter initialization clause:

CREATE PACKAGE random AS
   seed NUMBER;
   PROCEDURE initialize (starter IN NUMBER := seed, ...);
   ...
END random;