Package Declaration

A package is a schema object that groups logically related PL/SQL types, items, and subprograms. Use packages when writing a set of related subprograms that form an application programming interface (API) that you or others might reuse. Packages have two parts: a specification (spec for short) and a body. For more information, see Chapter 9, "Using PL/SQL Packages". For an example of a package declaration, see Example 9-3.

This section discusses the package specification and body options for PL/SQL. For information on the CREATE PACKAGE SQL statement, see Oracle Database SQL Reference. For information on the CREATE PACKAGE BODY SQL statement, see Oracle Database SQL Reference.

Syntax

package specification ::=

Description of the illustration package_specification.gif

package body ::=

Description of the illustration package_body.gif

Keyword and Parameter Description

call_spec

Publishes a Java method or external C function in the Oracle data dictionary. It publishes the routine by mapping its name, parameter types, and return type to their SQL counterparts. For more information, see Oracle Database Java Developer's Guide and Oracle Database Application Developer's Guide - Fundamentals.

collection_declaration

Declares a collection (nested table, index-by table, or varray). For the syntax of collection_declaration, see "Collection Definition".

collection_type_definition

Defines a collection type using the datatype specifier TABLE or VARRAY.

constant_declaration

Declares a constant. For the syntax of constant_declaration, see "Constant and Variable Declaration".

cursor_body

Defines the underlying implementation of an explicit cursor. For the syntax of cursor_body, see "Cursor Declaration".

cursor_spec

Declares the interface to an explicit cursor. For the syntax of cursor_spec, see "Cursor Declaration".

exception_declaration

Declares an exception. For the syntax of exception_declaration, see "Exception Definition".

function_body

Implements a function. For the syntax of function_body, see "Function Declaration".

function_spec

Declares the interface to a function. For the syntax of function_spec, see "Function Declaration".

object_declaration

Declares an object (instance of an object type). For the syntax of object_declaration, see "Object Type Declaration".

package_name

A package stored in the database. For naming conventions, see "Identifiers".

pragma_restrict_refs

Pragma RESTRICT_REFERENCES, which checks for violations of purity rules. To be callable from SQL statements, a function must obey rules that control side effects. If any SQL statement inside the function body violates a rule, you get an error at run time (when the statement is parsed). For the syntax of the pragma, see "RESTRICT_REFERENCES Pragma".

The pragma asserts that a function does not read and/or write database tables and/or package variables. For more information about the purity rules and pragma RESTRICT_REFERENCES, see Oracle Database Application Developer's Guide - Fundamentals.

PRAGMA SERIALLY_REUSABLE

Marks a package as serially reusable, if its state is needed only for the duration of one call to the server (for example, an OCI call to the server or a server-to-server remote procedure call). For more information, see Oracle Database Application Developer's Guide - Fundamentals.

procedure_body

Implements a procedure. For the syntax of procedure_body, see "Procedure Declaration".

procedure_spec

Declares the interface to a procedure. For the syntax of procedure_spec, see "Procedure Declaration".

record_declaration

Declares a user-defined record. For the syntax of record_declaration, see "Record Definition".

record_type_definition

Defines a record type using the datatype specifier RECORD or the attribute %ROWTYPE.

schema_name

The schema containing the package. If you omit schema_name, Oracle assumes the package is in your schema.

variable_declaration

Declares a variable. For the syntax of variable_declaration, see "Constant and Variable Declaration".

Usage Notes

You can use any Oracle tool that supports PL/SQL to create and store packages in an Oracle database. You can issue the CREATE PACKAGE and CREATE PACKAGE BODY statements interactively from SQL*Plus, or from an Oracle Precompiler or OCI host program. However, you cannot define packages in a PL/SQL block or subprogram.

Most packages have a specification and a body. The specification is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, and so implements the spec.

Only subprograms and cursors have an underlying implementation. If a specification declares only types, constants, variables, exceptions, and call specifications, the package body is unnecessary. The body can still be used to initialize items declared in the specification:

CREATE OR REPLACE PACKAGE emp_actions AS
--   additional code here ...
   number_hired INTEGER;
END emp_actions;
/
CREATE OR REPLACE PACKAGE BODY emp_actions AS
BEGIN
   number_hired := 0;
END emp_actions;
/

You can code and compile a spec without its body. Once the spec has been compiled, stored subprograms that reference the package can be compiled as well. You do not need to define the package bodies fully until you are ready to complete the application. You can debug, enhance, or replace a package body without changing the package spec, which saves you from recompiling subprograms that call the package.

Cursors and subprograms declared in a package spec must be defined in the package body. Other program items declared in the package spec cannot be redeclared in the package body.

To match subprogram specs and bodies, PL/SQL does a token-by-token comparison of their headers. Except for white space, the headers must match word for word. Otherwise, PL/SQL raises an exception.

Variables declared in a package keep their values throughout a session, so you can set the value of a package variable in one procedure, and retrieve the same value in a different procedure.

Examples

For examples, see the following:

Example 1-13, "Creating a Package and Package Body"
Example 6-43, "Declaring an Autonomous Function in a Package"
Example 9-3, "Creating the emp_admin Package"
Example 9-4, "Using PUT_LINE in the DBMS_OUTPUT Package"
Example 12-18, "TEAMS Package Using Dynamic SQL for Object Types and Collections"