END;
/* The total_cost function uses net_profit. */
FUNCTION total_cost (. . .) RETURN NUMBER
IS
BEGIN
IF net_profit (. . .) < 0
THEN
RETURN 0;
ELSE
RETURN . . .;
END IF;
END;
BEGIN
. . .
END;
Here are some rules to remember concerning forward declarations:
●
You cannot make forward declarations of a variable or cursor. This technique works only with
modules (procedures and functions).
●
The definition for a forwardly-declared program must be contained in the declaration section
of the same PL/SQL block (anonymous block, procedure, function, or package) in which you
code the forward declaration.
In some situations, you absolutely require forward declarations; in most situations, they just help
make your code more readable and presentable. As with every other advanced or unusual feature of
the PL/SQL language, use forward declarations only when you really need the functionality.
Otherwise, the declarations simply add to the clutter of your program, which is the last thing you
want.
Previous: 15.8 Module
Overloading
Oracle PL/SQL

and formulas. Apply a fanatic's devotion to the modular construction of true black boxes which easily
plug-and-play in and across applications.
You will find yourself spending more time in the design phase and less time in debug mode. Your
programs will be more readable and maintainable. They will stand as elegant testimonies to your
intellectual integrity. You will be the most popular kid in your class and ... but enough already. I am
sure you are properly motivated.
Go forth and modularize!
Previous: 15.9 Forward
Declarations
Oracle PL/SQL
Programming, 2nd Edition
Next: 16. Packages
15.9 Forward Declarations
Book Index
16. Packages
The Oracle Library
Navigation

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 15.10 Go Forth
and Modularize!
Chapter 16
Next: 16.2 Overview of
Package Structure

16. Packages
Contents:
The Benefits of Packages

