Re: update in function

Ahem ... Yes,effectively, I didn't watch enough the function ... I apologize.

Marc Billiet a écrit dans le message
<20001115.15545633_at_slu40xxx.hae.hydro.com>... Unfortunately, you can't use pragma exception_init(...WNDS) because your function updates the database (so you can't guarantee that it does not update the database).
If you are using Oracle 8i, you can try to execute the update in an autonomous transaction (I hope that you will not get ora-06571 in that case : I didn't test the following):

create procedure inc_hits(p_id in number) is
  pragma autonomous_transaction;
begin
  update banner set hits = hits + 1
  where id = p_id;
  commit;
end;

and call this procedure in your get_banner-function.

create function get_banner return varchar2 is   cursor c is select id,ref from banner order by hits; url banner.ref%type;
number banner.id%type;
begin

   open c;
  fetch c into number,url;
   close c;
    inc_hits(number);
   return url;
end;

Marc

>>>>>>>>>>>>>>>>>> Oorspronkelijk bericht <<<<<<<<<<<<<<<<<<

Op 2000-11-15, 15:18:23, schreef Maxim Ovchinnikov <eeyore_at_aaanet.ru> over het thema update in function:

> 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 - 10:37:11 CST