Insert select continue with error

I have some s开发者_StackOverflow社区ql scripts which use

INSERT INTO secondtable 
  (field, field1)
SELECT field, field2 
  FROM table;

I use oracle 9.x and sqldevelopper.

When I launch them, on some insert select I get foreign key error(s). And so the they didn't insert the set who work.

Is it possible to say to oracle to continue same when the error appear and if possible to display or log the error ?

You can't continue a failed set-based insert in ORacle 9. You either have to make the statement failsafe, or accept that it will be rolled back.

Options:

1. If such errors are rare, try the set-based effort and add an exception handler that does a row-based solution if an error occurs, and displays the errors (or stores them somewhere for further processing) when errors occur. Yes, this duplicates the effort but most times will be doing the most efficient operation and when errors occur it will handle them as you decide.

2. If this is a common occurance, suck it up and switch to a row-based solution as above. Optimize as best you can and hope that someday you can take advantage of #3)

3. Upgrade to Oracle 10 and take advantage of DML error logging

Not in Oracle 9i, no. In 10.2, DML error logging was introduced which is exactly what you appear to want.

In earlier versions, the most common approach would be to do PL/SQL, i.e.

BEGIN
  FOR src IN (SELECT field1, field2
                FROM source_table)
  LOOP
    BEGIN 
      INSERT INTO destination_table( field1, field2 )
        VALUES( src.field1, src.field2 );
    EXCEPTION
      WHEN dup_val_on_index
      THEN
        <<log the foreign key error>>
    END;
  END LOOP;
END;

You could also use SQL to separate rows that violate the foreign key, i.e.

INSERT INTO error_table( field1, field2 )
  SELECT field1, field2
    FROM source_table
   WHERE NOT EXISTS( 
     SELECT 1
       FROM parent_table 
      WHERE parent_table.field1 = source_table.field1 );

INSERT INTO destination_table( field1, field2 )
  SELECT field1, field2
    FROM source_table
   WHERE EXISTS( 
     SELECT 1
       FROM parent_table 
      WHERE parent_table.field1 = source_table.field1 );