Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




PL/SQL Packages
Oracle Tips by Burleson

A package is a group of procedures, functions, and variables, often grouped together because they accomplish related tasks. In PL/SQL, packages consist of two parts: the package specification, or package spec, and the package body.

The Package Spec

The package spec can be clearly illustrated by using a form-based analogy. When a form displays on your terminal, certain buttons are displayed as well. The code behind each button is a call to a procedure or function. Figure 6.1 illustrates this model within the context of a package.

Figure 6.1  The package model.

Simply put, a package spec defines how other objects within an Oracle database interact with the package. The package spec contains the following types of definitions:

  • Global variables, constants, user-defined datatypes, and user-defined exceptions
  • Procedure declarations (interface only)
  • Function declarations (interface only)

Every construct and object defined within a package spec is public and can be referenced by any block of PL/SQL code. Objects within a package body that are not defined within the package spec are private objects and can be referenced only by objects within the package.

Global Variables

The package spec can contain definitions for variables, constants, datatypes, and user-defined exceptions that can be referenced by the package spec and body, as well as by outside objects. Listing 6.1 illustrates the definition of these constructs.

Listing 6.1 The definition of global constructs in a package spec.

PACKAGE System_Errors
     TYPE MessageParts_type IS TABLE OF varchar2 (20)
       INDEX BY binary_integer;
    vLastError                  varchar2 (100);
    THIS_PACKAGE     CONSTANT   varchar2 (13) := 'System_Errors';
    FUNCTION Build_Message (vObjectName  IN     varchar2,
                            iErrorCode   IN     integer,
                            vErrorString IN     varchar2);
   PROCEDURE Log_Error (vObjectName  IN     varchar2,
                        vErrorString IN     varchar2,
                        vErrorData   IN     varchar2,
                        iErrorCode   IN     integer);
END System_Errors;

Each of the highlighted constructs can be referenced by all the procedures and functions contained within the package body. Objects outside the package can also reference the constructs by prefacing the name of the construct with the name of the package, as shown in Listing 6.2.

Listing 6.2 Referencing an object within a package.

PROCEDURE Local_Error_Log (vObjectName  IN     varchar2,
                           iErrorCode   IN     integer,
                           vErrorString IN     varchar2)
    vMessageParts     System_Errors.MessageParts_type;
   System_Errors.Build_Message (vObjectName  => vObjectName,
                                iErrorCode   => iErrorCode,
                                vErrorString => vErrorString);
END Local_Error_Log;

Each construct is specific to the user who references it. For example, user A initializes vLastError to ‘Student does not exist’ and user B initializes vLastError to ‘Attempted division by zero’. Neither user is overwriting the value of the other user’s variable.

The Bodyless Package
Large systems often have a number of definitions that need to be standardized throughout the system. You can define a package spec that contains these definitions and reference the package spec from any object. Although packages are usually defined using both a spec and a body, Oracle doesn’t require every package spec to have an associated package body. Consider this package spec:

   FIELD_DELIMITER   CONSTANT varchar2 (1) := chr (29);
   ROW_DELIMITER     CONSTANT varchar2 (1) := chr (30);
   MAX_LENGTH        CONSTANT integer := 255;
   TYPE Student_rec_TYPE
   RECORD (first_name      varchar2 (20),
           last_name       varchar2 (20),
           middle_initial  varchar2 (1),
           ssn             varchar2 (9));
   TYPE SubStrings_tab_TYPE IS TABLE OF varchar2 (20)
   INDEX BY binary_integer;
END Globals;

Any stored PL/SQL object can reference any of the constants or datatypes defined in the Globals package spec. Since the package doesn’t contain any actual procedures or functions, there’s no need to define a package body that corresponds to the spec.


This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter