开发人员在备库使用DB LINK跨库查询时遇到ORA-01861和ORA-02063错误。

SQL> with RST_WMS AS 
  2  -- T_WH_NO REP_NO REP_NUM WMS_NO F_WH_NO SY_RESOURCE SKU YQ_QTY QTY GB_LVL 
  3  (SELECT a.ERP单号 as REP_NO, 
  4  a.ERP行号 as REP_NUM, 
  5  a.WMS单号 as WH_NO, 
  6  a.WMS行号 as WMS_NUM, 
  7  a.目标仓库 as T_WH_NO, 
  8  a.来源仓库 as F_WH_NO, 
  9  a.货主 as SY_RESOURCE, 
 10  a.SKU as SKU, 
 11  '' as YQ_QTY, 
 12  a.在途数量 as QTY, 
 13  case 
 14  when b.lotatt08 = '00' then 
 15  'G' 
 16  ELSE 
 17  'B' 
 18  end GB_LVL 
 19  --to_date(A.日期) 
 20  FROM view_edi_erp_invonline@WMS a 
 21  left join doc_asn_details@WMS b 
 22  on a.WMS单号 = b.asnno 
 23  and a.WMS行号 = b.asnlineno 
 24  WHERE to_date(a.日期) = date '2016-06-27') 
 25  SELECT * FROM RST_WMS;
with RST_WMS AS
*
ERROR at line 1:
ORA-01861: literal does not match format string
ORA-02063: preceding line from WMS

从报错信息看是由于字段类型不匹配导致的，查找资料发现，通过DB LINK跨库查询，如果日期类型设置不一致很可能遇到这个错误，经过在其他备库验证，发现设置了NLS_DATE_FORMAT参数为’YYYY-MM-DD HH24:MI:SS’格式的数据库均可以查询，刚巧这个备库没有设置，设置后，问题解决。

SQL> alter session  set  nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> with RST_WMS AS 
  2  -- T_WH_NO REP_NO REP_NUM WMS_NO F_WH_NO SY_RESOURCE SKU YQ_QTY QTY GB_LVL 
  3  (SELECT a.ERP单号 as REP_NO, 
  4  a.ERP行号 as REP_NUM, 
  5  a.WMS单号 as WH_NO, 
  6  a.WMS行号 as WMS_NUM, 
  7  a.目标仓库 as T_WH_NO, 
  8  a.来源仓库 as F_WH_NO, 
  9  a.货主 as SY_RESOURCE, 
 10  a.SKU as SKU, 
 11  '' as YQ_QTY, 
 12  a.在途数量 as QTY, 
 13  case 
 14  when b.lotatt08 = '00' then 
 15  'G' 
 16  ELSE 
 17  'B' 
 18  end GB_LVL 
 19  --to_date(A.日期) 
 20  FROM view_edi_erp_invonline@WMS a 
 21  left join doc_asn_details@WMS b 
 22  on a.WMS单号 = b.asnno 
 23  and a.WMS行号 = b.asnlineno 
 24  WHERE to_date(a.日期) = date '2016-06-27') 
 25  SELECT * FROM RST_WMS;

REP_NO          REP_NUM    WH_NO      WMS_NUM    T_WH_NO    F_WH_NO    SY_RESOURC SKU        Y        QTY GB_
--------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- - ---------- ---
201606278798    102        1024210535 1          C03        C05        UGO        5285021103            6 G
201606208719    102        1024187943 1          C03        C04        UGO        5285021103            6 G
201606248792    104        1024202085 2          C05        C07        UGO        5015251110            8 G
201606248793    102        1024202086 1          C04        C07        UGO        5015251106            8 G
201606248791    102        1024202087 1          C05        C07        UGO        5406871014           75 G
201606248792    106        1024202085 3          C05        C07        UGO        5015251075            4 G
201606218735    104        1024192501 2          C03        C07        UGO        5218581013           80 G
201606278783    102        1024210693 1          C04        C07        UGO        5285021103           18 G
201606278797    102        1024210692 1          C05        C07        UGO        5285021114            6 G
201606218741    104        1024191214 2          C03        C05        UGO        3593921030           50 G
201606248777    106        1024202084 3          C01        C07        UGO        5015251042            4 G

本文固定链接: https://www.dbdream.com.cn/2016/07/%e4%bd%bf%e7%94%a8dblink%e8%b7%a8%e5%ba%93%e6%9f%a5%e8%af%a2%e9%81%87%e5%88%b0ora-01861ora-02063%e9%94%99%e8%af%af/ | 信春哥，系统稳，闭眼上线不回滚！