Oracle - 制約エラーになっているレコードをEXCEPTIONS表を使って簡単に調べる

エラーになっているレコードをEXCEPTIONS表を使って簡単に調べることができる

1.EXCEPTIONS表の作成
--  utlexcpt.sqlを実行
create table exceptions(row_id rowid,
                  owner varchar2(30),
                  table_name varchar2(30),
            constraint varchar2(30))

2.制約を使用禁止にする
    ALTER TABLE EMP 
    DISABLE CONSTRAINT FK_DEPTNO
3.制約エラーのデータを作成する
    UPDATE
        EMP
    SET
        DEPTNO = '50'
    WHERE
        EMPNO = '7902'

4.制約をINTO EXCEPTIONオプション付きで使用可能にする
    ALTER TABLE EMP 
    ENABLE CONSTRAINT FK_DEPTNO
    EXCEPTIONS INTO EXCEPTIONS

5.エラー発生したデータを検索する
    SELECT 
            * 
    FROM 
        EMP E
    WHERE
        E.ROWID  IN (SELECT ROW_ID FROM EXCEPTIONS)

おまけ

--- 制約状態を調べるSQL
SELECT
-- DISABLE
    ' ALTER TABLE ' || TABLE_NAME ||  
    ' DISABLE CONSTRAINT ' || CONSTRAINT_NAME AS SQL_DISABLE,
-- ENABLE
    ' ALTER TABLE ' || TABLE_NAME ||  
    ' ENABLE CONSTRAINT ' || CONSTRAINT_NAME ||
    ' EXCEPTIONS INTO EXCEPTIONS ' AS SQL_ENABLE,
    CONS.*
FROM
    USER_CONSTRAINTS CONS
WHERE
    TABLE_NAME = 'EMP'

-- TEST DATA
CREATE TABLE DEPT
(
    DEPTNO                         NUMBER(2,0) NOT NULL,
    DNAME                          VARCHAR2(14),
    LOC                            VARCHAR2(13),
    CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO) USING INDEX
)
/
CREATE TABLE EMP
(
    EMPNO                          NUMBER(4,0) NOT NULL,
    ENAME                          VARCHAR2(10),
    JOB                            VARCHAR2(9),
    MGR                            NUMBER(4,0),
    HIREDATE                       DATE,
    SAL                            NUMBER(7,2),
    COMM                           NUMBER(7,2),
    DEPTNO                         NUMBER(2,0),
    CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES DEPT 
(DEPTNO),
    CONSTRAINT PK_EMP PRIMARY KEY (EMPNO) USING INDEX
)
/
INSERT INTO DEPT values(10,'ACCOUNTING','NEW YORK')
/
INSERT INTO DEPT values(20,'RESEARCH','DALLAS')
/
INSERT INTO DEPT values(30,'SALES','CHICAGO')
/
INSERT INTO DEPT values(40,'OPERATIONS','BOSTON')
/
INSERT INTO EMP values(7369,'SMITH','CLERK',7902,to_date
('1980/12/17', 'yyyy/MM/dd'),
800,null,20)
/
INSERT INTO EMP values(7499,'ALLEN','SALESMAN',7698,to_date
('1981/02/20', 'yyyy/MM/dd'),
1600,300,30)
/
INSERT INTO EMP values(7521,'WARD','SALESMAN',7698,to_date
('1981/02/22', 'yyyy/MM/dd'),
1250,500,30)
/
INSERT INTO EMP values(7566,'JONES','MANAGER',7839,to_date
('1981/04/02', 'yyyy/MM/dd'),
2975,null,20)
/
INSERT INTO EMP values(7654,'MARTIN','SALESMAN',7698,to_date
('1981/09/28', 'yyyy/MM/dd'),
1250,1400,30)
/
INSERT INTO EMP values(7698,'BLAKE','MANAGER',7839,to_date
('1981/05/01', 'yyyy/MM/dd'),
2850,null,30)
/
INSERT INTO EMP values(7782,'CLARK','MANAGER',7839,to_date
('1981/06/09', 'yyyy/MM/dd'),
2450,null,10)
/
INSERT INTO EMP values(7788,'SCOTT','ANALYST',7566,to_date
('1987/04/19', 'yyyy/MM/dd'),
3000,null,20)
/
INSERT INTO EMP values(7839,'KING','PRESIDENT',null,to_date
('1981/11/17', 'yyyy/MM/dd'),
5000,null,10)
/
INSERT INTO EMP values(7844,'TURNER','SALESMAN',7698,to_date
('1981/09/08', 'yyyy/MM/dd'),
1500,0,30)
/
INSERT INTO EMP values(7876,'ADAMS','CLERK',7788,to_date
('1987/05/23', 'yyyy/MM/dd'),
1100,null,20)
/
INSERT INTO EMP values(7900,'JAMES','CLERK',7698,to_date
('1981/12/03', 'yyyy/MM/dd'),
950,null,30)
/
INSERT INTO EMP values(7902,'FORD','ANALYST',7566,to_date
('1981/12/03', 'yyyy/MM/dd'),
3000,null,20)
/
INSERT INTO EMP values(7934,'MILLER','CLERK',7782,to_date
('1982/01/23', 'yyyy/MM/dd'),
1300,null,10)
/