Ongoing insertions in Oracle when an exception occurs

Question

I am working on transferring data from the old system to our new application (works in Oracle Database, 10gR2). As part of the migration, I am working on a script that inserts data into tables that are used by the application.

The number of rows of imported data in thousands, and the original data is not clean (unexpected zeros in NOT NULL columns, etc.). Therefore, inserting data through scripts, whenever such an exception occurs, the script ends abruptly, and the entire transaction is rolled back.

Is there a way in which I can continue to insert data for which the rows are clean? Using NVL()or is COALESCE()not a parameter, as I would like to log lines causing errors so that the data can be corrected for the next pass.

EDIT: my current procedure has an exception handler, I register the first line that causes an error. It would be possible for the inserts to continue without interruption, because right now in the first exception thrown, the procedure ends.

Answer 1

PLSQL, (COMMIT ) , .

Answer 2

, ​​PL/SQL, , . DML,

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

Answer 3

:

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

sqlldr, , , "" .