Exceptions

PL/SQL supports an error handling management based on a throw/catch exception system similar to the one commonly used by modern programming languages.

Here is how it looks an anonymous block that throws and then catch a system exception:

begin
  raise value_error;
exception
  when value_error then
    dbms_output.put_line('A system exception has been raised');
end;

PL/SQL has a lot of predefined exceptions, as VALUE_ERROR that it is used above. You should expect it to be raised by a function when trying to use a value of the wrong type in a specific context.

As users, we could create our exceptions, and then raise and catch them, exactely as the system ones:

declare
  my_ex exception;
begin
  raise my_ex;
exception
  when my_ex then
    dbms_output.put_line('my_ex exception has been raised');
end;

In these two examples we have seen how to catch exception matching a specific type, but we can also use the catch-all clause WHEN OTHERS:

declare
  my_ex exception;
begin
  raise my_ex;
exception
  when others then
    dbms_output.put_line('An exception has been raised');
end;

Usually exception are raised by low level functions, and in everyday code is more common to catch exception that raise them:

declare
  l_value integer;
begin
  l_value := 3 / 0;
  dbms_output.put_line('Hello');
exception
  when zero_divide then
    dbms_output.put_line('Divide by zero');
end;

In this example we try do divide by zero, this leads to a ZERO_DIVIDE system exception, so the current block execution is interrupted (the 'Hello' message is not printed) and the control is passed to the catch block (marked EXCEPTION). All the WHEN clauses are checked sequentially till a matching one is found, and the relative code is executed. If no WHEN OTHERS is specified, and the exception is not intercepted before the end of the block, it is propagated to the caller of the current block.

Here is another example of system exception catching:

declare
  l_int integer;
  l_str varchar2(20) := 'hello';
begin
  l_int := l_str;
  dbms_output.put_line('Hello');
exception
  when value_error then
    dbms_output.put_line('Value error');
end;

We could raise an exception also using a numeric code, that should be in the interval [-20000, -20999] using the function raise_application_error():

declare
  my_exc_code integer := -20000;
begin
  raise_application_error(my_exc_code, 'Something bad happened');
exception
  when others then
    dbms_output.put_line(sqlcode);
    dbms_output.put_line(sqlerrm);
    dbms_output.put_line(dbms_utility.format_error_backtrace);
    dbms_output.put_line(dbms_utility.format_call_stack);
end;

In tis catch clause we could see a bunch of useful values to check for getting more information on the current exception.
SQLCODE gives us the code associated - in this case -20000;
SQLERRM contains the first 512 bytes of the associated error message;
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE includes information on where the exception has been raised;
DBMS_UTILITY.FORMAT_CALL_STACK gives us details on the exception stack trace.

More on PL/SQL exception handling in chapter 6 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein.