Exception Handling

Introduction#

Oracle produces a variety of exceptions. You may be surprised how tedious it can be to have your code stop with some unclear message. To improve your PL/SQL code’s ability to get fixed easily it is necessary to handle exceptions at the lowest level. Never hide an exception “under the carpet”, unless you’re here to keep your piece of code for you only and for no one else to maintain.

The predefined errors.

Exception handling

What is an exception?

Exception in PL/SQL is an error created during a program execution.

We have three types of exceptions:
- Internally defined exceptions
- Predefined exceptions
- User-defined exceptions
What is an exception handling?

Exception handling is a possibility to keep our program running even if appear runtime error resulting from for example coding mistakes, hardware failures.We avoid it from exiting abruptly.

Syntax

The general syntax for exception section:

declare
    declaration Section
begin
    some statements

exception
    when exception_one then
        do something
    when exception_two then
        do something
    when exception_three then
        do something
    when others then
        do something
end;

An exception section has to be on the end of the PL/SQL block. PL/SQL gives us the opportunity to nest blocks, then each block may have its own exception section for example:

create or replace procedure nested_blocks
is
begin
    some statements
    begin
        some statements
        
    exception
        when exception_one then
            do something
    end;
exception 
    when exception_two then
        do something
end;

If exception will be raised in the nested block it should be handled in the inner exception section, but if inner exception section does not handle this exception then this exception will go to exception section of the external block.

Internally defined exceptions

An internally defined exception doesn’t have a name, but it has its own code.

When to use it?

If you know that your database operation might raise specific exceptions those which don’t have names, then you can give them names so that you can write exception handlers specifically for them. Otherwise, you can use them only with others exception handlers.

Syntax

declare 
    my_name_exc exception;
    pragma exception_init(my_name_exc,-37);
begin
    ...
exception 
    when my_name_exc then
        do something
end;

my_name_exc exception; that is the exception name declaration.

pragma exception_init(my_name_exc,-37); assign name to the error code of internally defined exception.

Example

We have an emp_id which is a primary key in emp table and a foreign key in dept table. If we try to remove emp_id when it has child records, it will be thrown an exception with code -2292.

create or replace procedure remove_employee
is
    emp_exception exception;
    pragma exception_init(emp_exception,-2292);
begin
    delete from emp where emp_id = 3;
exception
    when emp_exception then
        dbms_output.put_line('You can not do that!');
end;
/

Oracle documentation says: “An internally defined exception with a user-declared name is still an internally defined exception, not a user-defined exception.”

Predefined exceptions

Predefined exceptions are internally defined exceptions but they have names. Oracle database raise this type of exceptions automatically.

Example

create or replace procedure insert_emp
is
begin
    insert into emp (emp_id, ename) values ('1','Jon');

exception
    when dup_val_on_index then
        dbms_output.put_line('Duplicate value on index!');
end;
/

Below are examples exceptions name with theirs codes:

Exception Name	Error Code
NO_DATA_FOUND	-1403
ACCESS_INTO_NULL	-6530
CASE_NOT_FOUND	-6592
ROWTYPE_MISMATCH	-6504
TOO_MANY_ROWS	-1422
ZERO_DIVIDE	-1476
Full list of exception names and their codes on Oracle web-site.

User defined exceptions

As the name suggest user defined exceptions are created by users. If you want to create your own exception you have to:

Declare the exception
Raise it from your program
Create suitable exception handler to catch him.

Example

I want to update all salaries of workers. But if there are no workers, raise an exception.

create or replace procedure update_salary
is
    no_workers exception;
    v_counter number := 0;
begin
    select count(*) into v_counter from emp;
    if v_counter = 0 then
        raise no_workers;
    else
        update emp set salary = 3000;
    end if;

    exception
        when no_workers then
            raise_application_error(-20991,'We don''t have workers!');                
end;
/

What does it mean raise?

Exceptions are raised by database server automatically when there is a need, but if you want, you can raise explicitly any exception using raise.

Procedure raise_application_error(error_number,error_message);

error_number must be between -20000 and -20999
error_message message to display when error occurs.

Define custom exception, raise it and see where it comes from

To illustrate this, here is a function that has 3 different “wrong” behaviors

the parameter is completely stupid: we use a user-defined expression
the parameter has a typo: we use Oracle standard NO_DATA_FOUND error
another, but not handled case

