Re: ORA-06571 function name does not guarantee not to update database: pragma

From: <josephsha_at_my-dejanews.com>
Date: Fri, 31 Jul 1998 03:25:09 GMT
Message-ID: <6prdel$2de$1@nnrp1.dejanews.com>

When a function is called from an SQL, the purity level of the function is checked by ORACLE. For package functions restrict refrence pragma can be used to declare the purity level of the function.
In the packaged function you can make a call to pragma-restrict reference with arguments of function name and WNDS. It is illustrated in following example.

tst_pack1.sql with header and body:

---

create or replace package tst_pack1 is
function func (num in number) return number; pragma restrict_references (func, wnds); end tst_pack1;
/
create or replace package body tst_pack1 is function func ( num in number )
return number
as
begin
  return(2);
end;
end tst_pack1;
/

---

Now if you call the function from sql using: select tst_pack1.func(3) from dual;
This will not give the error. If you omit pragma restrict reference then this select will issue the error ORA-06571.

---

WNDS stands for Write No database State.

In article <6po4en$dk4$1_at_news.iconz.co.nz>,   "Sridhar Iyer" <iyers_at_simpl.co.nz> wrote:
> Hi,
>
> I get this message when I use a packaged function.
>
> > ORA-06571 function name does not guarantee not to update database
>
> The cause and action documented by Oracle is as given below:
>
> >Cause: There are two possible causes for this message:
>
> >A SQL statement references a packaged PL/SQL function that does not
> contain a pragma
> >that prevents the database from being updated.
> > A SQL statement references a stand-alone, PL/SQL function that
> contains an instruction to
> > update the database.
>
> >Action: If the referenced function is a packaged PL/SQL function:
> Recreate the PL/SQL
> > function with the required pragma; be certain to include the 'Write No
> Database State'
> >(WNDS) argument in the argument list of the pragma.
>
> >If the referenced function is a stand-alone PL/SQL function: Do not use
> the function.
>
> But the manuals don't tell you the syntax for such pragmas. I have used
> pragmas to declare and handle exception codes.
>
> Can anyone help ???
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Thu Jul 30 1998 - 22:25:09 CDT