Procedures

The most commonly defined object within a package is a procedure. Listing 6.3 shows how a procedure is defined within a package spec.

Listing 6.3 Defining a procedure within 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;

Chapter 4 provides a detailed discussion of procedures. There are only four differences between a package procedure and a standalone procedure:

•  Creation—A standalone procedure is created using the CREATE PROCEDURE statement. A packaged procedure is created as part of the package body’s definition.

•  Memory—Oracle caches standalone procedures in memory by themselves, but packaged procedures must be stored and cleared from memory with the rest of the package.

•  Execution—Packaged procedures must be qualified by the package name to be executed by objects that aren’t contained within the package, as shown in the following example:

  System_Log.Log_Error (vObjectName  => 'Conversions.Feet_To_Meters',
                        iErrorCode   => NULL_PARAMETER,
                        vErrorString => SQLERRM,
                        vErrorData   => nFeet);

•  Scope—Packaged procedures can reference other constructs and objects within the same package without qualifying the reference with a package name.

Procedures aren’t created within a package spec, but the interface for a procedure is defined within the spec. Once a procedure has been defined in a package spec, the procedure must be created within the package body before the package body will compile.

Functions

Functions are also commonly defined within a package spec. Listing 6.4 shows how a function is defined within a package spec.

Listing 6.4 Defining a function within 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;

Chapter 5 provides a detailed discussion of functions. There are five important differences between standalone functions and packaged functions:

•  Creation—A standalone function is created using the CREATE FUNCTION statement. A packaged function is created as part of a package body’s definition.

•  Memory—Oracle caches standalone functions in memory by themselves, but packaged functions must be cached and cleared from memory with the rest of the package.

•  Execution—Packaged functions must be qualified by a package name to be executed by objects that aren’t contained within the package, as shown in the following example:

  vErrorMsg := Build_Message (vObjectName  => vObjectName,
                              iErrorCode   => iErrorCode,
                              vErrorString => SQLERRM);

•  Scope—Packaged functions can reference other constructs and objects within the same package without qualifying the reference with a package name.

•  Purity Levels—Functions defined within packages can’t be executed successfully inside DML statements, unless a purity level for the function is defined within the package spec. Purity levels are discussed in detail in the next section of this chapter.

Like procedures, functions aren’t created within a package spec, but the interface for ad the function is defined within a spec. Once a function has been defined in a package spec, the function must be created within the package body before the package body will compile.