Search code examples
sqloracle-databaseerror-handlinginsertoracle9i

Insert select continue with error

I have some sql 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 ?

Solution

  • 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