Oracle in a Nutshell

Package Body

CREATE [OR REPLACE] PACKAGE BODY package_name 
{IS | AS}

   [definitions of private TYPEs
                        ,declarations of private variables, types and objects
                        ,full definitions of cursors
                        ,full definitions of procedures and functions]

[BEGIN
   executable_statements

[EXCEPTION
   exception_handlers ] ]

END [package_name];

Contains all of the code needed to implement procedures, functions, and cursors listed in the specification, as well as any private objects accessible only to other elements defined in that package, and an optional initialization section.

The declarations in the specifications cannot be repeated in the body. Both the executable section and the exception section are optional in a package body. The package body itself can have an execution section, which follows the declaration of procedures and functions within the package body. If the executable section is present, it is called the initialization section and executes only once—the first time any package element is referenced during a session.

You must compile the package specification before the body specification. When you grant EXECUTE authority on a package to another schema or to PUBLIC, you are giving access only to the specification; the body remains hidden.

Here’s an example of a package:

CREATE OR REPLACE PACKAGE time_pkg IS FUNCTION GetTimestamp RETURN DATE; PRAGMA RESTRICT_REFERENCES (GetTimestamp, WNDS); PROCEDURE ResetTimestamp; END time_pkg; CREATE OR REPLACE PACKAGE BODY time_pkg IS StartTimeStamp DATE := SYSDATE; ...