When Batched Updates fail, ruins following Batched Updates

Hi,

I am using 8.1.5 with the classes12.zip JDBC drivers. I have a set of 3 OraclePreparedStatements to insert bulk data from a denormalised data file into 3 tables related as parent to child to it's child. So sequence is important. Sometimes, because it is a denormalised source, the parent table will have duplicated key values. In this case the prepStmt.executeUpdate() for that parent table fails (it seems to detect these before getting to the limit of the batch size) so I rollback all 3 OraclePreparedStatements. Then I call clearParameters() for all 3 OraclePreparedStatements.

Now I change the batch size to 1 and use a set of identical statements (setExecuteBatch(1) for each) and try again. This time if one fails I call an different OraclePreparedStatement with a batch size of 1 that does an UPDATE. Note I am also using the Oracle style Batch updating.

All this works well. I process a batch of records the same size as my batch setting (setExecuteBatch(20)) each time.

The second batch of records to come through then has very strange problems. Note that I had called clearParameters for my 3 OraclePreparedStatements used for the initial insert attempt. At random during the batch of 20 records it fails on an executeUpdate() for one of the statements. The error is gets "DBItem/DBType is null" with a vendor code of 17440. I had a look at the OraclePreparedStatement concerned and it has not filled the DBItems array starting at the position 19 as it does when it operates correctly the first time. It seems to start at a random position in this array, only after that first operation that failed. As clearParameters is the only call Oracle give me to clear out the batch items (and it does do this) I do not know what more I can do to 'initialise' the OraclePreparedStatement beyond destroying it and rebuilding it again which rather destroys performance.

I realise this is a fairly advanced question but any ideas?

thanks

David Received on Fri Mar 22 2002 - 09:10:26 CST