Oracle01877,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_DATeselect 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多年是怎么进去的呢？

打赏

微信扫一扫，打赏作者吧～