Re: Using stored function in a SELECT

In article <445362.94Dec16120401_at_aisserver1.llnl.gov>, 445362_at_aisserver1.llnl.gov (Grant Johnson) says:
>
>
>I'm trying to use a stored function from a SELECT statement, a new feature
>in 7.1.3, am getting the following error:
>
>ERROR at line 1:
>ORA-06571: Function VAL_PER_SINGLE does not guarantee not to update database
>
>Which is described as:
>
>06571, 00000, "Function %s does not guarantee not to update database"
>// *Cause: A plsql function that does not have a pragma asserting that it
>// does not write any database state is referenced in a sql statement.
>// Such functions cannot be used in sql statements.
>// *Action: Recreate the function and/or the functions it calls with the pragma
>
>Now the question is, what is the syntax of the pragma to let the db know
>this function is 'safe' ?
>
>I've looked through all the manuals I can get a hold of and can find no
>reference to this new pragma.

The manual to find and read up on is:

	PL/SQL Release 2.1 and Oracle Prcompilers 
	Release 1.6 Addendum
	First Edition

This is a very thin book. (Easy to overlook)

Section 2 PL/SQL Enhancements details calling your functions from SQL statements.

Anyway, the syntax is:

After the function is declared in the package specification you need:

PRAGMA RESTRICT_REFERENCES ( function_name, WNDS, WNPS, RNDS, RNPS);

You must pass at least WNDS.

The meanings are:

WNDS	writes no database state (does not modify database tables)
WNPS	writes no package state (does not change the values of packaged variables)
RNDS	reads no database state (does not query database tables)
RNPS	reads no package state (does not reference the values of packages variables)

"You can pass the arguments in any order, but you must pass the argument WNDS. No argument implies another. For instance, RNPS does not imply WNPS."

"Always assert the highest purity level a function allows."

Good Luck,

Tony Cook  

>
>Thanks in advance.
>
>Grant Johnson Internet: grantj_at_llnl.gov
>Lawrence Livermore National Lab PacBell Net: (510) 423-0406
Received on Mon Dec 19 1994 - 01:30:06 CET