Insert as select, capture errors

[xcor635]

I have a bunch of tables to migrate to a new schema. Can someone get me started with a pl/sql script to capture errors and write thes to a log, etc. Here is the simple insert as select:


INSERT INTO TEST.GEORGE_AT_TYPE(
DISTRICT_NUMBER,
VALUE,
NAME,
DESCRIPTION,
STATE_CODE,
DWELLING_CATEGORY_CD,
IS_ACTIVE_IND,
UPDATE_TS,
UPDATED_BY,
ORDER_DEFAULT,
GENERATION)
SELECT DISTINCT
0098,
trim(PROD.LOADER_EV200.DWL_TY),
trim(PROD.LOADER_EV200.DWL_TY))),
trim(PROD.LOADER_EV200.DWL_DESCR),
trim(PROD.LOADER_EV200.DWL_ST_CD),
trim(PROD.LOADER_EV200.DWL_CAT),
1,
to_date(decode(trim(PROD.LOADER_EV200.MNT_DT),0,20060101), 'yyyymmdd'),
-1,
1,
1
FROM PROD.LOADER_EV200;


The problem is that if 1 row has a bad value or a duplicate key in the new schema, nothing is moved over. I want to move over everything possible, and write a log file of the errors and rows that could not move over.

Any suggestions are appreciated.

[WilliamR]

Does this (http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14220/sqlplsql.htm#sthref3393) help?

[rigatoni]

I WilliamR's suggestion is to use PL/SQL and loop through a cursor catching any errors that occur inside the loop.

Something like:

begin

for r1 in (SELECT DISTINCT
0098 .....
FROM PROD.LOADER_EV200)
loop

begin

insert into ....
values (...);

exception
when ...
then

-- log error or something
end;

end loop;


end;

[ebrian]

You don't mention what version you are on, but if you are on 10gR2, you may want to consider the following:

10gR2 Insert (http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#BGBEIACB)

The error_logging_clause lets you capture DML errors and the log column values of the affected rows and save them in an error logging table.

[WilliamR]

Yes that was exactly what I was suggesting. I would try to avoid loops if possible.