Oracle 11g使用MERGE报错'ORA-02287..The specified sequence number (CURRVAL'
执行SQL语句报错
MERGE INTO OAPS_PARAMETERS a
USING (select SEQ_OAPS_PARAMETER.NEXTVAL N_ID,'OSMSR' C_PRODUCT_TYPE,'core' C_GROUP_NAME,'sendAdminAgentParamsUpdate' C_PARAM_NAME,'true' C_PARAM_VALUE,'true' C_PARAM_DEFAULT,0 N_CATEGORY,0 N_PARAMETER_TYPE,
'whether AdminAgentParamsUpdate APDU is sent (true) or not sent (false) during SM-SR change' C_DESCRIPTION,2 N_ACCESS_TYPE,'System' C_PARAM_NAME_SUFFIX,systimestamp D_TIMESTAMP from dual) b
ON (a.C_PARAM_NAME = b.C_PARAM_NAME)
WHEN NOT MATCHED THEN
insert (N_ID,C_PRODUCT_TYPE,C_GROUP_NAME,C_PARAM_NAME,C_PARAM_VALUE,C_PARAM_DEFAULT,N_CATEGORY,N_PARAMETER_TYPE,C_DESCRIPTION,N_ACCESS_TYPE,C_PARAM_NAME_SUFFIX,D_TIMESTAMP) values
(b.N_ID,b.C_PRODUCT_TYPE,b.C_GROUP_NAME,b.C_PARAM_NAME,b.C_PARAM_VALUE,b.C_PARAM_DEFAULT,b.N_CATEGORY,
b.N_PARAMETER_TYPE,b.C_DESCRIPTION,b.N_ACCESS_TYPE,b.C_PARAM_NAME_SUFFIX,b.D_TIMESTAMP);
SQL 错误: ORA-02287: 此处不允许序号
02287. 00000 - "sequence number not allowed here"
*Cause: The specified sequence number (CURRVAL or NEXTVAL) is inappropriate
here in the statement.
*Action: Remove the sequence number.
报错原因：
在USING语句中不能使用序列
解决方法：
将序列放到下方的INSERT语句中
MERGE INTO OAPS_PARAMETERS a
USING (select 'OSMSR' C_PRODUCT_TYPE,'core' C_GROUP_NAME,'sendAdminAgentParamsUpdate' C_PARAM_NAME,'true' C_PARAM_VALUE,'true' C_PARAM_DEFAULT,0 N_CATEGORY,0 N_PARAMETER_TYPE,
'whether AdminAgentParamsUpdate APDU is sent (true) or not sent (false) during SM-SR change' C_DESCRIPTION,2 N_ACCESS_TYPE,'System' C_PARAM_NAME_SUFFIX,systimestamp D_TIMESTAMP from dual) b
ON (a.C_PARAM_NAME = b.C_PARAM_NAME)
WHEN NOT MATCHED THEN
insert (N_ID,C_PRODUCT_TYPE,C_GROUP_NAME,C_PARAM_NAME,C_PARAM_VALUE,C_PARAM_DEFAULT,N_CATEGORY,N_PARAMETER_TYPE,C_DESCRIPTION,N_ACCESS_TYPE,C_PARAM_NAME_SUFFIX,D_TIMESTAMP) values
(SEQ_OAPS_PARAMETER.NEXTVAL,b.C_PRODUCT_TYPE,b.C_GROUP_NAME,b.C_PARAM_NAME,b.C_PARAM_VALUE,b.C_PARAM_DEFAULT,b.N_CATEGORY,
b.N_PARAMETER_TYPE,b.C_DESCRIPTION,b.N_ACCESS_TYPE,b.C_PARAM_NAME_SUFFIX,b.D_TIMESTAMP);
MERGE INTO OAPS_PARAMETERS a
USING (select SEQ_OAPS_PARAMETER.NEXTVAL N_ID,'OSMSR' C_PRODUCT_TYPE,'core' C_GROUP_NAME,'sendAdminAgentParamsUpdate' C_PARAM_NAME,'true' C_PARAM_VALUE,'true' C_PARAM_DEFAULT,0 N_CATEGORY,0 N_PARAMETER_TYPE,
'whether AdminAgentParamsUpdate APDU is sent (true) or not sent (false) during SM-SR change' C_DESCRIPTION,2 N_ACCESS_TYPE,'System' C_PARAM_NAME_SUFFIX,systimestamp D_TIMESTAMP from dual) b
ON (a.C_PARAM_NAME = b.C_PARAM_NAME)
WHEN NOT MATCHED THEN
insert (N_ID,C_PRODUCT_TYPE,C_GROUP_NAME,C_PARAM_NAME,C_PARAM_VALUE,C_PARAM_DEFAULT,N_CATEGORY,N_PARAMETER_TYPE,C_DESCRIPTION,N_ACCESS_TYPE,C_PARAM_NAME_SUFFIX,D_TIMESTAMP) values
(b.N_ID,b.C_PRODUCT_TYPE,b.C_GROUP_NAME,b.C_PARAM_NAME,b.C_PARAM_VALUE,b.C_PARAM_DEFAULT,b.N_CATEGORY,
b.N_PARAMETER_TYPE,b.C_DESCRIPTION,b.N_ACCESS_TYPE,b.C_PARAM_NAME_SUFFIX,b.D_TIMESTAMP);
SQL 错误: ORA-02287: 此处不允许序号
02287. 00000 - "sequence number not allowed here"
*Cause: The specified sequence number (CURRVAL or NEXTVAL) is inappropriate
here in the statement.
*Action: Remove the sequence number.
报错原因：
在USING语句中不能使用序列
解决方法：
将序列放到下方的INSERT语句中
MERGE INTO OAPS_PARAMETERS a
USING (select 'OSMSR' C_PRODUCT_TYPE,'core' C_GROUP_NAME,'sendAdminAgentParamsUpdate' C_PARAM_NAME,'true' C_PARAM_VALUE,'true' C_PARAM_DEFAULT,0 N_CATEGORY,0 N_PARAMETER_TYPE,
'whether AdminAgentParamsUpdate APDU is sent (true) or not sent (false) during SM-SR change' C_DESCRIPTION,2 N_ACCESS_TYPE,'System' C_PARAM_NAME_SUFFIX,systimestamp D_TIMESTAMP from dual) b
ON (a.C_PARAM_NAME = b.C_PARAM_NAME)
WHEN NOT MATCHED THEN
insert (N_ID,C_PRODUCT_TYPE,C_GROUP_NAME,C_PARAM_NAME,C_PARAM_VALUE,C_PARAM_DEFAULT,N_CATEGORY,N_PARAMETER_TYPE,C_DESCRIPTION,N_ACCESS_TYPE,C_PARAM_NAME_SUFFIX,D_TIMESTAMP) values
(SEQ_OAPS_PARAMETER.NEXTVAL,b.C_PRODUCT_TYPE,b.C_GROUP_NAME,b.C_PARAM_NAME,b.C_PARAM_VALUE,b.C_PARAM_DEFAULT,b.N_CATEGORY,
b.N_PARAMETER_TYPE,b.C_DESCRIPTION,b.N_ACCESS_TYPE,b.C_PARAM_NAME_SUFFIX,b.D_TIMESTAMP);
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26506993/viewspace-2133187/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- MySQL执行语句报Incorrect key file for table '/tmp/#sql_.MYI'; try to repair it
- MySQL启动报错InnoDB: The innodb_system data file './ibdata1' is of a different
- Redis 5.02官方集群搭建
- MySQL报错Table 'plugin' is read only [ERROR] Can't open the mysql.plugin table.
- MySQL报错Slave: received end packet from server, apparent master shutdown
- MySQL 5.6因为OOM导致数据库重启
- Django报错ValueError: invalid literal for int() with base 10:
- MySQL 5.6复制报错Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;
- MySQL [ERROR] Slave I/O: Found a Gtid_log_event or Previous_gtids_log_event
- MySQL 5.6执行DDL报错ERROR 1050 (42S01): Table 'sms/#sql-ib752' already exist
