update in function

From: Maxim Ovchinnikov <eeyore_at_aaanet.ru>
Date: Wed, 15 Nov 2000 17:18:23 +0300
Message-ID: <3A129B2F.86AAED5D@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:18:23 CST