Re: ORA-06571: Function FN_SUP does not guarantee not to update database

A copy of this was sent to Anonymous <nobody_at_newsfeeds.com> (if that email address didn't require changing) On Fri, 30 Jul 1999 15:48:43 +0200, you wrote:

>Im using Oracle 7.3.3 on Solaris.
>
>I write this package :
>
>CREATE OR REPLACE
>PACKAGE PKG_OPERATEUR_LOGIQUE
>IS
> FUNCTION fn_sup ( a in number, b in number) return Integer;

     pragma restrict_references( fn_sup, WNDS, RNDS, WNPS, RNPS );

     pragma restrict_references( pkg_operateur_logique, wnds, rnds, wnps, rnps);
>END;
>/
>

When you put a function in a package, you need to tell us if it

WNDS - write no database state (doesn't do inserts/updates/creates/alters/etc)
WNPS - write no package state (doesn't write to package variables)
RNDS - reads no database state (doesn't do selects and such)
RNPS - reads no package state (doesn't read package global variables)

At a minimum, in Oracle8.0 and below, a function must assert WNDS to be called from SQL. The others let you call it from other places (to be called from a where clause you must also promise to WNPS) and/or to be parralelized...

>CREATE OR REPLACE
>PACKAGE BODY PKG_OPERATEUR_LOGIQUE
>IS
>FUNCTION fn_sup ( a number, b number) return Integer is
>begin
>
> if a > b then
> return 1;
> else
> return 0;
> end if;
>
>EXCEPTION
> WHEN OTHERS THEN
> RAISE;
>END fn_sup;
>
>END PKG_OPERATEUR_LOGIQUE;
>/
>
>
>When I make : select decode(PKG_OPERATEUR_LOGIQUE.fn_sup(1,0),1,1,0) from
>dual;
>I obtain the error : ORA-06571: Function FN_SUP does not guarantee not to
>update database
>
>Help me please !!!
>
>
>
>Thank
>
>
>
>
>
>
>
>
> --------== Posted Anonymously via Newsfeeds.Com ==-------
> Featuring the worlds only Anonymous Usenet Server
> -----------== http://www.newsfeeds.com ==----------

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jul 30 1999 - 08:54:54 CDT