PL/SQL For Practitioners – #4 Exception Handling

Hi there,

One of the most used features of PL/SQL is the exception handling. Exceptions can arise from many different sources such as coding mistakes and design faults. Although we can’t predict all failures that might happen, we can use exception handlers predefined in our programs or create our own exceptions to keep them under control. In this post, I’m going to cover the basic concept of exceptions and show some examples of how to use it.

Syntax

BEGIN
   --your code

EXCEPTION
  WHEN ex_name_1 THEN statements_1               -- Exception handler
  WHEN ex_name_2 OR ex_name_3 THEN statements_2  -- Exception handler
  WHEN OTHERS THEN statements_3                  -- Exception handler
END;

Example 1

Well, this is a pretty simple example where I’m inserting a new line in a table. However, the new line already exists in the table and the exception will handle this error. To facilitate your work, I’m sharing the code to create and populate the tables used in these examples: create_and_populate_tables_city_and_country

BEGIN
   INSERT INTO CITY (ID, NAME, NAME_ASCII, POPULATION, COUNTRY_ID)
   VALUES (13646, 'Conley', 'Conley', 5994, 2);
EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
       dbms_output.put_line('Oh my God, something wrong happened!!!');
END;

Result

Instead of a DBMS_OUTPUT, you can raise a message to the main program or log this error in a table or file. What we do with each exception it depends on the architecture and need of each program. Many systems have a predefined exception and error handling, in these cases you can follow what is suggested on the documentation.

Example 2

In this example, I’m trying to find a city id that doesn’t exist.

DECLARE 
   V_PROGRAM_NAME VARCHAR2(255);
   V_CITY_ROW CITY%ROWTYPE;
   
BEGIN
   V_PROGRAM_NAME := 'TEST_FOR_EXCEPTION_HANDLING';
   
   SELECT * INTO V_CITY_ROW FROM CITY WHERE ID = -1;
   
EXCEPTION
   WHEN NO_DATA_FOUND THEN
       dbms_output.put_line(V_PROGRAM_NAME || ': THE GIVEN CITY_ID DOES NOT EXISTS IN THE TABLE CITY');
END;

Result

Example 3 – Creating your own exception

Even though there are plenty of exceptions available to we use in our programs (as you can see in the table below), yet we can create our exceptions as we want to. The following example shows how to create an exception and how to use it.

DECLARE 
   V_PROGRAM_NAME VARCHAR2(255);
   V_ERROR_MSG VARCHAR2(255);
   V_CITY_ROW CITY%ROWTYPE;
   V_IS_VALID_COUNTRY VARCHAR2(1) := 'N';
   V_MY_EXCEPT EXCEPTION;
   
BEGIN
   V_PROGRAM_NAME := 'TEST_FOR_EXCEPTION_HANDLING';
   
   SELECT MAX(ID) +1 INTO V_CITY_ROW.ID FROM CITY;
   
   V_CITY_ROW.NAME       := 'Test City 1';
   V_CITY_ROW.NAME_ASCII := 'Test City 1';
   V_CITY_ROW.POPULATION := 10293;
   V_CITY_ROW.COUNTRY_ID := 2;
      
   IF LENGTH(V_CITY_ROW.NAME) > 10 THEN
      V_ERROR_MSG := 'CITY NAME: ' || V_CITY_ROW.NAME || ' it is not valid!';
      RAISE V_MY_EXCEPT;
   END IF;
   
   INSERT INTO CITY VALUES(V_CITY_ROW.ID, V_CITY_ROW.NAME, V_CITY_ROW.NAME_ASCII, V_CITY_ROW.POPULATION, V_CITY_ROW.COUNTRY_ID);
   
EXCEPTION
   WHEN V_MY_EXCEPT THEN
       dbms_output.put_line(V_PROGRAM_NAME || ' - ERROR_MSG: ' || V_ERROR_MSG);
   WHEN OTHERS THEN 
       dbms_output.put_line(V_PROGRAM_NAME || ' - SQLCODE: ' || SQLCODE || ' - SQLERRM: ' || SQLERRM);
END;

Result

Table with other exceptions

This post is part of a series about the most common structures in PL/SQL. You can check the rest of them in the link below.

PL/SQL for real world – 8 features that you have to know

Please write down in the comments any further questions you may have, or get in touch via e-mail and LinkedIn. I’ll be happy to help you.