Table,View Or Sequence reference 'SEQUENCE.NEXTVAL' not allowed in this context

sql oracle sequence oracle9i
mounaim picture mounaim · Mar 19, 2014 · Viewed 9k times · Source

I'm having this error while trying to compile the following trigger :

CREATE OR REPLACE TRIGGER INCREMENTER_ID_CONSISTANCE 

BEFORE INSERT ON BD.CONSISTANCE 
for each row
BEGIN
:new.code := ID_CONSISTANCE.nextval;
END;  


**ERROR** : Table,View Or Sequence reference 'ID_CONSISTANCE.nextval' not allowed in
this context

What is the problem here ? and how can I fix this ?

Answer

a_horse_with_no_name picture a_horse_with_no_name · Mar 19, 2014

This syntax is only allowed in Oracle 11 or later.

The (unsupported and outdated) 9i version did not support direct assignment of a sequence value.

You need to use a select into instead:

select ID_CONSISTANCE.nextval
  into :new.code 
FROM dual;

And you should really plan an upgrade to a current version of Oracle (11.x or 12.x)

Related questions

How do I reset a sequence in Oracle?

In PostgreSQL, I can do something like this: ALTER SEQUENCE serial RESTART WITH 0; Is there an Oracle equivalent?

Read More
How to retrieve the current value of an oracle sequence without increment it?

Is there an SQL instruction to retrieve the value of a sequence that does not increment it. Thanks. EDIT AND CONCLUSION As stated by Justin Cave It's not useful to try to "save" sequence number so select a_seq.nextval …

Read More
How to create an Oracle sequence starting with max value from a table?

Trying to create a sequence in Oracle that starts with the max value from a specific table. Why does this not work? CREATE SEQUENCE transaction_sequence MINVALUE 0 START WITH (SELECT MAX(trans_seq_no) FROM TRANSACTION_LOG) INCREMENT BY 1 CACHE 20;

Read More