Oracle 11g使用MERGE報錯'ORA-02287..The specified sequence number (CURRVAL'

feelpurple發表於2017-02-07
原文網址 : http://blog.itpub.net/26506993/viewspace-2133187/
Oracle
執行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);

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26506993/viewspace-2133187/，如需轉載，請註明出處，否則將追究法律責任。

相關文章