Notes 0015

PL/SQL Exceptions

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.

Terminology

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).

EXCEPTION block

The general (usual) format of an exception block is:

EXCEPTION
    WHEN exception_name1 THEN
        code_to_handle_exception1;
    WHEN exception_name2 THEN
        code_to_handle_exception2;
    WHEN OTHERS THEN
        code_to_handle_all_others;
END;

The exception section is part of a more general PL/SQL code block; remember our PL/SQL block definition?

DECLARE
    variable_declarations
BEGIN
    procedural_code
EXCEPTION
    error_handling
END;

Whatever exceptions occur in the code block, are sent to the exception section.

Predefined Exceptions

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:

DECLARE
    A INT;
    B INT;
    C INT;
BEGIN
    A := 0;
    B := 2;
    C := B / A;
    DBMS_OUTPUT.PUT_LINE('C IS: ' || C);
END;

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:

DECLARE
    A INT;
    B INT;
    C INT;
BEGIN
    A := 0;
    B := 2;
    C := B / A;
    DBMS_OUTPUT.PUT_LINE('C IS: ' || C);
EXCEPTION
    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('HEY, A DIVISION BY ZERO!');
END;

What do we get now? We get:

HEY, A DIVISION BY ZERO!

PL/SQL procedure successfully completed.

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...

Undefined Exceptions

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:

OUR_DIV_OF_SOMETHING_BY_ZERO 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:

PRAGMA EXCEPTION_INIT(OUR_DIV_OF_SOMETHING_BY_ZERO,-1476);

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:

DECLARE
    A INT;
    B INT;
    C INT;
    OUR_DIV_OF_SOMETHING_BY_ZERO EXCEPTION;
    PRAGMA EXCEPTION_INIT(OUR_DIV_OF_SOMETHING_BY_ZERO,-1476);
BEGIN
    A := 0;
    B := 2;
    C := B / A;
    DBMS_OUTPUT.PUT_LINE('C IS: ' || C);
EXCEPTION
    WHEN OUR_DIV_OF_SOMETHING_BY_ZERO THEN
        DBMS_OUTPUT.PUT_LINE('HEY, A DIVISION BY ZERO!');
END;

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]).

User-Defined Exceptions

We have already seen most of the mechanism needed to define our own (user) exceptions. For example, the declare section:

OUR_DIV_OF_SOMETHING_BY_ZERO EXCEPTION;

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:

IF A = 0 THEN
    RAISE OUR_DIV_OF_SOMETHING_BY_ZERO;
END IF;
C := B / A;

Anyway, we can integrate that into our code:

DECLARE
    A INT;
    B INT;
    C INT;
    OUR_DIV_OF_SOMETHING_BY_ZERO EXCEPTION;
BEGIN
    A := 0;
    B := 2;
    IF A = 0 THEN
        RAISE OUR_DIV_OF_SOMETHING_BY_ZERO;
    END IF;
    C := B / A;
    DBMS_OUTPUT.PUT_LINE('C IS: ' || C);
EXCEPTION
    WHEN OUR_DIV_OF_SOMETHING_BY_ZERO THEN
        DBMS_OUTPUT.PUT_LINE('HEY, A DIVISION BY ZERO!');
END;

(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.

Exception Uses

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.

© 2006, Particle