Verificando corrupção de Lobs

/*
ORA-1578 ORA-26040 in a LOB segment - Script to solve the errors (Doc ID 293515.1)
Tabela para validar: "DBAMV"."PW_PERFIL_MODULO"
Coluna: LO_ICONE
VERIFICAR DATATYPE ORACLE TABLE
desc "DBAMV"."PW_PERFIL_MODULO";
*/

set serverout on
exec dbms_output.enable(100000);
declare
pag number;
len number;
c varchar2(10);
charpp number := 8132/2;
 
begin
for r in (select rowid rid, dbms_lob.getlength (LO_ICONE) len
from "DBAMV"."PW_PERFIL_MODULO") loop
if r.len is not null then
for page in 0..r.len/charpp loop
begin
select dbms_lob.substr (LO_ICONE, 1, 1+ (page * charpp))
into c
from "DBAMV"."PW_PERFIL_MODULO"
where rowid = r.rid;
 
exception
when others then
dbms_output.put_line ('Error on rowid ' ||R.rid||' page '||page);
dbms_output.put_line (sqlerrm);
end;
end loop;
end if;
end loop;
end;
/

drop table bad_rows;
create table bad_rows (row_id ROWID,oracle_error_code number);
 
set concat off
set serveroutput on
 
declare
  n number;
  error_code number;
  bad_rows number := 0;
  ora1578 EXCEPTION;
  ora600 EXCEPTION;
  PRAGMA EXCEPTION_INIT(ora1578, -1578);
  PRAGMA EXCEPTION_INIT(ora600, -600);
 
begin
   for cursor_lob in (select rowid rid, LO_ICONE from "DBAMV"."PW_PERFIL_MODULO") loop
   begin
     n:=dbms_lob.instr(cursor_lob.LO_ICONE,hextoraw('889911')) ;
   exception
    when ora1578 then
     bad_rows := bad_rows + 1;
     insert into bad_rows values(cursor_lob.rid,1578);
     commit;
    when ora600 then
     bad_rows := bad_rows + 1;
     insert into bad_rows values(cursor_lob.rid,600);
     commit;
    when others then
     error_code:=SQLCODE;
     bad_rows := bad_rows + 1;
     insert into bad_rows values(cursor_lob.rid,error_code);
     commit;   
   end;
  end loop;
  dbms_output.put_line('Total Rows identified with errors in LOB column: '||bad_rows);
end;
/

select * from bad_rows;
drop table bad_rows;