operators of the PL/SQL language, such as the + and LIKE operators and the INSTR function, are all
defined in a special package called STANDARD. If Oracle believes that packages are the way to go
when it comes to building both fundamental and complex programs, don't you think that you could
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
benefit from the same?
Packages are, by nature, highly modular. When you place a program unit into a package you
automatically create a context for that program. By collecting related PL/SQL elements in a package,
you express that relationship in the very structure of the code itself. Packages are often called "the
poor man's objects" because they support some, but not all, object-oriented rules. For example,
packages allow you to encapsulate and abstract your data and functions.
The PL/SQL package is a deceptively simple, powerful construct. You can in just a few hours learn
the basic elements of package syntax and rules; there's not all that much to it. You can spend days
and weeks, however, uncovering all the nuances and implications of the package structure. This
chapter -- and the next one filled with examples of packages -- will help you absorb the features and
benefits of the PL/SQL package more rapidly.
16.1 The Benefits of Packages
Before we explore all the aspects of working with packages, let's review some of the most important
benefits of the package:
16.1.1 Enforced Information Hiding
When you build a package, you decide which of the package elements are public (can be referenced
outside of the package) and which are private (available only within the package itself). You also can
restrict access to the package to only the specification. In this way, you use the package to hide the
implementational details of your programs. This is most important when you want to isolate the most
volatile aspects of your application, such as platform dependencies, frequently changing data
structures, and temporary workarounds.
16.1.2 Object-Oriented Design
While PL/SQL does not yet offer full object-oriented capabilities, packages do offer the ability to
follow many object-oriented design principles. The package gives developers very tight control over
how the modules and data structures inside the package can be accessed.
You can, therefore, embed all the rules about and access to your entities (whether they are database

When an object in a package is referenced for the first time, the entire package (already compiled and
validated) is loaded into memory (the Shared Global Area [SGA] of the RDBMS). All other package
elements are thereby made immediately available for future calls to the package. PL/SQL does not
have to keep retrieving program elements or data from disk each time a new object is referenced.
This feature is especially important in a distributed execution environment. You may reference
packages from different databases across a local area or even a wide area network. You want to
minimize the network traffic involved in executing your code.
Packages also offer performance advantages on the development side (with potential impact on
overall database performance). The Oracle RDBMS automatically tracks the validity of all program
objects (procedures, functions, packages) stored in the database. It determines what other objects that
program is dependent on, such as tables. If a dependent object such as a table's structure changes, for
example, then all programs that rely on that object are flagged as invalid. The database then
automatically recompiles these invalid programs before they are used.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
You can limit automatic recompiles by placing functions and procedures inside packages. If program
A calls packaged module B, it does so through the package's specification. As long as the
specification of a packaged module does not change, any program that calls the module is not flagged
as invalid and will not have to be recompiled.
This chapter should provide you with all the information and examples you need to put packages to
work immediately in your applications. If you are still unsure about packages after reading it, try out
a couple of small packages. Test those hard-to-believe features like global package data to prove to
yourself that they really work as advertised. Examine carefully the examples in
Chapter 18, Object
Types. Do whatever you need to do to incorporate packages into every level of your application, from
database server to client applications.
Previous: 15.10 Go Forth
and Modularize!
Oracle PL/SQL
Programming, 2nd Edition
Next: 16.2 Overview of

A package also has a specification and a body, but the package's two parts are structured differently,
and have a different significance, from those for a single module. With a package, the specification
and body are completely distinct objects. You can write and compile the specification independently
of the body. When you create and replace stored packages in the database, you perform this action
separately for each of the specification and body.
This separation of specification and body allows you to employ top-down design techniques in a
powerful way. Don't worry about the details of how a procedure or function is going to do its job. Just
concentrate on the different modules you need and how they should be connected together.
16.2.1 The Specification
The package specification contains the definition or specification of all elements in the package that
may be referenced outside of the package. These are called the public elements of the package (see
the section entitled
Section 16.2.4, "Public and Private Package Elements"" for more information).
Figure 16.1 shows an example of a package specification containing two module definitions.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Figure 16.1: The specification of sp_timer package
Like the module, the package specification contains all the code that is needed for a developer to
understand how to call the objects in the package. A developer should never have to examine the
code behind the specification (which is the body) in order to understand how to use and benefit from
the package.
16.2.2 The Body
The body of the package contains all the code behind the package specification: the implementation
of the modules, cursors, and other objects. Figure 16.2 illustrates the body required to implement the
specification of the sp_timer package shown in
Figure 16.1.
Figure 16.2: The body of sp_timer package
The body may also contain elements that do not appear in the specification. These are called the
private elements of the package. A private element cannot be referenced outside of the package, since
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
it does not appear in the specification.

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
END [ package_name ];
In the body you can declare other variables, but you do not repeat the declarations in the
specification. The body contains the full implementation of cursors and modules. In the case
of a cursor, the package body contains both specification and SQL statement for the cursor. In
the case of a module the package body contains both the specification and body of the module.
The BEGIN keyword indicates the presence of an execution or initialization section for the package.
This section can also optionally include an exception section.
As with a procedure or function, you can add the name of the package, as a label, after the END
keyword in both the specification and package.
16.2.4 Public and Private Package Elements
A central concept of packages is the privacy level of its elements. One of the most valuable aspects of
a package is its ability to actually enforce information hiding. With a package you can not only
modularize your secrets behind a procedural interface, you can keep these parts of your application
completely private.
An element of a package, whether it is a variable or a module, can either be private or public, as
defined below:
Public
Defined in the specification. A public element can be referenced from other programs and PL/
SQL blocks.
Private
Defined only in the body of the package, but does not appear in the specification. A private
element cannot be referenced outside of the package. Any other element of the package may,
however, reference and use a private element. Private elements in a package must be defined
before they can be referenced by other elements of the package. If, in other words, a public
procedure calls a private function, that function must be defined above the public procedure in
the package body. You can, alternatively, use a forward declaration if you wish to keep your
private programs at the bottom of the package body (see
Chapter 15, Procedures and
Functions).

CURSOR pet_cur RETURN pet%ROWTYPE;
FUNCTION next_pet_shots (pet_id_in IN NUMBER) RETURN
DATE;
PROCEDURE set_schedule (pet_id_in IN NUMBER);
END pets_inc;
To reference any of these objects, I preface the object name with the package name, as follows:
BEGIN
IF pets_inc.max_pets_in_facility > 100
THEN
...
END IF;
EXCEPTION
WHEN pets_inc.pet_is_sick
THEN
...
END;
OPEN pets_inc.pet_cur;
:pet_master.next_appointment
:= pets_inc.next_pet_shots (:pet_master.pet_id);
If you do not preface the call to next_pet_shots with the package name, pets_inc, PL/SQL is not able
to resolve the reference and the compile fails.
So, the rule for referencing package elements is simple and clear: To reference a stored package
element, use dot notation. The one exception is that inside a package, you do not need to qualify
references to other elements of that package. PL/SQL will automatically resolve your reference
within the scope of the package.
Suppose, for example, that the set_schedule procedure of pets_inc references the
max_pets_in_facility constant. Such a reference would be unqualified as shown here:
PROCEDURE set_schedule (pet_id_in IN NUMBER)
IS
...

CURSOR pet_cur (pet_name_in IN VARCHAR2) RETURN pet%
ROWTYPE;
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
FUNCTION next_pet_shots (pet_id_in IN petid_type)
RETURN DATE;
PROCEDURE set_schedule (pet_id_in IN petid_type)
END pets_inc;
The header for the package specification simply states PACKAGE. You do not explicitly indicate
that it is the specification, as in PACKAGE SPECIFICATION. Instead, when you create the body of
a package, you indicate explicitly in the first line of the definition that you are defining the body of
the pets_inc package.
Since all of these elements are in the package, I can reference them in other programs, such as the
following procedure:
PROCEDURE show_next_visit (pet_in IN VARCHAR2)
IS
next_visit DATE;
/* Declare record to receive row fetched from package
cursor. */
pet_rec pets_inc.pet_cur%ROWTYPE;
BEGIN
/* Open the package-based cursor. */
OPEN pets_inc.pet_cur (pet_in);
/* Fetch from cursor into local record. */
FETCH pets_inc.pet_cur INTO pet_rec;
IF pets_inc.pet_cur%FOUND
THEN
/* Call packaged function to get next visit date. */
next_visit := pets_inc.next_pet_shots (pet_rec.
pet_id);
/* Display the information. */

SELECT * FROM pet;
FUNCTION pet_status (pet_id_in IN petid_type) RETURN
VARCHAR2
IS
BEGIN
... code behind the module ...
END;
FUNCTION next_pet_shots (pet_id_in IN petid_type)
RETURN DATE
IS
BEGIN
... the code behind the module ...
END;
PROCEDURE set_schedule (pet_id_in IN petid_type)
IS
BEGIN
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
... the code behind the module ...
END;
END pets_inc;
The body for the pet maintenance package contains the SELECT statement for the pet_cur cursor as
well as the code required to implement all the modules.
This package body contains two private elements: max_date and pet_status. The max_date constant is
used inside the package modules to validate dates that are manipulated in the package. The pet_status
function is used by other modules to retrieve the status of the pet.
Because these elements are private, they can only be referenced by other elements of the package.
16.2.6.3 Observations about pets_inc
There are several interesting facts to point out about the previous two package components:
●
The package specification does not contain any executable statements or exception handlers.

Chapter 16
Packages
Next: 16.4 The Package
Body

16.3 The Package Specification
The specification of a package lists all the objects in that package that are available for use in
applications, and provides all the information a developer needs in order to use objects in the
package. A package specification may contain any of the following object specification statements:
●
Variable declaration. Any kind of variable declaration statement, from a Boolean variable to a
character string to a number. This variable is then available outside of the package (as well as
within the body of the package).
●
TYPE declaration (PL/SQL Version 2 only). Any kind of valid TYPE statement, such as those
to create a programmer-defined record type or a PL/SQL table. These complex data structures
are then available outside of the package (as well as within the body of the package).
●
Exception declaration. Declare exceptions in a package that can then be raised and handled
outside of the package.
●
Cursor specification (PL/SQL Version 2 only). Specify a cursor's name and its RETURN
clause. This cursor can then be opened, fetched, and closed outside of the package (as well as
within the body of the package). This is available only in PL/SQL Version 2 because Version
1 does not support the required RETURN clause for a cursor. For more information on
cursors, see
Chapter 6, Database Interaction and Cursors.
●
Module specification. Place the full specification for a module in the package specification. A
module specification is the module type (PROCEDURE or FUNCTION), module name,

may be just the ticket.
16.3.1.1 A package of exceptions
The exception handler package in the next example declares a set of programmer-defined exception
numbers and exceptions to go with them. It also declares a PL/SQL table to hold the associated error
messages (see
Chapter 8, Exception Handlers, for more information about exceptions and the pragma
statement, EXCEPTION_INIT).
PACKAGE exchdlr
IS
en_general_error NUMBER := -20000;
exc_general_error EXCEPTION;
PRAGMA EXCEPTION_INIT (exc_general_error, -20000);
en_must_be_eighteen NUMBER := -20001;
exc_must_be_eighteen EXCEPTION;
PRAGMA EXCEPTION_INIT (exc_must_be_eighteen, -20001);
max_error_number_used NUMBER := -20001;
TYPE error_msg_tabtype IS TABLE OF VARCHAR2 (240)
INDEX BY BINARY_INTEGER;
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
error_msg_table error_msg_tabtype;
END exchdlr;
Because this package does not specify any cursors or modules, I do not need to create a body for the
exception handler package. In
Chapter 8 I include a version of this package that does, in fact, specify
two procedures in the package. That version does need a package body).
16.3.1.2 A package of magic values
A magic value is a literal that has special significance in a system. These values might be type codes
or validation limits. Your users will tell you that these magic values never change. "I will always
have only 25 line items in my profit-and-loss," one will say. "The name of the parent company,"
swears another, "will always be ATLAS HQ." Don't take these promises at face value, and never