Now every time i run the above i get the following error
I tried to debug the program and found that the if statement checking if rs.next() is true is never returning true so that bind variable is never set. The exception occurs at addChildStmt.executeUpdate(insertChild);
I dont want to issue a commit after i have inserted the parent record. My understanding is that i dont have to commit if i am on the same session. Will the second insert statement shown above not be in the same session as the first one? Why exactly is the rs.next() not returning any value even though i had just inserted the record?
You're right that you need to use addChildStmt.executeUpdate(), that was the cause of the exception.
However, your code can be made much simpler and more performant. Instead of useand then erase lines 37 to 52 inclusive in your code. You'll then need to adjust the bind index on line 53 from 2 to 1, of course.
Explanation: the currVal value will return the value generated by nextVal in the previous statement. Then you don't need to reread the value generated for parent and don't need to set it in the child. This is how Oracle's sequences are supposed to be used. See also this documentation
Note that it must all happen in the same transaction, so autocommit must be set to false, but you're already doing it.
subject: Java/Oracle JDBC transaction management and committing sessions