CODEWITHSUNDEEP
javasqloracle-databasejdbc
Abhishek Singh
Abhishek Singh

Reputation: 9765

Oracle JDBC: How to know which row throws unique key constraint?

I have an oracle query which i am trying to execute using jdbc. Following is the query.

insert into bd_vehicles_temp select * from bd_vehicles_temp_1

table bd_vehicles_temp_1 contains about 7000-10000 rows. If a primary key in bd_vehicles_temp_1 is already present in bd_vehicles_temp i get an SQLException : Unique key constraint.

the line of exception is offcourse pstmt.executeUpdate() in my code. Is there a way to pinpoint the row in bd_vehicles_temp_1 which causes exception.

Or do i have to do loop through rows in bd_vehicles_temp_1 and insert each row one by one ?

Thanks in Advance !

Upvotes: 2

Views: 2021

Answers (3)

user330315
user330315

Reputation:

The only way (I know of) to find out which row causes the problem is to use Oracle's "log errors into" feature. That way the insert won't throw an exception and any row violating any constraint will be written into the error table specified.

To do that you first need to create a log table that holds the rejected rows:

EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('BD_VEHICLES_TEMP');

That will create a table named ERR$_BD_VEHICLES_TEMP

Then run change your statement to this:

insert into bd_vehicles_temp 
select * 
from bd_vehicles_temp_1
LOG ERRORS REJECT LIMIT UNLIMITED;

The statement will continue even if a row fails to validate the constraints. After the statement is finished you can check the contents of the table ERR$_BD_VEHICLES_TEMP for the rows that violated a constraint including the error message and the values.

(Edit): If you want to stop at the first error (and see that in the log table), then leave out the REJECT LIMIT UNLIMITED clause.

More details are in the manual:

Upvotes: 7

Uwe Plonus
Uwe Plonus

Reputation: 9954

If you know the column that can cause the exception you can use (Oracle specific)

SELECT col FROM bd_vehicles_temp
INTERSECT
SELECT col FROM bd_vehicles_temp_1;

to identify all rows that are in both tables.

Upvotes: 2

Aashray
Aashray

Reputation: 2763

You are trying to insert into a table right? You should be using pstmt.executeUpdate() instead of pstmt.execute(). If there are already existing records in your table, then its better to delete all rows and add again if this statement is executed more than once.

Upvotes: 2

Related Questions