Would a scenario where the OTHERS exception handler in PL/SQL is not catching an Oracle error (ORA-xx) be considered a defect in the Oracle database, or is this expected behaviour (as has been suggested to me)?
The Oracle documentation for PL/SQL states "The optional OTHERS exception handler, which is always the last handler in a block or subprogram, acts as the handler for all exceptions not named specifically. Thus, a block or subprogram can have only one OTHERS handler. Use of the OTHERS handler guarantees that no exception will go unhandled."
I have a scenario where an Oracle error when executing a DML database statement isn't caught by the OTHERS exception handler and as a consequence we have in effect lost data as there is no exception to indicate that the DML operation has failed.
To be able to implement a reliable business application we are depending on that errors when performing DML operations are caught by the exception handler so that we can handle the failure (retry, alert or error message etc).
Must we create explicit exception handlers for certain ORA-xx errors, or should we be able to rely on that the OTHERS exception handler will handle any unhandled exception?
Any views from the PL/SQL community would be appreciated..