Hi
Below is the similar code from Docid 452341.1.
this code is for detecting the corruption in table with LOB column.
Request to all people with PL/SQL knowledge is there any chance to improve performance of this code block.
also if this code can provide update on screen after processing every 1000 rows.
I think this code block is processing it row by row so it is taking long to completed.
where LOBCOL is name of LOB column
SCHEMA.TABNAME is the table name with LOB column
drop table corrupted_lob_data;
create table corrupted_lob_data (corrupted_rowid rowid);
set concat off
declare
error_1555 exception;
pragma exception_init(error_1555,-1555);
num number;
begin
for cursor_lob in (select rowid r, LOBCOL from SCHEMA.TABNAME) loop
begin
num := dbms_lob.instr (cursor_lob.LOBCOL, hextoraw ('889911')) ;
exception
when error_1555 then
insert into corrupted_lob_data values (cursor_lob.r);
commit;
end;
end loop;
end;
/