为了能够分批次执行,所以将所有大字段的表名和列名写入到一个表中,并打标识
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 cols where 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_lob in ( select table_name,column_name from temp_clob where sfcl= '否' and rownum<100 order 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_CKD for sqltext;
fetch v_cur_CKD into row_id,clobtext;
while v_cur_CKD%found
loop
begin
n:=dbms_lob.instr(clobtext,hextoraw( '889911' ));
exception
when error_1578 then insert into corrupt_lobs values (row_id,cursor_lob.column_name,cursor_lob.table_name); commit ;
when error_1555 then insert into corrupt_lobs values (row_id,cursor_lob.column_name,cursor_lob.table_name); commit ;
when error_22922 then insert into corrupt_lobs values (row_id,cursor_lob.column_name,cursor_lob.table_name); commit ;
end ;
fetch v_cur_CKD into row_id,clobtext;
-- dbms_output.put_line(row_id);
end loop;
close v_cur_CKD;
update temp_clob set sfcl= '是' where table_name=cursor_lob.table_name and column_name=cursor_lob.column_name;
end loop;
end ;
|