Re: Updates with sequences : not allowed ?

   Thanks again,
  That's the second time you help me :). I tried to improve your idea with a function like :   

  1 create or replace function get_s
  2 (seq_name IN VARCHAR2(30))
  3 return number
  4 as
  5 l_s number;
  6 begin
  7 select seq_name.currval into l_s from dual;   8 return l_s
  9* end;  

  But it does not work... and compiles with errors :(. Anyone has got a better idea ?

"Thomas J. Kyte" wrote:
>
> In article <38FF2983.7A53C851_at_uhb.fr>,
> Joel SEGUILLON <Joel.Seguillon_at_uhb.fr> wrote:
> > Hi,
> > I am still fighting with my SQL scripts and i have a new problem.
 I'm
> > just trying to do something like :
> > Update table1
> > set field1 = 'myvalue'
> > where field2 = table2_sequence.CURRVAL;
> > But SQLplus answers me : ORA-02287 : sequence number not allowed
 here.
> > I tryed to use something like :
> > ... where field2 = (select last_number from user_sequences where
> > sequence_name = 'table2');
> > But it does not work : currval and last_number do not seem to be the
> > same (i had currval = 30 and last_number = 41 :) ).
> >
> > If anyone has got an idea...
> > Thanks.
> >
>
> this is fixed in 8.1 -- you can use the currval directly. Until then,
> you might be able to use something like the following:
>
> scott_at_ORA734.WORLD> create table t ( x int, y int );
> Table created.
>
> scott_at_ORA734.WORLD> create sequence s;
> Sequence created.
>
> scott_at_ORA734.WORLD>
> scott_at_ORA734.WORLD> create or replace function get_s return number
> 2 as
> 3 l_s number;
> 4 begin
> 5 select s.currval into l_s from dual;
> 6 return l_s;
> 7 end;
> 8 /
>
> Function created.
>
> scott_at_ORA734.WORLD> insert into t values ( s.nextval, 0 );
> 1 row created.
>
> scott_at_ORA734.WORLD> update t set y = y+1 where x = s.currval;
> update t set y = y+1 where x = s.currval
> *
> ERROR at line 1:
> ORA-02287: sequence number not allowed here
>
> scott_at_ORA734.WORLD> update t set y = y+1 where x = ( select get_s from
> dual );
>
> 1 row updated.
>
> --
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries
> http://osi.oracle.com/~tkyte/index.html
> --
> Opinions are mine and do not necessarily reflect those of Oracle Corp
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Apr 21 2000 - 00:00:00 CDT