sequence number not allowed here in same session

Question

How can i get current rule id in a same session because

select ID from rules where ID=rules_seq.currval

is throwing error :

ORA-02287: sequence number not allowed here

score 3 · Answer 1 · answered May 18 '17 at 07:27

3

You should save the sequence value when it is created using NEXTVAL.

For example if the NEXTVAL is used in INSERT there is a RETURNING INTO clause for this purpose.

 INSERT INTO my_tab VALUES (seq.nextval, ...)
 RETURNING id INTO v_id;

answered May 18 '17 at 07:27

Marmite Bomber

19,886
4
26
53

APC · Answer 2 · 2017-05-18T07:38:38.223

It's a documented limitation of sequences that we cannot use them in a WHERE clause. Check it here.

What some may find surprising is that this extends to sub-queries (also documented, as @MarmiteBomber points out):

SQL> select * from t23
  2  where t23.id = (select s23.currval from dual)
  3  /
where t23.id = (select s23.currval from dual)
                           *
ERROR at line 2:
ORA-02287: sequence number not allowed here


SQL>

Of course there is always PL/SQL but it's not very convenient.

SQL> set serveroutput on
SQL> declare
  2      n pls_integer;
  3      x pls_integer;
  4  begin
  5       n := s23.currval;
  6      select id into x
  7      from t23
  8      where id = n;
  9      dbms_output.put_line('x='||x);
 10  end;
 11  /
x=5

PL/SQL procedure successfully completed.

SQL>

Well, the exclusion in subqueies is documented as well, so I'd say it's awkward, but not surprising. The reason IMO is that the whole query must guarantie to deliver identical `NEXTVAL` in all calls; which is trivial in `select seq.nextval a, seq.nextval b from dual` , but less trivial in `select * from (select seq.nextval a from dual),(select seq.nextval b from dual)` — Marmite Bomber, May 18 '17 at 07:37

score 0 · Answer 3 · answered May 18 '17 at 06:25

0

if ID is primary key or other index you can use index descending to get maximum id

select --+ index_desc (r)
ID 
from rules r 
where rownum=1

answered May 18 '17 at 06:25

Vecchiasignora

1,275
7
6

Rene · Answer 4 · 2017-05-18T06:10:37.930

Maybe you can select the highest ID.

select max(ID) from rules

This will also get you around the problem that not all numbers generated by the sequence are actually used in a record.

On the other hand, if, in your session, the sequence was used. Why not

select rules_seq.currval from dual

And finally, if this all takes place in PLSQL you can either return the generated the ID into a variable:

declare
   l_id rules.id%type;
begin
  insert into rules (id) 
   values (rules_seq.nextval) 
  returning id into l_id;
end;

or assign the sequence value to the variable before the insert.

declare
       l_id rules.id%type;
    begin
      l_id := rules_seq.nextval;
      insert into rules (id) 
       values (l_id);
    end;

You're assuming that sequence values are always returned in sequential order. They're not. — Jeffrey Kemp, May 19 '17 at 04:04

sequence number not allowed here in same session

4 Answers4