Oracle JDBC: how do you know which row causes a unique key constraint?

Question

I have an oracle request that I am trying to execute using jdbc. The following is a request.

insert into bd_vehicles_temp select * from bd_vehicles_temp_1 

The bd_vehicles_temp_1 table contains about 7000-10000 rows. If the primary key in bd_vehicles_temp_1 is already present in bd_vehicles_temp, I get a SQLException: Unique key constraint.

the exception line is minus pstmt.executeUpdate() in my code. Is there a way to define a string in bd_vehicles_temp_1 that throws an exception.

Or do I need to loop through the lines in bd_vehicles_temp_1 and insert each row one at a time?

Thank Advance

Answer 1

If you know a column that can throw an 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.

Answer 2

The only way (I know) to figure out which line is causing the problem is to use the Oracle log log into function. Thus, insert will not throw an exception, and any line that violates any restriction will be written to the specified error table.

To do this, you first need to create a log table that contains the rejected rows:

 EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('BD_VEHICLES_TEMP'); 

This will create a table named ERR$_BD_VEHICLES_TEMP

Then run change your expression to the following:

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

The operation will continue even if the line does not check the constraint. After completing the instruction, you can check the contents of the ERR$_BD_VEHICLES_TEMP for rows that violate the restriction, including an error message and values.

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

For more information, see the manual:

• for DBMS_ERRLOG http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_errlog.htm#CEGBBABI
• for the LOG ERRORS INTO clause: http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9014.htm#BGBEIACB

Answer 3

Are you trying to insert into the table correctly? You should use pstmt.executeUpdate() instead of pstmt.execute() . If your table already has records, it is best to delete all rows and add again if this statement is executed more than once.