Re: Using stored function in SELECT statement

Victor Z. Krokhmal wrote:
>
> Hi there,
>
> I am having difficulty in using stored function in SELECT statement.
> And next example returns error :
> ORA-06571 "Function does not guarantee to not update database".
>
> SELECT var1 FROM tab1
> WHERE Func1(var2)=0;
>
> Therefore, could anyone out there help me to solve this problem.
>
> Thanks for any advice.
>
> ---
> €Š Victor Z. Krokhmal Š email: victor_at_dwayne.freenet.kiev.ua Š€
> Š Oracle Database Administrator Š phone: +7 (044) 212-54-81 /faxmodem/ Š
> ‚Š State Tax Inspectorate - Ukraine Š Š‚
I received a similar error when trying to use stored functions I put into a package. My functions worked fine as stand alones, but not in the package. Since a SELECT is a read and needs only read privileges, you must guarantee to ORACLE that your function won't try to perform an UPDATE. I had to add the following after the declaration of the function in the package declaration:

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

Good luck.

Steve Gutterman
sgutterman_at_dreamworks.com Received on Fri May 24 1996 - 00:00:00 CEST