为了能够分批次执行,所以将所有大字段的表名和列名写入到一个表中,并打标识
create table temp_clob(
table_name varchar2(32),
column_name varchar2(40),
sfcl varchar2(2)
)
insert into temp_clob
select table_name,column_name,'否' from colswhere data_type='CLOB' order by table_name,column_name
--建立记录表,记录找到的损毁字段所在的表,列,行
create table corrupt_lobs (
corrupt_rowid rowid,
table_name varchar2(32),
column_name varchar2(32));
遍历所有字段的值,找出损毁的clob字段
-- Created on 2018-07-04 by ADMINISTRATOR
declare
v_cur_CKD sys_refcursor;
sqltext varchar2(200);
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;
row_id rowid;
clobtext clob;
begin
-- Test statements here
for cursor_lobin (select table_name,column_namefrom temp_clobwhere sfcl='否' and rownum<100order by table_name,column_name)
loop
-- dbms_output.put_line(cursor_lob.table_name||','||cursor_lob.column_name);
sqltext :='select rowid r, '||cursor_lob.column_name||' from '||cursor_lob.table_name;
open v_cur_CKDfor sqltext;
fetch v_cur_CKDinto row_id,clobtext;
while v_cur_CKD%found
loop
begin
n:=dbms_lob.instr(clobtext,hextoraw('889911'));
exception
when error_1578then insert into corrupt_lobsvalues (row_id,cursor_lob.column_name,cursor_lob.table_name);commit;
when error_1555then insert into corrupt_lobsvalues (row_id,cursor_lob.column_name,cursor_lob.table_name);commit;
when error_22922then insert into corrupt_lobsvalues (row_id,cursor_lob.column_name,cursor_lob.table_name);commit;
end;
fetch v_cur_CKDinto row_id,clobtext;
-- dbms_output.put_line(row_id);
end loop;
close v_cur_CKD;
update temp_clobset sfcl='是' where table_name=cursor_lob.table_nameand column_name=cursor_lob.column_name;
end loop;
end;