Insert as select, capture errors

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:

Code:

---

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.

Does this help?

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;

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

10gR2 Insert

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.

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