Continuing Inserts in Oracle when exception is raised

Question

Hi, 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.

Answer 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.

Answer 2

If you use sqlldr you can specify to continue loading data, and all the 'bad' data will be skipped and logged in a separate file.

Answer 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;

Answer 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;

Answer 5

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