Oracle 查询 ORA-01555 错误，处理方式。

--创建临时表
create table corrupt_lob (ID VARCHAR2(50));
--循环检索表中错误数据并将ID插入临时表
declare error_1578 exception;
error_1555 exception;
error_22922 exception;
pragma exception_init(error_1578,-1578);
pragma exception_init(error_1555,-1555);
pragma exception_init(error_22922,-22922);
n number;
begin
for cursor_lob in (select ID, 大字段名称 from 表名 )--括号内按时间过滤
loop
begin n:=dbms_lob.instr(cursor_lob.大字段名称,hextoraw('889911'));
exception when error_1578 then insert into corrupt_lob values (cursor_lob.ID); commit;
when error_1555 then insert into corrupt_lob values (cursor_lob.ID); commit;
when error_22922 then insert into corrupt_lob values (cursor_lob.ID);
commit;
end;
end loop;
end;


SELECT *,rowid FROM corrupt_lob;

---查询
SELECT t.id,t.yb_settlement_value,rowid FROM PM_PAY_ACCOUNT T WHERE
T.SETTLEMENT_TIME BETWEEN TO_DATE('2021-10-28 00:00:00','YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2021-10-30 00:00:00','YYYY-MM-DD HH24:MI:SS')
and t.id in (select id from corrupt_lob);

---更新为空
update PM_PAY_ACCOUNT T
set t.yb_settlement_value = ''
WHERE T.SETTLEMENT_TIME BETWEEN
TO_DATE('2021-10-28 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
TO_DATE('2021-10-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and t.id in (select id from corrupt_lob);
commit;

 

以上步骤，创建一个临时表，检索报错的表中大字段。若发生 01555错误，将其存储到临时表中，然后set null。这是临时解决方案。具体的还要看Oracle 到底出了什么问题。