一、创建临时表
创建临时表corrupt_lobs_wfcaserun用于存放检查结果
create table corrupt_lobs_wfcaserun (corrupt_rowid rowid);
二、执行检查
执行检查,并将检查出blob字段存在问题的数据的rowid放到临时表中
其中RISENET_NEW是数据库用户名,
OFFICE_WORKFLOWDOCUMENT是需要检查的表名,
DOCUMENTCONTENT是blob字段名,
该表有多个blob字段需逐一检查
DECLARE 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) ; num NUMBER ;
BEGIN
FOR cursor_lob IN (
SELECT
ROWID r,
DOCUMENTCONTENT
FROM
RISENET_NEW.OFFICE_WORKFLOWDOCUMENT
) loop
BEGIN
num := dbms_lob. INSTR (
cursor_lob.DOCUMENTCONTENT,
HEXTORAW ('889911')
) ; EXCEPTION
WHEN error_1578 THEN
INSERT INTO corrupt_lobs_wfcaserun
VALUES
(cursor_lob.r) ; COMMIT ;
WHEN error_1555 THEN
INSERT INTO corrupt_lobs_wfcaserun
VALUES
(cursor_lob.r) ; COMMIT ;
WHEN error_22922 THEN
INSERT INTO corrupt_lobs_wfcaserun
VALUES
(cursor_lob.r) ; COMMIT ;
END ;
END loop ;
END ;
三、删除原表有问题的数据
DELETE
FROM
RISENET_NEW.OFFICE_WORKFLOWDOCUMENT
WHERE
ROWID IN (
SELECT
corrupt_rowid
FROM
corrupt_lobs_wfcaserun
);