Oracle ORA-02287: sequence number not allowed here
Category : How-to
I’ve recently hit an issue when trying to include a sequence.nextval in an Oracle database view. the database throws the following error:
ORA-02287: sequence number not allowed here
I can see why that might not be allowed – each time you select from the view you’ll get a new sequence number making it a bit daft for most circumstances.
I say most because I’ve had a need recently; ETL logic at my current client is held in database views with a boilerplate set of code that wraps around that to perform the table load. That’s fine until you want to fabricate ID’s (SIDs) as part of the load.
Solution
The solution here was to create a utility package to wrap the sequence that could be referenced in the view. See the below example code:
CREATE SEQUENCE seq_test
MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20
/
CREATE OR REPLACE FUNCTION func_seq_test (p_sequence in VARCHAR2) RETURN NUMBER IS
l_nextval NUMBER;
BEGIN
EXECUTE IMMEDIATE 'select ' || p_sequence || '.nextval from dual'
INTO l_nextval;
RETURN l_nextval;
END;
/
CREATE OR REPLACE VIEW view_seq_test AS
SELECT func_seq_test('seq_test') seq_id
, dummy
FROM dual
/
You can now query the view without any pesky ORAs.
SELECT * FROM view_seq_test
/
SEQ_ID D
---------- -
1 X
1 row selected.
See the test script here.