Re: update in function

Thanks, Marc.
But unfurtunately your procedure isn't compiled by Oracle(I work under Oracle8 Enterprise Edition Release 8.0.5.1.0).
Oracle doesn't like this sting:' pragma autonomous_transaction;' Btw what is 'Oracle 8i'?
Is there a way to solve this problem somehow else? The problem is that I can write any SQL/PL_SQL code on Oracle server & client program.But I can't change client program code('cause it's compiled).So this
client program reads sql queries(which I write in its configurartion file) & then uses them in 'DECLARE CURSOR' together with 'FETCH' to get data. As far as I know 'FETCH' allows me to retrieve data only in 'SELECT' clause, or may be I'm wrong?

Any way.Do you have any ideas about how can I solve this problem?

Marc Billiet wrote:

> 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 Fri Nov 17 2000 - 08:24:44 CST