-
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.
-
-
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;
____________________
Pete
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|