SelectInsertのデバッグがメンドイとき

SelectInsertするときにInsert先のテーブルにPK、UK、FK、NOTNULL制約などが張ってあって
Insertに失敗すると結構ストレスがたまります。
そもそもどのレコードで失敗したのかわからなかったりすると
NotNullだったらSelectのWhereにIS NOT NULLかいて探したり
UKだったら GROUP HAVING 書いて探したり
かなりめんどくさいので10gからはdbms_errlogパッケージ使ってレコード特定してます。
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/appdev.102/B19245-02/d_errlog.htm

つかいかた例：

Insert先にUKが張ってあり、Select元で重複が発生している状態

select元テーブル

CREATE TABLE SELECT_TABLE
(
    NAME                           VARCHAR2(10)
);

select_tableデータ

NAME
a
b
c
d
e
f
a
g
h

insert先テーブル

CREATE TABLE INSERT_TABLE
(
    NAME                           VARCHAR2(10),
    CONSTRAINT UK_INSERT_TABLE UNIQUE (NAME) USING INDEX
)

上記の状態でSelectInsert

INSERT INTO INSERT_TABLE(NAME) SELECT NAME FROM SELECT_TABLE;

すると、「ORA-00001: unique constraint violated.」と一意制約違反で失敗しますが
どのレコードで失敗したかは自分で下記のようなSQLでSelectしてこないとわかりません。

SELECT
	NAME
	,COUNT(*)
FROM
	SELECT_TABLE
GROUP BY
	NAME
HAVING
	COUNT(*) > 1;

がんばってデバッグするのも大変なので
まずdbms_errlogパッケージのcreate_error_logプロシージャを呼び出します。

exec dbms_errlog.create_error_log('INSERT_TABLE');

このプロシージャを呼ぶとエラー行保持テーブルが作成されます。

CREATE TABLE NIWANO.ERR$_INSERT_TABLE
(
    ORA_ERR_NUMBER$                NUMBER,
    ORA_ERR_MESG$                  VARCHAR2(2000),
    ORA_ERR_ROWID$                 UROWID,
    ORA_ERR_OPTYP$                 VARCHAR2(2),
    ORA_ERR_TAG$                   VARCHAR2(2000),
    NAME                           VARCHAR2(4000)
)
/

次に先ほどのSelectInsertの最後にLOG ERRORS INTOをつけます。

INSERT INTO INSERT_TABLE(NAME) SELECT NAME FROM SELECT_TABLE
LOG ERRORS INTO ERR$_INSERT_TABLE('SelectInsertで失敗した') REJECT LIMIT 10;

すると、SelectInsertは失敗しませんが、ERR$_INSERT_TABLEに下記のようにエラーレコードが入ります。

ORA_ERR_NUMBER$	ORA_ERR_MESG$	ORA_ERR_ROWID$	ORA_ERR_OPTYP$	ORA_ERR_TAG$	NAME
1	"ORA-00001: 一意制約(UK_INSERT_TABLE)に反しています"		I	SelectInsertで失敗した	a

これをみれば NAME=a のレコードでInsertが失敗したことがわかり、さらにInsert可能行はすべてInsertされます。

ちなみにLOG ERRORSの内容は
LOG ERRORS INTO エラーログ格納テーブル名('エラータグ名') REJECT LIMIT エラー許容件数;
って感じです。

今回のようにSQLのデバッグにも活用できますし、SQLでエラー行はスキップしてInsertしたい場合などにも
使えると思いますのでOracle10gの環境が近くにある方は使ってみるとストレスから解放されるかもしれません。