Tuesday, October 15, 2019

Log Errors when insert select all

When you use insert into ., select ... statement, if few of your data will have issues, your statement will fail due to violation of some existing constraints. To avoid this happening, which means you want to make sure all the 'good' data are still being inserted while keep your eye on those 'bad' boys, you can use log errors feature:

1. set up the base error log table:

BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 'XXXXX');
END;

XXX is your table name that you are going to insert to
This will create a table err$_XXX which will store all error data/log

2. Add log errors at the end of your insert statement

insert into .... 
LOG ERRORS INTO err$_XXX ('INSERT') REJECT LIMIT UNLIMITED;

No comments:

Post a Comment