ora-02298

最近升级脚本老是发现主外健的一些冲突,造成数据不完整,于是这里了一个脚本,怎么查那些数据造成主外健冲突[@more@]

CREATE OR REPLACE PROCEDURE P_CON_ERR(AC_I_CONSTRAINT_NAME VARCHAR2, AC_I_OWNER VARCHAR2)
AUTHID CURRENT_USER AS
V_CURSORID INTEGER;
V_CONSNAME VARCHAR2(30);
V_OWNER VARCHAR2(30);
V_TABLE_NAME VARCHAR2(30);
V_RTABLE_NAME VARCHAR2(30);
V_COLUMN VARCHAR2(100);
V_STR VARCHAR2(600);
TYPE T_COL_VALUE IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
V_COL_VAL T_COL_VALUE;
V_RET NUMBER;
V_NUM NUMBER;
I BINARY_INTEGER;
V_WHERE VARCHAR2(600);
V_CAUSE VARCHAR2(200);
CURSOR C_COL_NAME(V_CON_NAME VARCHAR2) IS
SELECT *
FROM ALL_CONS_COLUMNS
WHERE CONSTRAINT_NAME = V_CON_NAME
AND OWNER = UPPER(AC_I_OWNER);
BEGIN
V_CONSNAME := TRIM(UPPER(AC_I_CONSTRAINT_NAME)); -- 约束名称
V_OWNER := TRIM(UPPER(AC_I_OWNER));
V_NUM := 0;
FOR T_COL_NAME IN C_COL_NAME(V_CONSNAME) LOOP
IF (V_NUM = 0) THEN
V_COLUMN := T_COL_NAME.COLUMN_NAME;
V_WHERE := 'A.' || T_COL_NAME.COLUMN_NAME || '= B.' ||
T_COL_NAME.COLUMN_NAME;
ELSE
V_COLUMN := V_COLUMN || ',' || T_COL_NAME.COLUMN_NAME;
V_WHERE := V_WHERE || ' AND ' || 'A.' || T_COL_NAME.COLUMN_NAME || '= B.' ||
T_COL_NAME.COLUMN_NAME;
END IF;
V_NUM := V_NUM + 1;
END LOOP;
FOR I IN 1 .. V_NUM LOOP
V_COL_VAL(I) := '';
END LOOP;
SELECT DISTINCT OWNER || '.' || TABLE_NAME
INTO V_TABLE_NAME
FROM ALL_CONS_COLUMNS
WHERE CONSTRAINT_NAME = V_CONSNAME
AND OWNER = V_OWNER;
-- 找到被引用的表名称
SELECT OWNER || '.' || TABLE_NAME
INTO V_RTABLE_NAME
FROM ALL_CONSTRAINTS
WHERE CONSTRAINT_NAME IN (SELECT R_CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE CONSTRAINT_NAME = V_CONSNAME
AND OWNER = V_OWNER)
AND OWNER = V_OWNER;
DBMS_OUTPUT.PUT_LINE('外键错误情况');
DBMS_OUTPUT.PUT_LINE('============');
DBMS_OUTPUT.PUT_LINE(V_COLUMN);
DBMS_OUTPUT.PUT_LINE(RPAD('-', LENGTH(V_COLUMN), '-'));
V_CURSORID := DBMS_SQL.OPEN_CURSOR;
V_STR := 'SELECT DISTINCT ' || V_COLUMN || ' FROM ' || V_TABLE_NAME ||
' A WHERE NOT EXISTS ( SELECT NULL FROM ' || V_RTABLE_NAME ||
' B WHERE ' || V_WHERE || ')';
DBMS_SQL.PARSE(V_CURSORID, V_STR, DBMS_SQL.V7);
FOR I IN 1 .. V_NUM LOOP
DBMS_SQL.DEFINE_COLUMN(V_CURSORID, I, V_COL_VAL(I), 30);
END LOOP;
V_RET := DBMS_SQL.EXECUTE(V_CURSORID);
WHILE DBMS_SQL.FETCH_ROWS(V_CURSORID) > 0 LOOP
V_CAUSE := '';
FOR I IN 1 .. V_NUM LOOP
DBMS_SQL.COLUMN_VALUE(V_CURSORID, I, V_COL_VAL(I));
IF (I = 1) THEN
V_CAUSE := V_COL_VAL(I);
ELSE
V_CAUSE := V_CAUSE || ', ' || V_COL_VAL(I);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V_CAUSE);
END LOOP;
DBMS_SQL.CLOSE_CURSOR(V_CURSORID);
END;
/

SQL> SET SERVEROUTPUT ON;
SQL> EXEC P_CON_ERR_02298('FK_BR_FEE_I_REFERENCE_BR_BUSI_','EA_PUB');

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/60217/viewspace-1009195/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/60217/viewspace-1009195/