今天开发说有个程序,页面查询报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多年是怎么进去的呢?
打赏
微信扫一扫,打赏作者吧~