Re: PL/SQL Purity Levels
Date: 1997/04/22
Message-ID: <335d4b08.1018454_at_newshost>#1/1
On Tue, 22 Apr 1997 16:44:38 -0500, John Hough <q6y_at_ornl.gov> wrote:
>One of our programmers is attempting to write a
>PL/SQL Package of functions that are callable from SQL.
>He knows he must be concerned about the purity level of
>his functions. In order to use his functions in the
>where clause of the sql he believes the must be a purity
>level of WNDS and WNPS. All of the resources we have
>checked (Oracle Application Developers Guide, Oracle Press
>PL/SQL book and the O'Reilly PL/SQL book state the WNPS
>and RNPS mean that you cannot WRITE or READ package var-
>iables "OUTSIDE" the package in which your function resides.
From the Application Developers Guide that comes with 7.3
WNDS means “writes no database state” (does not modify
database tables)
WNPS means “writes no package state” (does not change the
values of packaged variables)
RNDS means “reads no database state” (does not query database
tables)
RNPS means “reads no package state” (does not reference the
values of packaged variables)
This restriction has been tightened up over time. At the time of writing, the sample code may have been OK. In 7.3, the restriction RNPS applies to all packaged variables.
From the 7.3 application developers guide as well:
The function cannot modify database tables; therefore, it cannot execute an INSERT, UPDATE, or DELETE statement.
Functions that read or write the values of packaged variables cannot be executed remotely or in parallel.
Only functions called from a SELECT, VALUES, or SET clause can write the values of packaged variables.
The function cannot call another subprogram that breaks one of the foregoing rules. Also, the function cannot reference a view that breaks one of the foregoing rules. (Oracle replaces references to a view with a stored SELECT operation, which can include function calls.)
the third bullet point above precludes the function below from being called in a where clause.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Apr 22 1997 - 00:00:00 CEST