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

小鱼儿 2024-04-18 00:29 106阅读 0赞

执行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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2133187/