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



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.

Table 6.1 Purity levels for packaged functions.

Purity Level



The function doesn’t alter the contents of any database table.


The function doesn’t read the contents of any database table.


The function doesn’t alter any variables within another package.


The function doesn’t 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
   TYPE MessageParts_type IS TABLE OF varchar2 (20)
     INDEX BY binary_integer;
   vLastError                  varchar2 (100);
   THIS_PACKAGE     CONSTANT   varchar2 (13) := 'System_Errors';
   FUNCTION Build_Message (vObjectName  IN     varchar2,
                           iErrorCode   IN     integer,
                           vErrorString IN     varchar2)
      RETURN varchar2;
                                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
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.


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