What is a PL SQL exception

The exception part of a PL / SQL block

PL / SQL has a powerful concept for handling errors or exceptions in programs. It is actually possible to have stable programs that are “bulletproof” and secure errors in the database or the use of the database. Exception handling is event-based. All that has to be done is to formulate an exception condition and whenever this condition applies, the corresponding exception handling part, which you also formulate yourself, is called.

As is well known, a PL / SQL block consists of several parts: the declaration part, the executable part and the error handling part (exception section).

If an error occurs in the executable part of the block, program control is passed to the exception part. PL / SQL searches the exception part for handling for the error that has occurred.

An error is considered handled if a so-called Exception handler exists. This means if there is a WHEN clause that corresponds to the exception (= error) that has occurred. If so, the executable statements of the exception handler are executed. When there is no treatment, there is WHENOTHERS Clause exists, the statements formulated here are carried out. If none of the above conditions apply, the exception is considered untreated and the error code including the error text is “passed on”, in the simplest case presented to the user.

Types of exceptions

There are four types of exceptions.

  • Named system exceptions. These are exceptions which have already been named by ORACLE and which are triggered as a result of an error in PL / SQL programs or within the database.
  • Named exceptions defined by the programmer. These errors are triggered by your own programming logic (for example, do you want to trigger an error when an employee's salary is less than zero). These exceptions can be named in the declaration and triggered in the executable part (RAISE).
  • Unnamed system exceptions. These exceptions are also triggered due to an error within PL / SQL or within the database, but are not named by ORACLE. Your program will therefore not be able to respond to these errors without further preparation. You can use the EXCEPTION_INIT pragma to link these ORACLE errors to an exception in your PL / SQL program and then react to them.
  • Unnamed, exceptions defined by the programmer. These exceptions are defined and triggered by the developer. They are triggered and "passed on" by the RAISE_APPLICATION_ERROR procedure. A reaction within the same program is not possible with this error class.

Named system exceptions

Examples of named system errors are e.g. NO_DATA_FOUND, DUP_VAL_ON_INDEX or ZERO_DIVIDE exceptions. (The named exceptions are best found in the ORACLE documentation. However, they are only a very small part of the possible errors that the database can return)

Example:

Named exceptions defined by the programmer

Here is an example of a programmer named exception. I created an exception with the name no_sales. In the executable part, I explicitly throw the exception. The execution is continued immediately in the exception part.

The exception is defined in the declaration part. The executable part only contains the instruction to trigger the exception. PL / SQL now searches the exception part for the exception handler of the exception and executes the statements for the handler (the NULL statement). The error or exception is then treated as having been dealt with.

Unnamed system exceptions

If an internal error occurs (for example an extent cannot be created or authorization to manipulate tables is missing), ORACLE delivers the corresponding error, which is identified by its error number. PL / SQL does not provide any predefined errors here. But we can write our own handler for such errors. To do this, you link the ORACLE error to an exception declared in the program. This is done with the EXCEPTION_INIT pragma.

The example does not make sense, but shows the binding of the errors: ORACLEs? Error -00001 is the error “duplicate values ​​found”, which occurs, for example, when a unique constraint is violated. Whenever this error occurs in the executable part, the exception handler is called. The example shows that you can still trigger such errors yourself with RAISE. The example does not make sense because there is already an exception predefined by ORACLE for the error -00001: DUP_VAL_ON_INDEX.

Behavior and propagation

When an exception is declared in a block, it is local within the block, but global for all blocks enclosed by the block. All locally declared exceptions have priority over the system exceptions. This means that if you generate an exception no_data_found, this exception replaces the standard behavior of the system exception with the same name. So it is not a good idea to name your own exceptions in the same way as predefined exceptions.

If an exception is thrown, PL / SQL looks for the appropriate handler in the current block. If the error is not dealt with there, PL / SQL propagates the error to the higher-level block. If the error is not dealt with here either, propagation continues. If there are no further blocks to “pass up”, the user receives an unhandled exception error.

Throw exceptions

Exceptions are automatically thrown when a database error or PL / SQL error occurs. However, they can also be triggered explicitly by the developer using the RAISE statement. The developer can also use the RAISE_APPLICATION_ERROR procedure to report errors to the client application.

Handle exceptions

If an exception is triggered, normal program execution is aborted and the exception part receives program control. From the exception part it is not possible to return to the executable part. This seems like a huge limitation, but you can get around it with nested blocks.

If you use the WHENOTHERS clause in the exception part, this must be the last handler in the exception part. Because PL / SQL scans the exception part from top to bottom for the appropriate handler. Since WHENOTHERS reacts to every form of exception, the handlers behind it would never be called.

Unhandled exceptions

If an exception is thrown and there is no handler in the exception part, this is referred to as an unhandled exception and is propagated. A well-designed application will have a WHENOTHERS part to catch unexpected exceptions.

There are two standards that allow you to check which error has occurred: SQLCODE and SQLERRM.

SQLCODE and SQLERRM

The SQLCODE function is used to determine the error number of the last exception that occurred. The SQLERRM function receives the error number as an argument and returns the associated error text.

RAISE_APPLICATION_ERROR

The RAISE_APPLICATION_ERROR procedure is the only way to send errors to the client application. It is often used to report database errors to the client with the help of triggers. The procedure is called with two arguments. The first argument is the error number (numbers between -20001 and -21000 are freely available), the second argument any error text. The program terminates immediately after calling the procedure.

example

Overridden exceptions

System exceptions do not actually need to be declared because they are already declared as soon as PL / SQL is executed. However, if - for whatever reason - you want to declare your own exception with the same name as a system exception, this is possible. The user-defined exception overrides the system exception.

This exception overrides the system exception. It is no longer triggered if a duplicate value is inserted into a column with a UNIQUEINDEX, which would be normal behavior.

Qualify exceptions

So if for some sadistic reason you name a custom exception equal to a system exception, it overrides the system exception. Nevertheless, it is possible to distinguish between the two exceptions. You only have to be qualified more precisely.

The example above differentiates between the user-defined and the system exception and allows PL / SQL to find the correct error-handling part.

But overriding system exceptions is generally a bad idea, so we don't even think about the possible consequences and, even worse, the tedious debugging process.