Exceptions are the run time errors that exist in a program and only occurs if the condition is met. In PL/SQL, exceptions are run time error and can arise from design faults, coding mistakes, hardware failures, or many other sources.
In PL/SQL exceptions are meant to be handled. Exceptions when occur, if not handled can cause the termination of a block. Exceptions can be internally defined (by the run-time system) or user defined.
Exception Handlers
Any PL/SQL block can have an exception-handling part, which can have one or more exception handlers. An exception handler processes a raised exception.
DECLARE
variable declaration;
BEGIN
executable part;
EXCEPTION
WHEN handler1 THEN
statement1;
WHEN handler2 THEN
statement2;
WHEN OTHERS THEN
statement3;
END;
Every exception handler must have a name. Oracle provides some named exception handlers such NO_DATA_FOUND, INVALID_NUMBER and many more. Oracle PL/SQL also allows users to declare names for user defined exceptions.
In addition to that, PL/SQL provides OTEHRS exception handler to handle unexpected exceptions. In the exception-handling part of a block, the WHEN OTHERS exception handler is optional. It can appear only once, as the last exception handler in the exception-handling part of the block.
Advantages of Exception Handlers
- It is easy and very common to overlook places where errors might occur while writing a program. With exception handlers, you need not know every possible error or everywhere that error might occur. You only need to include an exception-handling part in each block where errors might occur. Thus, using exception handlers in code for error-handling makes programs easier to write and understand, and also reduces the likelihood of abnormal termination.
- You can include exception handlers in all known and unknown places. When an error occurs anywhere in the block (including inside a sub-block), then an exception handler handles it. You can take the help of variables to identify which part of the program failed during an exception.
- Exception handling part is isolated from the actual business logic. This increases readability and easy debug of code.
Error vs. Exceptions in PL/SQL
Exceptions are not same as errors. Errors are always corrected, but not handled. For example, if you have a typo error or syntax error then you correct the code.
| Error | Exception |
|---|---|
| Code mistakes that makes the program not to execute properly. | A program may execute properly while containing exceptions. Exceptions occur only when the error condition arises. |
| Errors occur at compile time. | Exceptions are run time errors. |
| Errors are corrected, not handled. | Exceptions are handled. |
Every error is an identifier in Oracle. That means every error produced by Oracle is an identifiable situation existing in the system. When any Exception is generated it must be handled. If it is not handled then it propagates to the next block. If in that block it is not handled then propagates to the next and so on.There are 2 types of exceptions in Oracle PL/SQL.
- Pre defined Exception
- User Defined exception
Exception Propagation
Unhandled exception propagates. That means, if an exception is raised in a block that has no exception handler for it, then the exception propagates to the outer block or to the calling block. If there is no handler for the exception, then PL/SQL returns an unhandled exception error.
Any exception generated at BEGIN block can be handled by EXCEPTION block. But if the exception is generated at the DECLARE part then it can’t be handled by the EXCEPTION block and in that case the exception propagates. Hence any Exception generated on the Declaration part propagates. So, it is always advisable to do all the assignments at the declaration part only.