You will encounter several kinds of errors and warnings when working with PL/SQL program units:
1. Compilation errors: your programs will not compile. The errors reported by the PL/SQL compiler are of the form "PLS-NNNN: message".
2. Compile-time warnings: While compiling stored PL/SQL units, the PL/SQL compiler generates warnings for conditions that are not serious enough to cause errors and prevent compilation—for example, using a deprecated PL/SQL feature. Warnings take the form "PLW-NNNN: message".
3. Run-time errors: you (or your users) run your program and an error (known as an exception) is raised. Run-time errors generally take the form "ORA-NNNN: message".
You can write an exception handler in a PL/SQL block to trap and handle run-time errors.
The PL/SQL run-time engine will raise exceptions whenever the Oracle database detects a problem or it executes a RAISE or RAISE_APPLICATION_ERROR statement in your code. You can then trap or handle these exceptions in the exception section - or let the exception propagate unhandled to the enclosing block or host environment.
If you want processing in your block to continue, even if an exception was raised, enclose the code that might raise an exception in its own BEGIN-END nested block. Then add an exception to that block, trap the error, record information about what went wrong, and then allow the (now) outer block to continue.
When you execute a non-SELECT DML statement against a table and an error occurs, the statement is terminated and rolled back in its entirety. This can be wasteful of time and system resources. You can avoid this situation by using the DML error logging feature.
To use DML error logging, you add a LOG ERRORS statement clause that specifies the name of an error logging table into which the database records errors encountered during DML operations. When you add this error logging clause, certain types of errors no longer terminate and roll back the statement. Instead, each error is logged and the statement continues. Afterwards, you can query the contents of the table to either move your error information into the application log or take corrective action on the rows that caused problems.
The error logging clause allows you to specify an upper limit for the number of errors that are allowed to be logged for that statement. If this number is exceeded, then the statement terminates with an error and all its changes are rolled back. Alternatively, you can specify REJECT LIMIT UNLIMITED to allow any number of errors to be logged.
If Oracle is able to log all the eventual errors without exceeding the limit allowed, the statement can succeed and the changes that were successful are not rolled back.
The error logging is always performed as an autonomous transaction, so that the logged errors are not rolled back when a DML statement fails and/or is rolled back, thus allowing them to be used for checking and error correction.
When is it a good idea to implement error log tables?
Certainly in the development and test phases of a project, they are an excellent mechanism for catching issues that were not captured as part of the application requirements.
Are error log tables needed in production?
Can you safely say that all accepted requirements were correctly implemented, that the requirements are complete and that nothing was left out? If the answer is Yes, then you don't need those tables (nothing can go wrong). If the answer is No, they may help you debug issues as they pop up.
You can implement error log tables (or equivalents thereof) as part of instrumentation that documents execution of application logic, and in many cases that is probably the best way to go. But, for tables that are subjected to nothing but simple DML, yet have constraints and therefore the ability to cause errors, error log tables may be the easiest and "cheapest" way of documenting when something didn't work out the way we thought it would.