Like Java and C++, PL/SQL has a built in exception handling mechanism. What that means is that while coding the bulk of the code, you could avoid worrying about errors, and handle all errors in a special place, called exception handler.
Note that we are not talking about compilation errors. If there is a lexical or syntactical error in your PL/SQL statements you will simply not be able to run the code in the first place.
Exception handling refers to run-time errors. These are the type of errors that you cannot detect while writing your program. They only occur when your code is running (thus, the term: run-time).
There are three basic types of exceptions in PL/SQL: predefined, undefined, and user-defined.
A run-time error is usually referred to as exception. These exceptions (seen as unwanted events) are raised. When an exception event is raised, program control is transferred to an EXCEPTION section (mentioned earlier).
The general (usual) format of an exception block is:
The exception section is part of a more general PL/SQL code block; remember our PL/SQL block definition?
Whatever exceptions occur in the code block, are sent to the exception section.
Predefined exceptions correspond to common errors that are faced by many programs, and have been given a specific exception name. Below are some common exceptions, with their explanations.
ORA-00001: DUP_VAL_ON_INDEX Unique constraint on primary key violated.
ORA-01001: INVALID_CURSOR Illegal cursor operation
ORA-01403: NO_DATA_FOUND Query returns no records
ORA-01423: TOO_MANY_ROWS Query returns more rows than anticipated
ORA-01476: ZERO_DIVIDE Division by zero
ORA-01722: INVALID_NUMBER Invalid number conversion (like trying to convert '2B' to a number)
ORA-06502: VALUE_ERROR Error in truncation, arithmetic, or conversion operation
This list is by no means exhaustive. There are many predefined exceptions.
Anyway, let's do an example. Consider this code:
The code obviously does a division by zero (A := 0, then B / A), so, what do we get when we run this code? We get an error:
ORA-01476: divisor is equal to zero
Now, because this exact error is one of the predefined ones (it occurs quite often for Oracle to have predefined it), we can "handle" it by specifying an EXCEPTION section:
What do we get now? We get:
Notice that we do get the HEY, A DIVISION BY ZERO! string displayed. Now, normally you'd do something more useful than just displaying the fact that you got an error (probably log it, or handle it somehow, etc.)
Also notice that we got: PL/SQL procedure successfully completed. What does this mean? Does it mean we didn't get an error? No, it just means that the code did not raise any exceptions (the ZERO_DIVIDE exception that it raised we caught and 'handled' in our EXCEPTION block - so overall, the code didn't raise any exceptions that it did not handle). To the outside world, all this means is that the code ran without errors.
Now, what do we do when we don't know the name of our particular exception or when the name doesn't exist? That's what the next section is all about...
If we do not know the exception name, we can always define one ourselves! (ie: undefined exceptions can be defined)
We start in our DECLARE section, by declaring the exception:
Which declares an EXCEPTION named OUR_DIV_OF_SOMETHING_BY_ZERO. That's all it does. This is our own name, and it can be anything we like (except of course all the predefined exception names).
To tell Oracle that every time it sees the ORA-01476 exception (which is the ORA-01476: divisor is equal to zero; which we've seen earlier), we have to use a thing called a PRAGMA. PRAGMAs are compiler directives that do various things, and in our case, it will associate our exception with the ORA-01476 exception:
Notice that we provide our exception name (OUR_DIV_OF_SOMETHING_BY_ZERO), and some number. Well, this number is the exception code. We're binding our name to ORA-01476, which just means that it is an ORACLE exception number -01476. Knowing all we know about numbers, we can drop the starting zero, and end up with -1476. That's all there is to it!
Oh, and once the exception is defined (by us), it is no longer undefined, and we can use it in our EXCEPTION block just as other predefined exceptions. Here's a complete example:
The output is the same as in the previous example, so we won't go too deeply into that. The point is that we can define our own exceptions, and then bind (or INITialize them (using a PRAGMA EXCEPTION_INIT) to some undefined system exception (assuming we have the error code [number]).
We have already seen most of the mechanism needed to define our own (user) exceptions. For example, the declare section:
Remains exactly as it is. We just don't bind (or INIT) this exception to any system exception (ie: we do not use that PRAGMA). That's all there is to it. Oh, wait. No, that's not all. We also must RAISE the exception if we ever want it to be caught and handled.
We cannot depend on the system to raise our exception, so we must raise it ourselves if we sense something is going wrong. For example, how about this code fragment:
Anyway, we can integrate that into our code:
(the output is the same as before)
Notice that we never use the PRAGMA, and notice that we also have to check for the division by zero ourselves in order to RAISE the exception manually. The exception is then caught and handled as before. The EXCEPTION section doesn't really need to know if the exception is predefined, undefined, or user-defined. In the EXCEPTION section, all exceptions have a name.
Also, if we don't check the condition, we get our old ORA-01476: divisor is equal to zero back. This is because the system does encounter the error, and since we never bound our exception to the system one, the system RAISEs its own exception.
Also notice if we never catch (or handle) our user-defined exception in the code... (ie: we simply omit the EXCEPTION section), then oracle will complain with a ORA-06510: PL/SQL: unhandled user-defined exception (Which we cannot handle ourselves for some reason; Anyway).
Other ways to raise exceptions
There are other ways to raise exceptions, and we've talked about at least one other method when we dealt with triggers. Basically, you have code like:
RAISE_APPLICATION_ERROR(-20000,'SOME ERROR TEXT');
Which will raise ORA-20000 (note the -20000 number) that will have text 'SOME ERROR TEXT'. We won't go into much detail; you can play with these on your own.
Well, system predefined exceptions can mostly be used to spot trouble spots in the code (like division by zero, etc.), while undefined exceptions are mostly used to redefine the system exceptions which we don't have a convenient name for (there are a ton of predefined ones, so mostly you won't need to use undefined exceptions - but if you ever do need it, you know how to do it).
User defined exceptions are great for handling business rules (business logic, etc.)
It is important to note that you shouldn't use exceptions to drive the basic functionality. Exceptions are errors, and are not to be used for simple flow control. You have IF statements for that.
That's it. Finally.