Re: update in function

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> ov er
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) f
 rom
> 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 - 09:54:56 CST