price number;
qty   number;
no_cost exception;
cost_or exception;

The Package Body

The body of a package contains the definition of the public objects you declared in the specification. The body also contains other object declarations that are private to the package. The objects declared privately in the package body are not accessible to other objects outside the package. Unlike the package specification, the declaration portion of the package body can contain subprogram bodies.

After the package is written, applications can reference its types, call its subprograms, use its cursor, or raise its exceptions. After the package is created, it is stored in the database for all to use.

Creating Packages

The first step to creating a package is to create its specification. The specification publicly declares the schema objects that are continued in the body of the package.

To create a specification, issue the CREATE PROCEDURE command:

CREATE OR REPLACE PACKAGE inv_pck_spec as

FUNCTION inv_count(qty integer, part_nbr varchar2(15))
RETURN integer;

PROCEDURE inv_adjust(qty integer);

END inv_pck_spec;

Note that the OR REPLACE clause was used. This clause re-creates the package specification without losing any grants that already exist.

After the specification is created, you create the body of the package. The body of a package is a collection of schema objects that was declared in the specification. These objects, or package subprograms, are accessible outside the package only if their specifications are included in the package specification.

In addition to the object definitions for the declaration, the package body can also contain private declarations. These private objects are for the internal workings of the package and are local in scope. External objects cannot reference or call internal declarations to another package.

If you perform any initialization in the package body, it is executed once when the package is initially referenced.

The following is an example of the body of the package that was specified in the previous example's specification:

CREATE OR REPLACE PACKAGE BODY inv_control is

FUNCTION inv_count
(qty integer,
part_nbr varchar2(15))
RETURN integer is ;
new_qty integer;
BEGIN
new_qty:= qty*6
INSERT into mst_inv values
(new_qty,part_nbr);
RETURN(new_qty);
END inv_count;

PROCEDURE inv_adjust(qty integer);
BEGIN
DELETE from user_01.mst_inv
WHERE inv_qty<10000;
END;

BEGIN  -- package initialization begins here
INSERT into inv_audit values
(SYSDATE, user);
END inv_control;

The final part of the procedure body in the preceding example is the package initialization. By definition, this runs only once when the procedure is referenced the first time.

Calling Package Subprograms

When a package is invoked, Oracle performs three steps to execute it:

• Verify user access—Confirms that the user has the execute system privilege grant for the subprogram.
• Verify procedure validity—Checks with the data dictionary to determine whether the subprogram is valid or not. If the object is invalid, it is automatically recompiled before being executed.
• Execute—The package subprogram is executed.

To reference the package's subprograms and objects, you must use dot notation.

The Syntax for Dot Notation

package_name.type_name
package_name.object_name
package_name.subprogram_name

In this syntax, package_name is the name of the declared package. type_name is the name of the type that you define, such as record. object_name is the name of the constant or variable you declare. subprogram_name is the name of the procedure or function contained in the package body.

To reference the variable max_balance in the package named inventory, the referencing statement would be

DECLARE
max_balance number;
BEGIN
...
IF inventory.max_balance < curr_balance THEN
...
END IF

When Oracle executes a package subprogram, an implicit savepoint will be created. If the subprogram fails with an unhandled exception, before returning to the host environment, Oracle will roll back to the savepoint, thereby undoing any changes made by the package subprogram.

Recompiling Packages

To recompile a package, use the ALTER PACKAGE command with the compile keyword. This explicit recompilation eliminates the need for any implicit runtime recompilation and prevents any associated runtime compilation errors and performance overhead. It is common to explicitly recompile a package after modifications to the package.

Recompiling a package recompiles all objects defined within the package. Recompiling does not change the definition of the package or any of its objects.

The following examples recompile just the body of a package. The second statement recompiles the entire package including the body and specification:

ALTER PACKAGE inventory_pkg compile body

ALTER PACKAGE inventory_pkg compile package

All packages can be recompiled by using the Oracle utility dbms_utility:

execute dbms_utility.compile_all

Private Versus Public Package Objects

Within the body of a package, you are permitted to define subprograms, cursors, and private declarations for types and objects. For objects that are declared inside the package body, you are restricted to use within that package. Therefore, PL/SQL code outside the package cannot reference any of the variables that were privately declared within the package.

Any items declared inside the package specification are visible outside the package. This enables PL/SQL code outside the package to reference objects from within the package. These objects declared in the package specification are called public.

Variables, Cursors, and Constant Persistence

Variables, cursors, and constants can change their value over time and have a specific life span. This life duration can vary depending on where the declaration is located. For standalone procedures, variables, cursors, and constants persist only for the duration of the procedure call and are lost when the procedure execution terminates.

If the variable, constant, or cursor was declared in a package specification or body, their values persist for the duration of the user's session. The values are lost when the current user's session terminates or the package is recompiled.

Package State

A package is always either valid or invalid. A package is considered valid if none of its source code or objects it references have been dropped, replaced, or altered since the package specification was last recompiled.

The package is considered invalid if its source code or any object that it references has been dropped, altered, or replaced since the package specification was last recompiled. When a package becomes invalid, Oracle will also make invalid any object that references the package.

Package Dependency

During the recompiling of a package, Oracle invalidates all dependent objects. These objects include standalone or package subprograms that call or reference objects declared in the recompiled specification. If another user's program calls or references a dependent object before it is recompiled, Oracle automatically recompiles it at runtime.

During package recompilation, Oracle makes a determination whether objects on which the package body depends are valid. If any of these objects are invalid, Oracle will recompile them before recompiling the package body. If recompilation is successful, then the package body becomes valid. If any errors are detected, the appropriate error messages are generated and the package body remains invalid.

Trapping Errors and Exceptions

Sometimes the Oracle server or the user's application causes an error to occur during runtime processing. Such errors can arise from hardware or network failures, application logic errors, data integrity errors, and many other sources. These errors are known as exceptions; that is, these unwanted events are exceptions to the normal processing that is expected.