Oracle Merge语句触发ORA-02012: missing USING keyword错误求助

错误原因 #

你遇到的ORA-02012错误虽然官方提示和数据库链接的USING子句有关，但实际是Merge语句的语法不符合Oracle要求：Oracle要求USING子句后的数据源（无论是表、视图还是子查询）必须用括号包裹起来，你当前的写法直接引用表名，缺少括号导致解析错误。

修正后的语句 #

merge into GlassesGesDev.VEHICLEACCESSORYDETAILS current 
using (select * from VehicleAccessoryTest) new  -- 添加括号包裹表
on  (current.VehicleId = new.Vehicle and current.OptionCode = new.OptionCode and current.EffectiveDate = new.EffectiveDate)
when not matched then
    insert (VehicleId, OptionCode, EffectiveDate, OptionType, OptionPriceNew, OptionPriceDepreciated, Deleted, CreatedDate, CreatedUser, ModifiedDate, ModifiedUser, ImportedToGuideWire)
    values (new.VehicleCode, new.OptionCode, new.Effective_Date, new.OptionType, new.RrpAmount, new.RetailAmount, 'N', current_timestamp, USER, current_timestamp, USER, 'N')
when matched then
    update 
    set OptionType = new.OptionType,
        OptionPriceNew = new.RrpAmount,
        OptionPriceDepreciated = new.RetailAmount,
        ModifiedDate = current_timestamp,
        ModifiedUser = USER;

补充说明 #

如果USING后的数据源是子查询，本身就有括号，不会出现这个问题；但直接引用表时，必须显式用括号把表包裹成子查询形式，这是Oracle Merge语法的硬性要求，和数据库链接无关，属于错误提示的误导性关联。

内容的提问来源于stack exchange，提问作者Ray