Re: update in function

You have to declare:
pragma restrict_references(get_banner,WNDS);

That sould work...

WNDS stands for Write No Database State. You can't use a function that alter the database in a select,an insert ... But Oracle is not able to detect if a function alter or not the database. So you have to declare that your function doesn't alter the base ...

Maxim Ovchinnikov a écrit dans le message <3A129B2F.86AAED5D_at_aaanet.ru>...
>Hi!
>I'm studing Oracle & I've some problems.I need your help.
>I have a table:
>==================
>create table banner (
> id number(5) primary key,
> ref varchar2(1000),
> hits number(5) NOT NULL);
>==================
>& I wrote a function:
>============================
>create function get_banner return varchar2 is
>cursor c is select id,ref from banner where hits=(select min(hits) from
>banner);
>url banner.ref%type;
>number banner.id%type;
>begin
> open c;
> fetch c into number,url;
> close c;
> update banner set hits=hits+1 where id=number;
> return url;
>end;
>========================
>As you can see this function should extract 'url' of a banner with
>lowest 'hits' number & increase last one by one.However when I try to
>execute my fucntion, calling 'select get_banner from dual'
>Oracle refuse to fulfil it:
>'ORA-06571: Function GET_BANNER does not guarantee not to update
>database'
>
>Explain to me please.How can I solve this problem & why can't I use
>update statement in this function?
>
>Thanks.
>
>
>
Received on Wed Nov 15 2000 - 08:54:39 CST