Feel free to adapt it to your standards:

DECLARE
  this_is_not_acceptable EXCEPTION;
  PRAGMA EXCEPTION_INIT(this_is_not_acceptable, -20077);
  g_err varchar2 (200) := 'to-be-defined';
  w_schema all_tables.OWNER%Type;

  PROCEDURE get_schema( p_table in Varchar2, p_schema out Varchar2)
  Is 
    w_err varchar2 (200) := 'to-be-defined';
  BEGIN
    w_err := 'get_schema-step-1:';
    If (p_table = 'Delivery-Manager-Is-Silly') Then
      raise this_is_not_acceptable;
    end if;
    w_err := 'get_schema-step-2:';
    Select owner Into p_schema 
      From all_tables
     where table_name like(p_table||'%');
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- handle Oracle-defined exception
     dbms_output.put_line('[WARN]'||w_err||'This can happen. Check the table name you entered.');
  WHEN this_is_not_acceptable THEN
    -- handle your custom error
     dbms_output.put_line('[WARN]'||w_err||'Please don''t make fun of the delivery manager.');
  When others then
     dbms_output.put_line('[ERR]'||w_err||'unhandled exception:'||sqlerrm);
     raise;    
  END Get_schema;  

BEGIN
  g_err := 'Global; first call:';
  get_schema('Delivery-Manager-Is-Silly', w_schema);
  g_err := 'Global; second call:';
  get_schema('AAA', w_schema);
  g_err := 'Global; third call:';
  get_schema('', w_schema);
  g_err := 'Global; 4th call:';
  get_schema('Can''t reach this point due to previous error.', w_schema);
  
EXCEPTION
  When others then
     dbms_output.put_line('[ERR]'||g_err||'unhandled exception:'||sqlerrm);
  -- you may raise this again to the caller if error log isn't enough.
--  raise;
END;
/

Giving on a regular database:

[WARN]get_schema-step-1:Please don't make fun of the delivery manager.
[WARN]get_schema-step-2:This can happen. Check the table name you entered.
[ERR]get_schema-step-2:unhandled exception:ORA-01422: exact fetch returns more than requested number of rows
[ERR]Global; third call:unhandled exception:ORA-01422: exact fetch returns more than requested number of rows

Remember that exception are here to handle rare cases. I saw applications who raised an exception at every access, just to ask for the user password, saying “not connected”… so much computation waste.

Handling connexion error exceptions

Each standard Oracle error is associated with an error number. It’s important to anticipate what could go wrong in your code. Here for a connection to another database, it can be:

-28000 account is locked
-28001 password expired
-28002 grace period
-1017 wrong user / password

Here is a way to test what goes wrong with the user used by the database link:

declare
  v_dummy number;
begin
  -- testing db link
  execute immediate 'select COUNT(1) from dba_users@pass.world' into v_dummy ;
  -- if we get here, exception wasn't raised: display COUNT's result
  dbms_output.put_line(v_dummy||' users on PASS db');

EXCEPTION
  -- exception can be referred by their name in the predefined Oracle's list
    When LOGIN_DENIED 
    then  
        dbms_output.put_line('ORA-1017 / USERNAME OR PASSWORD INVALID, TRY AGAIN');
    When Others 
    then 
  -- or referred by their number: stored automatically in reserved variable SQLCODE    
        If  SQLCODE = '-2019'
        Then    
          dbms_output.put_line('ORA-2019 / Invalid db_link name');
        Elsif SQLCODE = '-1035'
        Then
          dbms_output.put_line('ORA-1035 / DATABASE IS ON RESTRICTED SESSION, CONTACT YOUR DBA');        
        Elsif SQLCODE = '-28000'
        Then
          dbms_output.put_line('ORA-28000 / ACCOUNT IS LOCKED. CONTACT YOUR DBA');
        Elsif SQLCODE = '-28001'
        Then
          dbms_output.put_line('ORA-28001 / PASSWORD EXPIRED. CONTACT YOUR DBA FOR CHANGE');
        Elsif SQLCODE  = '-28002'
        Then
          dbms_output.put_line('ORA-28002 / PASSWORD IS EXPIRED, CHANGED IT');
        Else
   -- and if it's not one of the exception you expected
          dbms_output.put_line('Exception not specifically handled');
          dbms_output.put_line('Oracle Said'||SQLCODE||':'||SQLERRM);
        End if;
END;
/