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

Answer 1

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;

Answer 2

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;

Answer 3

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> 

Answer 4

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