Re: Using stored function in SELECT statement

"Victor Z. Krokhmal" <victor_at_dwayne.FreeNet.Kiev.UA> 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;

Victor,

Take a look at the Server Applications Development Guide for Oracle 7.2. There's an inbuilt pragma that you need to use to tell Oracle what database access the procedure/function needs. By default Oracle assumes that any stored procedure/function updates the database.

Here's an example...

CREATE OR REPLACE
  FUNCTION mult ( x INTEGER , y INTEGER ) RETURN INTEGER IS   BEGIN
    RETURN ( x * y ) ;
  END ; PRAGMA RESTRICT_REFERENCES ( mult , WNDS , RNDS ) ;

The weird WNDS ( The winds of New England :-} ) and RNDS tell Oracle what the function does to the database. There are 4 modifiers...

WNDS - Write No Database State - Doesn't update the database
WNPS - Write No Package State - Doesn't change any package vars
RNDS - Reads no database tables
RNPS - Reads no package variables.

You can pass these args in any order, and include any you wish, but you MUST include WNDS whatever. There is some other interesting stuff in the manual regarding calling functions from SQL statements - it's well worth a read.

Hope this helps! Received on Sat May 25 1996 - 00:00:00 CEST