Re: PL/SQL Pragmas

jychan_at_corp.hp.com (Contractor - Yuk Hon "Johnny" Chan) wrote:
>Brian C Hogue (paubh4+_at_pitt.edu) wrote:
>: Hi There,
 

>: I'm working with create a stored function. I got the
>: function to work for my account. I got this error when trying
>: to use it from another account, to which I had granted execute on
>: the stored function.
 

>: from the error messages file:
>: 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
 

>: The only pragma I've found documented is one called EXCEPTION_INIT.
>: Can someone point me to where the rest of the pragmas are documented?
>

You need to use pragma restrict_references (function_name,

                  WNDS, RNDS, WNPS, RNPS);

where:

WNDS= Write No Database State
RNDS= Read No Database State
WNPS= Write No Package State
RNPS= Read No Package State

This pragma is documented with the PL/SQL Addendum Manual for Oracle V7.1.4, and is not in the general PL/SQL 2.x manual.

This pragma is required because you can now reference stored functions and packaged functions in a SELECT clause (and a WHERE, ORDER BY etc too!) and the database needs assurances about what the stored function is doing before it will allow you to reference it.

Hope this helps

dmacpher_at_sas.ab.ca Received on Thu Aug 17 1995 - 00:00:00 CEST