Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Purity Levels Oracle Tips by Burleson

An Oracle database cannot determine the work done by a packaged function when the function is executed from inside a DML statement. Therefore, if packaged functions are to be executed from within a DML statement, developers must use a PRAGMA to define a purity level for functions defined as part of the package spec. A PRAGMA is a compiler directive that instructs the compiler to handle code in a specific manner. To define a purity level for a packaged function, the PRAGMA RESTRICT_REFERENCES is used.

A purity level defined within a package spec instructs Oracle about the kinds of operations that the function performs. Table 6.1 lists the four purity levels that can be defined for a function.

Listing 6.5 illustrates how the purity level of a function is defined within a package spec.

Listing 6.5 Defining the purity level of a packaged function.

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)

      RETURN varchar2;

    PRAGMA RESTRICT_REFERENCES (Build_Message,

                                WNDS, RNDS, WNPS, RNPS);

   PROCEDURE Log_Error (vObjectName  IN     varchar2,

                        vErrorString IN     varchar2,

                        vErrorData   IN     varchar2,

                        iErrorCode   IN     integer);

END System_Errors;

Notice the PRAGMA RESTRICT_REFERENCES call in the highlighted portion of code. This defines the purity level for the Build_Message function.

While the Build_Error() function in this example is marked with all four purity levels, any number of levels can be defined for a function. As a general rule, it’s best to make your functions as “pure” as possible.

Overloading Procedures and Functions

Oracle allows developers to overload procedures and functions that are created within packages. An overloaded object is actually several objects that all have the same name, but each object differs from the others in type and/or number of parameters. Listing 6.6 illustrates a package spec that contains the definition for an overloaded function.

Listing 6.6 A package spec containing an overloaded function.

PACKAGE Parse_Strings

IS

iLastDelimiter    integer;

--

-- Get the first word from the string using the

-- standard delimiter.

--

FUNCTION NextWord (vStringToParse IN     varchar2) RETURN varchar2;

--

-- Specify what delimiter will be used to signify the

-- end of the word.

--

FUNCTION NextWord (vStringToParse IN     varchar2,

                   vDelimiter     IN     varchar2);

--

-- Get the n th word from the string.

--

FUNCTION NextWord (vStringToParse IN     varchar2,

                   iWordNumber    IN     integer);

--

-- Get the n th word from the string and specify what

-- delimiter signifies the end of the word.

--

FUNCTION NextWord (vStringToParse IN     varchar2,

                   iWordNumber    IN     integer,

                   vDelimiter     IN     varchar2);

END Parse_Strings;

In this example, the function NextWord() is defined four times, and each definition of the function accomplishes a specific purpose. When a PL/SQL block calls NextWord(), Oracle examines the Parse_Strings package spec and executes the function that corresponds with the type and number of parameters specified in the call. The calling code doesn’t know that there are actually four functions with the same name.
 

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