Re: Updates with sequences : not allowed ?

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/04/20
Message-ID: <8dncgu$r4h$1@nnrp1.deja.com>#1/1

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 Thu Apr 20 2000 - 00:00:00 CDT