解决oracle数据库的表中存在异常日期值在查询时抛出ora-01841错误的办法

生产环境的服务器上，在查询时抛出SQLDataException，错误代码为ora-01841

ORA-01841: (完整) 年份值必须介于 -4713 和 +9999 之间, 且不为 0

经排查是在rs.next()语句中报错，导致后面的数据不能正常读取和显示，经多方判断，只能判断是表中的某个日期列被保存了一个异常日期值，在PLSQL中使用日期列排序时也会抛出此异常，表中的数据量也比较多，有十几万，无法精确的定位是哪一行数据的日期列有问题，网上也查不到相应的解决方案，后来无意中使用to_char函数查询年份

select distinct to_char(dt_column, 'yyyy') from tbl 

居然可以查询出结果，结果集中显示有数据的年份为0000，故基本上可以判断是存在年份为0的数据引起的异常，下面尝试使用以下SQL语句对数据进行校正

update em_t_equ$detail t set t.DT_REPLACE_DATE=null where to_char(t.DT_REPLACE_DATE,'yyyy')='0000' 
or to_number(to_char(t.DT_REPLACE_DATE,'yyyy'))>9999 or to_number(to_char(t.DT_REPLACE_DATE,'yyyy'))<-1000;

执行后居然成功了，没有报错，为了保证将所有数据列的错误值都进行校正，可以使用SQL语句查出此表的所有日期类型的列名，然后结合excel，自动生成多个SQL语句

select * from user_tab_columns t where t.TABLE_NAME='EM_T_EQU$DETAIL' and data_type='DATE'

结果集：

EM_T_EQU$DETAIL    DT_REPLACE_DATE    DATE
EM_T_EQU$DETAIL    DT_NEXT$REPLACE$DATE    DATE
EM_T_EQU$DETAIL    DT_CS    DATE
EM_T_EQU$DETAIL    DT_USE_LIFE    DATE
EM_T_EQU$DETAIL    DT_LM_DATE    DATE
EM_T_EQU$DETAIL    DT_MIDDLE_DATE    DATE
EM_T_EQU$DETAIL    DT_BIG_DATE    DATE
EM_T_EQU$DETAIL    DT_NEXT$CHECK$DATE    DATE
EM_T_EQU$DETAIL    DT_NEXT$MIDDLE$DATE    DATE
EM_T_EQU$DETAIL    DT_NEXT$BIG$DATE    DATE
EM_T_EQU$DETAIL    DT_FACTORY_DATE    DATE
EM_T_EQU$DETAIL    DT_CHECK_DATE    DATE
EM_T_EQU$DETAIL    DT_EQU$USE$DATE    DATE
EM_T_EQU$DETAIL    DT_DATE    DATE

在PLSQL中可以单独复制第2列，粘贴到Excel中，在后面的单元格上填写公式：

="update em_t_equ$detail t set t."&I2&"=null where to_char(t."&I2&",'yyyy')='0000' or to_number(to_char(t."&I2&",'yyyy'))>9999 or to_number(to_char(t."&I2&",'yyyy'))<-1000;"

鼠标下拉复制，生成多个SQL语句，复制后在PLSQL中粘贴执行即可

update em_t_equ$detail t set t.DT_REPLACE_DATE=null where to_char(t.DT_REPLACE_DATE,'yyyy')='0000' or to_number(to_char(t.DT_REPLACE_DATE,'yyyy'))>9999 or to_number(to_char(t.DT_REPLACE_DATE,'yyyy'))<-1000;
update em_t_equ$detail t set t.DT_NEXT$REPLACE$DATE=null where to_char(t.DT_NEXT$REPLACE$DATE,'yyyy')='0000' or to_number(to_char(t.DT_NEXT$REPLACE$DATE,'yyyy'))>9999 or to_number(to_char(t.DT_NEXT$REPLACE$DATE,'yyyy'))<-1000;
update em_t_equ$detail t set t.DT_CS=null where to_char(t.DT_CS,'yyyy')='0000' or to_number(to_char(t.DT_CS,'yyyy'))>9999 or to_number(to_char(t.DT_CS,'yyyy'))<-1000;
update em_t_equ$detail t set t.DT_USE_LIFE=null where to_char(t.DT_USE_LIFE,'yyyy')='0000' or to_number(to_char(t.DT_USE_LIFE,'yyyy'))>9999 or to_number(to_char(t.DT_USE_LIFE,'yyyy'))<-1000;
update em_t_equ$detail t set t.DT_LM_DATE=null where to_char(t.DT_LM_DATE,'yyyy')='0000' or to_number(to_char(t.DT_LM_DATE,'yyyy'))>9999 or to_number(to_char(t.DT_LM_DATE,'yyyy'))<-1000;
update em_t_equ$detail t set t.DT_MIDDLE_DATE=null where to_char(t.DT_MIDDLE_DATE,'yyyy')='0000' or to_number(to_char(t.DT_MIDDLE_DATE,'yyyy'))>9999 or to_number(to_char(t.DT_MIDDLE_DATE,'yyyy'))<-1000;
update em_t_equ$detail t set t.DT_BIG_DATE=null where to_char(t.DT_BIG_DATE,'yyyy')='0000' or to_number(to_char(t.DT_BIG_DATE,'yyyy'))>9999 or to_number(to_char(t.DT_BIG_DATE,'yyyy'))<-1000;
update em_t_equ$detail t set t.DT_NEXT$CHECK$DATE=null where to_char(t.DT_NEXT$CHECK$DATE,'yyyy')='0000' or to_number(to_char(t.DT_NEXT$CHECK$DATE,'yyyy'))>9999 or to_number(to_char(t.DT_NEXT$CHECK$DATE,'yyyy'))<-1000;
update em_t_equ$detail t set t.DT_NEXT$MIDDLE$DATE=null where to_char(t.DT_NEXT$MIDDLE$DATE,'yyyy')='0000' or to_number(to_char(t.DT_NEXT$MIDDLE$DATE,'yyyy'))>9999 or to_number(to_char(t.DT_NEXT$MIDDLE$DATE,'yyyy'))<-1000;
update em_t_equ$detail t set t.DT_NEXT$BIG$DATE=null where to_char(t.DT_NEXT$BIG$DATE,'yyyy')='0000' or to_number(to_char(t.DT_NEXT$BIG$DATE,'yyyy'))>9999 or to_number(to_char(t.DT_NEXT$BIG$DATE,'yyyy'))<-1000;
update em_t_equ$detail t set t.DT_FACTORY_DATE=null where to_char(t.DT_FACTORY_DATE,'yyyy')='0000' or to_number(to_char(t.DT_FACTORY_DATE,'yyyy'))>9999 or to_number(to_char(t.DT_FACTORY_DATE,'yyyy'))<-1000;
update em_t_equ$detail t set t.DT_CHECK_DATE=null where to_char(t.DT_CHECK_DATE,'yyyy')='0000' or to_number(to_char(t.DT_CHECK_DATE,'yyyy'))>9999 or to_number(to_char(t.DT_CHECK_DATE,'yyyy'))<-1000;
update em_t_equ$detail t set t.DT_EQU$USE$DATE=null where to_char(t.DT_EQU$USE$DATE,'yyyy')='0000' or to_number(to_char(t.DT_EQU$USE$DATE,'yyyy'))>9999 or to_number(to_char(t.DT_EQU$USE$DATE,'yyyy'))<-1000;
update em_t_equ$detail t set t.DT_DATE=null where to_char(t.DT_DATE,'yyyy')='0000' or to_number(to_char(t.DT_DATE,'yyyy'))>9999 or to_number(to_char(t.DT_DATE,'yyyy'))<-1000;