ORA-01578 ORA-01110 bad block solution

Alter session set events '10231 trace name context off ';

Segment_name = sys_lob2017119493c00006 $
Segment_type = LOBSEGMENT

Column_name = WORD

Set serveroutput on


Declare
N number;
Error_code number;
Bad_rows number: = 0;
Ora1578 EXCEPTION;
PRAGMA EXCEPTION_INIT (ora1578,-1578 );
Begin
For cursor_lob in (select rowid rid, & lob_column from & table_owner. & table_with_lob) loop
Begin
N: = dbms_lob.instr (cursor_lob. & lob_column, hextoraw ('20140901 '));
Exception
When ora1578 then
Bad_rows: = bad_rows + 1;
Insert into bad_rows values (cursor_lob.rid, 1578 );
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;
/

Enter value for table_owner: HGHIS
Enter value for table_with_lob: EMR_CASE
Bad rowid can be queried
Select * from bad_rows;

Update & table_owner. & table_with_lob set & lob_column = empty_blob () where rowid in (select row_id from bad_rows );