Thanks Martin. I've tried Googling around this ORA error and all references suggests the issue to be related to remote database trigger and stuff.
However, in this case, the stored procedure is never reached. I'm able to establish the fact because, I have an exception block added to the procedure which logs all errors that happens in the block.
So, had it been an issue with the remote database or trigger, it should have been well caught in my stored procedure exception block.
Not sure if my statement makes sense.
In any case, the description implies this need not be a bug in your code. This seems to be a kind of error that sometimes 'just happens' and you'll probably need to handle it gracefully, similarly to having to handle ORA-08177 when using Serializable isolation level. You could, for example, automatically restart the transaction when this error occurs - ie. in the catch block, inspect the exception, and if it was the ORA-02067 (sqlE.getErrorCode() == 2067), retry (probably limiting the retry count to a sensible number like 3 or 5).
You learn how to close your eyes and tell yourself "this just isn't really happening to me." Tiny ad:
SKIP - a book about connecting industrious people with elderly land owners