ORA-02287: sequence number not allowed here

 http://devbbs.doit.com.cn/thread-11269-1-1.html

http://zsjg13.iteye.com/blog/737677

 

当插入值需要从另外一张表中检索得到的时候，如下语法的sql语句已经不能完成该功能：
insert into my_table(id, name) values ((select seq_my_table.nextval from dual), ‘runto30′);
会报“ORA-02287: sequence number not allowed here”的错误，可以使用如下语句来完成：
insert into my_table(id, name) select seq_my_table.nextval, ' runto30 ' from dual;

 

或者是

insert into my_table(id, name) values( seq_my_table.nextval, ' runto30 ' )

＝＝＝＝＝＝＝＝＝＝

ORA-02287
From Oracle FAQ
Jump to: navigation, search
ORA-02287: sequence number not allowed here

[edit] What causes this error?
An ORA-02287 occurs when you use a sequence where it is not allowed. The usage of a sequence is limited and it can be used only in few areas of PL/SQL and SQL coding.

[edit] Where can one use sequences
The following are the cases where you can't use a sequence:

For a SELECT Statement:

In a WHERE clause
In a GROUP BY or ORDER BY clause
In a DISTINCT clause
Along with a UNION or INTERSECT or MINUS
In a sub-query
Other areas:

A sub-query of Update or Delete
In a View or snapshot
In a DEFAULT or CHECK Condition of a table definition
Within a single SQL statement that uses CURRVAL or NEXTVAL, all referenced LONG columns, updated tables, and locked tables must be located on the same database.
[edit] How to fix it
If your statement fits one of the above said conditions, then you would see this error being raised. I have some possible solutions:

If you need the sequence value for a WHERE clause or a sub-query or order by, then fetch the sequence value into a variable and use the variable in the necessary places.
If you want the sequence value to be inserted into the column for every row created, then create a before insert trigger and fetch the sequence value in the trigger and assign it to the column
If you want to use a sequence in a view, then first create the view with all other columns and conditions without the sequence and then use the sequence when you actually type in "select a, b, c from view".
If you want to use a sequence in a select query with distinct clause, then you can use distinct in sub query and sequence.nextval in main query as follows -