Insert as select, capture errors
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Insert as select, capture errors

  1. #1
    Join Date
    Apr 2006
    Posts
    1

    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.

  2. #2
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    Does this help?

  3. #3
    Join Date
    Nov 2003
    Location
    Ohio
    Posts
    51
    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

  4. #4
    Join Date
    Apr 2006
    Posts
    377
    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.

  5. #5
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    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
  •  


Click Here to Expand Forum to Full Width