首页 » ORACLE » ORA-01877: string is too long for internal buffer 排查

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 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多年是怎么进去的呢?

打赏

目前这篇文章有1条评论(Rss)评论关闭。

  1. Shanice Querta | #1
    2011-12-21 at 06:07

    I am just commenting to let you understand of the exceptional discovery my child experienced going through yuor web blog. She realized some details, which included how it is like to have an incredible coaching mood to have certain people smoothly learn about several problematic subject matter. You really exceeded her expectations. Many thanks for imparting the warm and helpful, safe, revealing and even fun tips about that topic to Jane.

上一篇:

下一篇: