Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

PL/SQL Procedures and Functions Oracle Tips by Burleson

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.
 

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