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


 

HTML Text

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
 
IS
 
     TYPE MessageParts_type IS TABLE OF varchar2 (20)
       INDEX BY binary_integer;
 
    vLastError                  varchar2 (100);
    THIS_PACKAGE     CONSTANT   varchar2 (13) := 'System_Errors';
 
    xUNHANDLED_ERROR            EXCEPTION;
 
    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)
 
IS
 
    vMessageParts     System_Errors.MessageParts_type;
 
BEGIN
   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:

PACKAGE Globals
 
IS
 
   FIELD_DELIMITER   CONSTANT varchar2 (1) := chr (29);
   ROW_DELIMITER     CONSTANT varchar2 (1) := chr (30);
   MAX_LENGTH        CONSTANT integer := 255;
 
   TYPE Student_rec_TYPE
   IS
   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