|Previous||Table of Contents||Next|
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.
|WNDS||The function doesnt alter the contents of any database table.|
|RNDS||The function doesnt read the contents of any database table.|
|WNPS||The function doesnt alter any variables within another package.|
|RNPS||The function doesnt read any variables within another package.|
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, its best to make your functions as pure as possible.
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 doesnt know that there are actually four functions with the same name.
The package body includes the definition of procedures and functions declared in the package spec and, like the package spec, can also include variables, constants, user-defined exceptions, and user-defined datatypes.
Unlike objects declared in a package spec, variables and other constructs defined within a package body are private to the package body. These constructs can only be referenced by procedures and functions within the package body.
Figure 6.2 illustrates the relationship of objects defined within a package body to a package spec.
Figure 6.2 Relating a package body to a package spec.
|Previous||Table of Contents||Next|