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 ofINSERT
statements- The
SELECT
list of aSELECT
statement - The
SET
clause of anUPDATE
statement
CURRVAL
and NEXTVAL
cannot be used in these places:- A subquery
- A view query or materialized view query
- A
SELECT
statement with theDISTINCT
operator - A
SELECT
statement with aGROUP
BY
orORDER
BY
clause - A
SELECT
statement that is combined with anotherSELECT
statement with theUNION,
INTERSECT
, orMINUS
set operator - The
WHERE
clause of aSELECT
statement DEFAULT
value of a column in aCREATE
TABLE
orALTER
TABLE
statement- The condition of a
CHECK
constraint
No comments:
Post a Comment