I'm running a Java application with Spring and I am getting an error on one of my insert statements. My error is:
nested exception is java.sql.SQLIntegrityConstraintViolationException: ORA-01400: cannot insert NULL into ("MY_SCHEMA"."VALIDATION_RESULT"."RESULT_SEQ")
For all the database guys, is there ever a scenario that Oracle would return null from a nextval call? What about if multiple threads are calling it simultaneously?
For any Spring developers, we're using
org.springframework.jdbc.support.incrementer.OracleSequenceMaxValueIncrementer
to handle the sequence. We use the nextLongValue method.
My gut here is telling me that Oracle isn't giving me a null nextval. From everything I've already searched for, that seems impossible. Can anyone confirm?
Confirmed. They do not return NULL. You get an error message.
Oracle sequences actually generate a block of "nextval" objects so threads can quickly access them. You can alter the sequences to create larger readahead numbers of values if it is performance obstacle. The only possibility is if Oracle is seriously broken. Get your DBA to look in the alert log. Errors like ORA-06nn errors are a DBA's nightmare and are the only thing I am aware of that actually breaks objects like sequences. In this case the DB and probably the DBA, too, are close to DOA. This kind of thing happens once in a career.
I would suspect your code first. Or someone tinking with the sequences - like doing something stupid with ALTER SEQUENCE. i.e., restarting the sequence from one and breaking table constraints. It is also easy to get things screwed up sequence-wise when you export only table from database DEV -> import to database TEST, because the other metadata needs to be brought over as well.