Oracle中使用Merge语句与DBLink需注意的问题

Oracle提供的Merge into语句非常强大，能在一个语句中经判定条件而进行update和insert等操作。这里有个坑，一旦你使用含有DBLink的表，就有可能出现"ORA-01008: not all variables bound" 错误。见下面例子语句：

merge into ucr_sta_chnl.mapp_ext_attr_usage@ngsta aa
using (select *
         from bill.grid_dev_report_point_rate
        where timest = to_char(sysdate - 1, 'yyyymmdd')) bb
on (aa.ext_attr_obj_id = bb.depart_id and aa.eparchy_code = '0758' and aa.ext_attr_id = 41)
when not matched then
  insert
  values
    (ucr_sta_chnl.seq_mapp_ext_attr_usage.nextval@ngsta,
     '1002',
     bb.depart_id,
     41,
     'MONTH_SALES',
     null,
     'GRP',
     '当月销量',
     '当月销量',
     'ZQAD0001',
     sysdate,  --注意这里
     'ZQAD0001',
     sysdate,  --注意这里
     '0758');

估计是因为横跨了两个数据库，所以sysdate这个变量参数未能进行绑定传递，所以必须要做一些变通，把变量变成常量后即可，修改后的语句如下：

merge into ucr_sta_chnl.mapp_ext_attr_usage@ngsta aa
using (select depart_id, sysdate as "SYS_DATE" --注意这里
         from bill.grid_dev_report_point_rate
        where timest = to_char(sysdate - 1, 'yyyymmdd')) bb
on (aa.ext_attr_obj_id = bb.depart_id and aa.eparchy_code = '0758' and aa.ext_attr_id = 41)
when not matched then
  insert
  values
    (ucr_sta_chnl.seq_mapp_ext_attr_usage.nextval@ngsta,
     '1002',
     bb.depart_id,
     41,
     'MONTH_SALES',
     null,
     'GRP',
     '当月销量',
     '当月销量',
     'ZQAD0001',
     bb.sys_date, --注意这里
     'ZQAD0001',
     bb.sys_date, --注意这里
     '0758');