PL/SQL User's Guide and Reference

Packages

Description

A package is a database object that groups logically related PL/SQL types, objects, and subprograms. Packages have two parts: a specification and a body. For more information, see Chapter 8.

Syntax

package_specification ::=

PACKAGE package_name IS
   {object_declaration | spec_declaration}
   [{object_declaration | spec_declaration}]...
END [package_name];

package_body ::=

PACKAGE BODY package_name IS
   [[object_declaration [object_declaration] ...]
    [body_declaration [body_declaration] ...]]
[BEGIN
   seq_of_statements]
END [package_name];

object_declaration ::=

{  constant_declaration
 | cursor_declaration
 | exception_declaration
 | plsql_table_declaration
 | record_declaration
 | variable_declaration}

spec_declaration ::=

{  cursor_specification
 | function_specification
 | procedure_specification}

body_declaration ::=

{  cursor_body
 | function_body
 | procedure_body}

Keyword and Parameter Description

package_name

This identifies a package. For naming conventions, see "Identifiers"

constant_declaration

This construct declares a constant. For the syntax of constant_declaration, see "Constants and Variables"

cursor_declaration

This construct, which cannot contain a RETURN clause, declares an explicit cursor. For the syntax of cursor_declaration, see "Cursors"

exception_declaration

This construct declares an exception. For the syntax of exception_declaration, see "Exceptions"

plsql_table_declaration

This construct declares a PL/SQL table. For the syntax of plsql_table_declaration, see "PL/SQL Tables"

record_declaration

This construct declares a user-defined record. For the syntax of record_declaration, see "Records"

variable_declaration

This construct declares a variable. For the syntax of variable_declaration, see "Constants and Variables"

cursor_specification

This construct declares the interface to an explicit cursor. For the syntax of cursor_specification, see "Cursors"

function_specification

This construct declares the interface to a function. For the syntax of function_specification, see "Functions"

procedure_specification

This construct declares the interface to a procedure. For the syntax of procedure_specification, see "Procedures"

cursor_body

This construct defines the underlying implementation of an explicit cursor. For the syntax of cursor_body, see "Cursors"

procedure_body

This construct defines the underlying implementation of a procedure. For the syntax of procedure_body, see "Procedures"

function_body

This construct defines the underlying implementation of a function. For the syntax of function_body, see "Functions"

Usage Notes

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 CREATE PACKAGE and CREATE PACKAGE BODY statements interactively from SQL*Plus or Server Manager and from an Oracle Precompiler or OCI host program. For the full syntax of the CREATE PACKAGE statement, see Oracle7 Server SQL Reference.

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

Only subprograms and cursors have an underlying implementation (definition). So, if a specification declares only types, constants, variables, and exceptions, the package body is unnecessary. However, the body can still be used to initialize objects declared in the specification, 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 specification without its body. Once the specification 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 specification) to the package body. That means you need not recompile calling programs.

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

To match subprogram specifications 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.