I have a table named CUSTOMERS with the following columns :
CUSTOMER_ID (NUMBER), DAY(DATE), REGISTERED_TO(NUMBER)
There are more columns in the table but it is irrelevant to my question as only the above columns are defined together as the primary key
In our application we do a large amount of inserts into this table so we do not use MERGE but use the following statement :
INSERT INTO CUSTOMERS (CUSTOMER_ID , DAY, REGISTERED_TO)
SELECT ?, ?, ?
FROM DUAL WHERE NOT EXISTS
(SELECT NULL
FROM CUSTOMERS
WHERE CUSTOMER_ID = ?
AND DAY = ?
AND REGISTERED_TO = ?
)";
We use a PreparedStatement object using the batch feature to insert a large number of records collected through the flow of the application per customer.
Problem is that sometimes I get the following error :
ORA-00001: unique constraint (CUSTOMERS_PK) violated
Strange thing is that when I do NOT use batch inserts and insert each record one by one (by simply executing pstmt.execute()) there are no errors.
Is it something wrong with the insert statement ? the jdbc driver ? Am I not using the batch mechanism correctly ?
Here is a semi-pseudo-code of my insertion loop :
pstmt = conn.prepareStatement(statement);
pstmt.setQueryTimeout(90);
for each customer :
- pstmt.setObject(1, customer id);
- pstmt.setObject(2, current day);
- pstmt.setObject(3, registered to);
- pstmt.addBatch();
end for
pstmt.executeBatch();
It is all enclosed in a try/catch/finally block making sure the statement and connection are closed at the end of this process.
