Packages

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".

Determines whether all the packaged subprograms execute with the privileges of their definer (the default) or invoker, and whether their unqualified references to schema objects are resolved in the schema of the definer or invoker. For more information, see "Using Invoker's Rights Versus Definer's Rights (AUTHID Clause)".

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

cursor_spec

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

exception_declaration

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

function_body

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

function_spec

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

object_declaration

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

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".

You can use any Oracle tool that supports PL/SQL to create and store packages in an Oracle database. You can issue the CREATEPACKAGE and CREATEPACKAGEBODY statements interactively from SQL*Plus, or from an Oracle Precompiler or OCI host program.

You cannot define packages in a PL/SQL block or subprogram.

Most packages have a spec and a body. The spec 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 spec declares only types, constants, variables, exceptions, and call specs, the package body is unnecessary. The body can still be used to initialize items declared in the spec:

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.