Continuing Inserts in Oracle when exception is raised

Question

I'm working on migration of data from a legacy system into our new app(running on Oracle Database, 10gR2). As part of the migration, I'm working on a script which inserts the data into tables that are used by the app.

The number of rows of data that are imported runs into thousands, and the source data is not clean (unexpected nulls in NOT NULL columns, etc). So while inserting data through the scripts, whenever such an exception occurs, the script ends abruptly, and the whole transaction is rolled back.

Is there a way, by which I can continue inserts of data for which the rows are clean? Using NVL() or COALESCE() is not an option, as I'd like to log the rows causing the errors so that the data can be corrected for the next pass.

EDIT: My current procedure has an exception handler, I am logging the first row which causes the error. Would it be possible for inserts to continue without termination, because right now on the first handled exception, the procedure terminates execution.

Solution 1

Using PLSQL you can perform each insert in its own transaction (COMMIT after each) and log or ignore errors with an exception handler that keeps going.

Solution 2

If the data volumes were higher, row-by-row processing in PL/SQL would probably be too slow. In those circumstances, you can use DML error logging, described here

CREATE TABLE raises (emp_id NUMBER, sal NUMBER 
   CONSTRAINT check_sal CHECK(sal > 8000));

EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('raises', 'errlog');

INSERT INTO raises
   SELECT employee_id, salary*1.1 FROM employees
   WHERE commission_pct > .2
   LOG ERRORS INTO errlog ('my_bad') REJECT LIMIT 10;

SELECT ORA_ERR_MESG$, ORA_ERR_TAG$, emp_id, sal FROM errlog;

ORA_ERR_MESG$               ORA_ERR_TAG$         EMP_ID SAL
--------------------------- -------------------- ------ -------
ORA-02290: check constraint my_bad               161    7700
 (HR.SYS_C004266) violated

Solution 3

Try this:

for r_row in c_legacy_data loop
  begin
    insert into some_table(a, b, c, ...)
    values (r_row.a, r_row.b, r_row.c, ...);
  exception
    when others then 
      null;  /* or some extra logging */
  end;
end loop;

Solution 4

DECLARE
   cursor;
BEGIN
    loop for each row  in cursor
      BEGIN  -- subBlock begins 
         SAVEPOINT startTransaction;  -- mark a savepoint
 -- do whatever you have do here
         COMMIT;         
      EXCEPTION
         ROLLBACK TO startTransaction;  -- undo changes
      END;  -- subBlock ends
   end loop;
END;

View more solutions

Comments

[Sathyajith Bhat]

The exception handler traps the first error, and then exits from the procedure. How can I keep it from exiting ?

[DCookie]

@Arnshea is right - put the insert inside it's own block: BEGIN insert ... EXCEPTION handle_exception END;

[Sathyajith Bhat]

Thanks, implemented this, and will help a lot going forward.

[Sathyajith Bhat]

Just read the link you had provided, DML validation has a restriction of not being able to capture and hence fail, on a unique constraint violation, and I have encountered such errors. Anyways, I was unaware of this, and will definitely be of use in other areas on conversion that I'm working on. Thanks.