What is Package in Oracle?

A package is a stored sub-program that combines PL/SQL procedures and functions into a single unit. A package is an encapsulated collection of related program objects stored together in the database. Program objects are procedures, functions, variables, constants, cursors, and exceptions. The purpose of a package is to logically connect groups of functions and procedures together. This makes maintenance of related portions of code easier. Packages add a layer of complexity to code. If your system contains fewer than ten procedures and functions, you are better off leaving them as separate objects rather than combining them into packages.
The package contains two main components:

1. the package specification and
2. the package body .

These are stored in the database as separate objects. Look at the following ToolTip to see all the components that make up a package.

PL/SQL packages have two parts:

1. the specification and
2. the body,

although sometimes the body is unnecessary. The specification is the interface to your application; it declares the

1. types,
2. variables,
3. constants,
4. exceptions,
5. cursors, and
6. subprograms

available for use. The body fully defines cursors and subprograms, and so implements the specification. Unlike subprograms, packages cannot be called, parameterized, or nested. However, the formats of a package and a subprogram are similar:

CREATE PACKAGE name AS -- specification (visible part)
-- public type and item declarations
-- subprogram specifications
END [name];
CREATE PACKAGE BODY name AS -- body (hidden part)
-- private type and item declarations
-- subprogram bodies
[BEGIN
-- initialization statements]
END [name];

The specification holds public declarations that are visible to your application. The body holds implementation details and private declarations that are hidden from your application. You can debug, enhance, or replace a package body without changing the specification. You can change a package body without recompiling calling programs because the implementation details in the body are hidden from your application.

A package can contain any combination of 1) procedures and 2) functions. In addition to procedures and functions, a package specification may include declared

1. variables,
2. cursors,
3. types, and
4. exceptions.

The procedure specification contains the procedure name and all its parameters. The function specification includes the function name, its parameters, and its return specification. The package body contains blocks that define each procedure and function within the package.
A private procedure is a procedure that is called by other procedures or functions inside this package. It cannot be referenced outside the package. A private procedure is also called a local procedure. A private function is a function that is called by other procedures or functions inside this package. It cannot be referenced outside the package. A private function is also called a local function. A function that was defined in the package specification must be defined in the package body. The name, parameters, and return specifications must all match exactly with those in the package specification. The complete function is defined here in the same syntax as that used when defining a stand-alone function.
A procedure that was defined in the package specification must be defined in the package body. The name and parameters must all match exactly with those in the package specification. The complete procedure is defined here in the same syntax as that used when defining a stand-alone procedure.

The procedures and functions that are defined in the package specifications can be called from outside the package. Each of these procedures and functions has corresponding executable blocks within the package body. The package body can also contain private procedures and functions that are not accessible outside the package. These are called from other procedures and functions inside the package. Private functions and procedures must never call objects (such as procedures or other packages) that are outside the package. The next lesson describes the pros and cons of using packages.