Skip to Main Content
  • Questions

  • Catching a row causing DUP_VAL_ON_INDEX

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Andrew Franklin.

Asked: March 21, 2016 - 7:59 am UTC

Last updated: March 21, 2016 - 10:09 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi,
I want to update multiple rows in a table but the update statement throws unique constraint error on index.So I used PL/SQL block to update the rows to check DUP_VAL_ON_INDEX.but is there any ways to record the rows which causing the error like inserting the rows that throws error into new table.

and Chris said...

You can use the log errors clause to store the invalid rows. To use this you need to:

- create an error logging table
- Add the log errors into clause to your update

Details of the failing row(s) will then appear in the error table.

For further details, check out Connor's video:

https://www.youtube.com/watch?v=8WV15BzQIto

Or Tim Hall's article:

https://oracle-base.com/articles/10g/dml-error-logging-10gr2

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.