CODEWITHSUNDEEP
sqloracle-databaseexceptionplsql
Sathyajith Bhat
Sathyajith Bhat

Reputation: 21851

Continuing Inserts in Oracle when exception is raised

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.

Upvotes: 4

Views: 33118

Answers (5)

diederikh
diederikh

Reputation: 25271

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;

Upvotes: 4

Gary Myers
Gary Myers

Reputation: 35401

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

Upvotes: 11

Sai Ganesh
Sai Ganesh

Reputation: 300

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;

Upvotes: 1

Mark Roddy
Mark Roddy

Reputation: 27936

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.

Upvotes: 0

Shea
Shea

Reputation: 11243

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.

Upvotes: 5

Related Questions