ORA-02287: sequence number not allowed here
来源:互联网 发布:阿里云服务器什么是io 编辑:程序博客网 时间:2024/05/28 15:24
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 -
- ORA-02287: sequence number not allowed here
- ORA-02287: sequence number not allowed here解决
- insert into...select from...ORA-02287: sequence number not allowed here问题
- ORA-02287:此处不允许序号(sequence number not allowed here) 的避免以及强制实现
- ORA-02287:此处不允许序号(sequence number not allowed here) 的避免以及强制实现
- Oracle ORA-00984: column not allowed here
- ORA-01733: virtual column not allowed here
- column not allowed here
- magento:DirectoryIndex not allowed here
- 用merge into 的时候提示 ORA-01733: virtual column not allowed here
- element filtername is not allowed here
- listener Element listener-calss not allowed here
- Element multipart-config is not allowed here
- element filter-name is not allowed here
- element listener-class is not allowed here
- ORA-24323: value not allowed ORA-01089
- Delphi - 关于错误E2002 File Type is not allowed here
- error: a function-definition is not allowed here before ‘{’ token
- Proteus Simulation FAILED due to fatal simulator errors解决办法
- 链表中的节点占多大内存空间
- [转]C# 中的委托和事件
- listview拖动时背景变黑
- getchar()和EOF总结
- ORA-02287: sequence number not allowed here
- 在使用高版本vs 编译MFC的 WINVER and _WIN32_WINNT版本问题
- POJ-3083 Children of the Candy Corn 解题报告
- [转]在线生成条形码(39码、EAN-13)
- android sdk 编译--如何将源代码加入android.jar,以及make原理
- Java通过System.getProperties()获取系统参数
- Android 一些不为人知的小秘密
- 嵌入式系统的基本概念
- Flex4之DataGrid增删改同步数据库及页面数据示例总结