Purity Levels

{\rtf1\ansi\ansicpg1252\deff0\deflang16393{\fonttbl{\f0\froman\fprq2\fcharset0 Times New Roman;}{\f1\fnil\fcharset0 Calibri;}}
{\*\generator Msftedit 5.41.21.2509;}\viewkind4\uc1\pard\li720\lang1033\f0\fs18 Prior to Oracle8\i i\i0 Release 8.1, it was necessary to assert the purity level of a packaged procedure or function when using it directly or indirectly in a SQL statement. Beginning with Oracle8\i i\i0 Release 8.1, the PL/SQL runtime engine determines a program's purity level automatically if no assertion exists.\par
The RESTRICT_REFERENCES pragma asserts a purity level. The syntax for the RESTRICT_REFERENCES pragma is:\par
PRAGMA RESTRICT_REFERENCES (program_name | \par
DEFAULT, purity_level);\par
The keyword DEFAULT applies to all methods of an object type or all programs in a package. \par
There can be from one to five purity levels, in any order, in a comma-delimited list. The purity level describes to what extent the program or method is free of \i side effects\i0 . Side effects are listed in the following table with the purity levels they address. \par
\pard\li360\par
\par
\trowd\trgaph45\trleft630\trbrdrl\brdrdash\brdrw15 \trbrdrt\brdrdash\brdrw15 \trbrdrr\brdrdash\brdrw15 \trbrdrb\brdrdash\brdrw15 \trpaddl45\trpaddr45\trpaddfl3\trpaddfr3
\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx2055\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx3851\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx9450\pard\intbl\b Purity Level\cell Description\cell Restriction\cell\row\trowd\trgaph45\trleft630\trbrdrl\brdrdash\brdrw15 \trbrdrt\brdrdash\brdrw15 \trbrdrr\brdrdash\brdrw15 \trbrdrb\brdrdash\brdrw15 \trpaddl45\trpaddr45\trpaddfl3\trpaddfr3
\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx2055\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx3851\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx9450\pard\intbl\b0 WNDS\cell Write No Database State\cell Executes no INSERT, UPDATE, or DELETE statements.\cell\row\trowd\trgaph45\trleft630\trbrdrl\brdrdash\brdrw15 \trbrdrt\brdrdash\brdrw15 \trbrdrr\brdrdash\brdrw15 \trbrdrb\brdrdash\brdrw15 \trpaddl45\trpaddr45\trpaddfl3\trpaddfr3
\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx2055\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx3851\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx9450\pard\intbl RNDS\cell Read No Database State\cell Executes no SELECT statements.\cell\row\trowd\trgaph45\trleft630\trbrdrl\brdrdash\brdrw15 \trbrdrt\brdrdash\brdrw15 \trbrdrr\brdrdash\brdrw15 \trbrdrb\brdrdash\brdrw15 \trpaddl45\trpaddr45\trpaddfl3\trpaddfr3
\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx2055\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx3851\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx9450\pard\intbl WNPS\cell Write No Package State\cell Does not modify any package variables.\cell\row\trowd\trgaph45\trleft630\trbrdrl\brdrdash\brdrw15 \trbrdrt\brdrdash\brdrw15 \trbrdrr\brdrdash\brdrw15 \trbrdrb\brdrdash\brdrw15 \trpaddl45\trpaddr45\trpaddfl3\trpaddfr3
\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx2055\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx3851\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx9450\pard\intbl RNPS\cell Read No Package State\cell Does not read any package variables.\cell\row\trowd\trgaph45\trleft630\trbrdrl\brdrdash\brdrw15 \trbrdrt\brdrdash\brdrw15 \trbrdrr\brdrdash\brdrw15 \trbrdrb\brdrdash\brdrw15 \trpaddl45\trpaddr45\trpaddfl3\trpaddfr3
\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx2055\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx3851\clbrdrl\brdrw15\brdrs\clbrdrt\brdrw15\brdrs\clbrdrr\brdrw15\brdrs\clbrdrb\brdrw15\brdrs \cellx9450\pard\intbl TRUST (Oracle8\i i\i0 )\cell\~\cell Does not enforce the restrictions declared but allows the compiler to trust they are true.\cell\row\pard\li360\par
\pard\li720 The purity level requirements for packaged functions are different depending on where in the SQL statement the stored functions are used:\par
To be called from SQL, all stored functions must assert WNDS. \par
All functions not used in a SELECT, VALUES, or SET clause must assert WNPS. \par
To be executed remotely, the function must assert WNPS and RNPS. \par
To be executed in parallel, the function must assert all four purity levels or, in Oracle8\i i\i0 , use PARALLEL_ENABLED in the declaration. \par
These functions must not call any other program that does not also assert the minimum purity level. \par
If a package has an initialization section, it too must assert purity in Oracle7. \par
If a function is overloaded, each overloading must assert its own purity level, and the levels don't have to be the same. To do this, place the pragma immediately after each overloaded declaration. \par
Many of the built-in packages, including DBMS_OUTPUT, DBMS_PIPE, and DBMS_SQL, do not assert WNPS or RNPS, so their use in SQL stored functions is necessarily limited. \par
\pard\par
\pard\sa200\sl276\slmult1\lang9\f1\fs22\par
}