|PL/SQL User's Guide and Reference
Release 2 (9.2)
Part Number A96624-01
PL/SQL Language Elements, 36 of 52
A package is a schema object that groups logically related PL/SQL types, items, and subprograms. Packages have two parts: a specification (spec for short) and a body. For more information, see Chapter 9.
This 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 "Invoker Rights Versus Definer Rights".
This 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 Oracle9i Java Stored Procedures Developer's Guide and/or Oracle9i Application Developer's Guide - Fundamentals.
This declares a collection (nested table, index-by table, or varray). For the syntax of
collection_declaration, see "Collections".
This defines a collection type using the datatype specifier
This declares a constant. For the syntax of
constant_declaration, see "Constants and Variables".
This defines the underlying implementation of an explicit cursor. For the syntax of
cursor_body, see "Cursors".
This declares the interface to an explicit cursor. For the syntax of
cursor_spec, see "Cursors".
This declares an exception. For the syntax of
exception_declaration, see "Exceptions".
This defines the underlying implementation of a function. For the syntax of
function_body, see "Functions".
This declares the interface to a function. For the syntax of
function_spec, see "Functions".
This declares an object (instance of an object type). For the syntax of
object_declaration, see "Object Types".
This identifies a package stored in the database. For naming conventions, see "Identifiers".
This is pragma
RESTRICT_REFERENCES, which lets you check for violations of "purity" rules. To be callable from SQL statements, a function must obey those rules, which are meant to 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 Oracle9i Application Developer's Guide - Fundamentals.
This pragma lets you mark a package as serially reusable. You can so mark a package 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 RPC). For more information, see Oracle9i Application Developer's Guide - Fundamentals.
This defines the underlying implementation of a procedure. For the syntax of
procedure_body, see "Procedures".
This declares the interface to a procedure. For the syntax of
procedure_spec, see "Procedures".
This declares a user-defined record. For the syntax of
record_declaration, see "Records".
This defines a record type using the datatype specifier
RECORD or the attribute
This qualifier identifies the schema containing the package. If you omit
schema_name, Oracle assumes the package is in your schema.
This declares a variable. For the syntax of
variable_declaration, see "Constants and Variables".
You cannot define packages in a PL/SQL block or subprogram. However, you can use any Oracle tool that supports PL/SQL to create and store packages in an Oracle database. You can issue the
BODY statements from an Oracle Precompiler or OCI host program, or interactively from SQL*Plus.
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 (definition). So, if a spec declares only types, constants, variables, exceptions, and call specs, the package body is unnecessary. However, the body can still be used to initialize items declared in the spec, as the following example shows:
CREATE PACKAGE emp_actions AS ... number_hired INTEGER; END emp_actions; CREATE 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 need not define the package bodies fully until you are ready to complete the application. Furthermore, you can debug, enhance, or replace a package body without changing the interface (package spec) to the package body. So, you need not recompile calling programs.
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. So, except for white space, the headers must match word for word. Otherwise, PL/SQL raises an exception.