ORA-22922: nonexistent LOB value error while dealing with CLOBS
My java application uses batching to insert multiple rows (100) into the database at once using a combination of setBatchSize, executeUpdate and sendBatch methods of JDBC Oracle Driver. This works perfectly fine for most of our cases, and gives us a performance boost as it reduces the number of trips to the database.
However, when any of the inserts have CLOBs, I see that the executeUpdate fails after it processes 4 big CLOB objects. In other words, when dealing with clobs, the first 4 statements work fine, and the 5th statement fails on executeUpdate throwing a ORA-22922 error.
I could work around this problem by setting the batch size to 1 instead of 100, so that all the statements are committed instantly and not batched. This strategy works fine for CLOBs, but takes away the performance factor for the other 99% of the cases we deal with. Is there a way to handle this problem ? Can we increase some kind of buffer in Oracle, so that it can handle more clobs in memory before it finally commits to the database.