ORA-01877: string is too long for internal buffer 排查

今天开发说有个程序，页面查询报ora-1877错误，提供了sql那是个页面展示的语句，为了直观缩短处理后如下

SELECT COUNT (*)
  FROM (SELECT a.icpcode, a.discomname
          FROM ehealth_arch_basicinfo a
         WHERE     TO_CHAR (a.BUILD_DATE, 'yyyy-mm-dd') >= '1900-01-01'
               AND TO_CHAR (a.BUILD_DATE, 'yyyy-mm-dd') <= '2011-11-11'
               AND (a.ext_field1 IS NULL OR a.ext_field1 = '0')) b,
       node_center_region c
 WHERE c.cityid = 220800 AND b.icpcode = c.nodeid;
 
ORA-01877: string is too long for internal buffer

sql问题排查，当然一步步拆分，先去掉时间限制

SELECT COUNT (*)
  FROM (SELECT a.icpcode, a.discomname
          FROM ehealth_arch_basicinfo a
         WHERE    (a.ext_field1 IS NULL OR a.ext_field1 = '0')) b,
       node_center_region c
 WHERE c.cityid = 220800 AND b.icpcode = c.nodeid

发现运行成功了，那问题就在日期
发现这脚本很不正规，时间不知为什么转换成char再做比较,修改后如下

SELECT COUNT (*)
  FROM (SELECT a.icpcode, a.discomname
          FROM ehealth_arch_basicinfo a
         WHERE     a.BUILD_DATE >= TO_DATE ('1900-01-01', 'yyyy-mm-dd')
               AND a.BUILD_DATE <= TO_DATE ('2011-11-11', 'yyyy-mm-dd')
               AND (a.ext_field1 IS NULL OR a.ext_field1 = '0')) b,
       node_center_region c
 WHERE c.cityid = 220800 AND b.icpcode = c.nodeid
运行后成功 
总共返回738961行

但是开发的提出，说同样的sql程序在另外两套系统上运行不存在问题，而且也是同一个实例不同schema,排除服务器设置，很是怀疑这里个里面的数据才把上面sql的隐藏很久的bug给暴露出来
那先看一下最大时间与最少时间,在toad 中执行
select max(BUILD_DATE),min(BUILD_DATE) from ehealth_arch_basicinfo

最大时间列显示为空，最小居然显示9589-11-11 0:00:00.000000
在sqlplusw 里执行

SQL> col maxdate for a35
SQL> col mindate for a35
SQL> select max(BUILD_DATE) maxdate ,min(BUILD_DATE) mindate from jmjk_jilin_th_mhk.ehealth_arch_basicinfo;
MAXDATE                             MINDATE
----------------------------------- -----------------------------------
10-NOV-00 12.00.00.000000 AM        11-NOV-89 12.00.00.000000 AM              
SQL> alter session set NLS_TIMESTAMP_FORMAT='yyyy-mm-dd hh24:mi:ss';
会话已更改。
SQL> select BUILD_DATe,rowid r , TO_CHAR (BUILD_DATE, 'yyyy-mm-dd'),to_char(BUILD_DATe,'SYEAR')
    from  jmjk_jilin_th_mhk.ehealth_arch_basicinfo a where  a.BUILD_DATe    ;
BUILD_DATE                          R                  TO_CHAR(BU TO_CHAR(BUILD_DATE,'SYEAR')
----------------------------------- ------------------ ---------- -------------------------------------------
0001-11-10 00:00:00                 AAAeLZAAHAAAKIQAAH 0001-11-10  ONE
0004-11-10 00:00:00                 AAAeLZAAFAAAPyiAAR 0004-11-10  FOUR
0128-06-26 00:00:00                 AAAeLZAAFAAAP3xAAH 0128-06-26  ONE TWENTY-EIGHT
0128-06-26 00:00:00                 AAAeLZAAFAAAP6xAAL 0128-06-26  ONE TWENTY-EIGHT
0011-11-11 00:00:00                 AAAeLZAAGAAARSlAAG 0011-11-11  ELEVEN
9589-11-11 00:00:00                 AAAeLZAAGAAARW5AAD 9589-11-11 -NINETY-FIVE EIGHTY-NINE
9589-11-11 00:00:00                 AAAeLZAAGAAARXNAAR 9589-11-11 -NINETY-FIVE EIGHTY-NINE
0011-11-11 00:00:00                 AAAeLZAAGAAARbJAAC 0011-11-11  ELEVEN
0011-10-13 00:00:00                 AAAeLZAAGAAARfrAAO 0011-10-13  ELEVEN
9 rows selected.
diss connect
这里可以看到那个9589-11-11 还是公元前的，看到日期小于正常时间的没有问题，那我们再试试日期大于的
SQL> select  BUILD_DATE,dump(build_date) from  jmjk_jilin_th_mhk.ehealth_arch_basicinfo where  BUILD_DATe>sysdate
  2  ;
BUILD_DATE
------------------------------
DUMP(BUILD_DATE)
------------------------------------------------------------------------------------------------------------------------------------------------------
08-DEC-11 12.00.00.000000 AM
Typ=180 Len=7: 120,111,12,8,1,1,1
10-NOV-00 12.00.00.000000 AM
Typ=180 Len=7: 211,100,11,10,1,1,1

SQL> alter session set NLS_TIMESTAMP_FORMAT='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select  BUILD_DATE,dump(build_date) from  jmjk_jilin_th_mhk.ehealth_arch_basicinfo where  BUILD_DATe>sysdate
  2  ;
ERROR:
ORA-01877: string is too long for internal buffer
退出再进
SQL> l
  1  select  BUILD_DATE,dump(build_date) value,rowid r,BUILD_DATE-sysdate diffdays from  jmjk_jilin_th_mhk.ehealth_arch_basicinfo where  BUILD_DATe>sysdate
  2*
SQL> /
BUILD_DATE                          VALUE                                              R                    DIFFDAYS
----------------------------------- -------------------------------------------------- -------------------- --------------------
08-DEC-11 12.00.00.000000 AM        Typ=180 Len=7: 120,111,12,8,1,1,1                  AAAeLZAAGAAA2RsAAM   +000000026 10:20:42.
                                                                                                            000000
10-NOV-00 12.00.00.000000 AM        Typ=180 Len=7: 211,100,11,10,1,1,1                 AAAeLZAAFAAAP9rAAJ   +003319687 10:20:42.
我们计算一下，比今天晚3319687天                                                                             000000
SQL> select 3319687/365 from dual;
3319687/365
-----------
 9095.03288
就是当前2011年的9095年后，这就是引起上面问题的罪魁祸首(后来把开发的sql，加上rowid排除这一行显示正常)，那oracle允许yyyy存这么大的值么？
下面我们制造个错，来给出上面的答案
 
SQL> select  to_timestamp('9999-1-1','yyyy-mm-dd')+366 from dual;
select  to_timestamp('9999-1-1','yyyy-mm-dd')+366 from dual
                                             *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
SQL> select  to_timestamp('0001-1-1','yyyy-mm-dd')-366*5000 from dual;
select  to_timestamp('0001-1-1','yyyy-mm-dd')-366*5000 from dual
                                             *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

SQL> select  to_timestamp('0001-1-1','yyyy-mm-dd')-366*4000 from dual;
TO_TIMESTAMP('0001-
-------------------
4008-10-15 00:00:00

呵呵，如果时间最小是-4713 ，那我们上面的公元前9500多年是怎么进去的呢？