程式在执行一句SQL: SELECT to_char(mcs_trx_seq.NEXTVAL) mcs_trx_seq FROM dual 偶尔会报出 ORA-02287: sequence number not allowed here 的错误讯息。 当然只是很偶尔才会有发生,猜测问题出现在DB环境那里,还请高手不吝赐教~
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 -
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 -
这个sql一直在执行,绝大部分时候是成功的,几乎一年都没有出过错误,但最近两周报错过十几次,问题不清楚,还请帮忙看看,或分析下该从哪方面去考虑。
用Toad查看这个SEQUENCE 是:
CREATE SEQUENCE MCS_TRX_SEQ
START WITH 300
MAXVALUE 999
MINVALUE 0
CYCLE
NOCACHE
NOORDER;
2. 把catche的值修改到20
这里应该是应用sequence的地方的问题。把你的实际的代码贴出来看看。