Total Pageviews

2020/08/06

[Oracle] ORA-02287: sequence number not allowed here

Problem
Assume I create a sequence as bellows:
create sequence seq_test
    start with 1
    increment by 1;


As I try to get the next sequence value in my select statement, I get ORA-02287 error
 select (select lpad(to_char(seq_test.NEXTVAL), 4, '0') from dual) as seq,
        .........
 from ............
 where .........



How-To
1. create a function to get the next sequence
    FUNCTION FN_GET_NEXT_SEQ_VAL RETURN VARCHAR2 IS v_next_seq_val VARCHAR2(4) := '';
    BEGIN
        select lpad(to_char(seq_test.NEXTVAL), 4, '0')
        into v_next_seq_val
        from dual;

        return v_next_seq_val;
    END FN_GET_NEXT_SEQ_VAL;

2. select value from function
    select FN_GET_NEXT_SEQ_VAL() as seq,
           .........
    from ............
    where .........


Reference
Uses and Restrictions of NEXTVAL and CURRVAL
CURRVAL and NEXTVAL can be used in the following places:
  • VALUES clause of INSERT statements
  • The SELECT list of a SELECT statement
  • The SET clause of an UPDATE statement
CURRVAL and NEXTVAL cannot be used in these places:
  • A subquery
  • A view query or materialized view query
  • SELECT statement with the DISTINCT operator
  • SELECT statement with a GROUP BY or ORDER BY clause
  • SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator
  • The WHERE clause of a SELECT statement
  • DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
  • The condition of a CHECK constraint

No comments: