How will you find the erroneous records at Oracle?

To will you find the erroneous records at Oracle?

We already had seen one scenario like this wherein we separate out erroneous records and allow good records to get inserted / updated at target table. Below is the link for it.
http://gogates.blogspot.sg/2012/02/saving-error-while-dml-statement.html

But, what if we do not have above implementation is in place and then the session fails because of in-correct record.

For an example..

We have below insert statement..


insert into departments
(select * from departments_dummy)

When you execute the statement, you are getting error as "invalid number". Now the question is, if there are thousands of records how you are going to find a particular records which is causing the problem?

Below is the solution




select *
from departments_dummy
where (translate (department_id, '1234567890', '~') not like '%~%'
or translate (manager_id, '1234567890', '~') not like '%~%'
or translate (location_id, '1234567890', '~') not like '%~%')




Here, insert statement was failing because once of the record has manger_id value as 280:

Any id should not have character apart from number, so we are verifying all id columns in a table. So, all numeric values will get translated into ~. Values which do not get translated into ~ are erroneous.