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

Exception NameError Code
ACCESS_INTO_NULL-6530
CASE_NOT_FOUND-6592
COLLECTION_IS_NULL-6531
CURSOR_ALREADY_OPEN-6511
DUP_VAL_ON_INDEX-1
INVALID_CURSOR-1001
INVALID_NUMBER-1722
LOGIN_DENIED-1017
NO_DATA_FOUND100
NO_DATA_NEEDED-6548
NOT_LOGGED_ON-1012
PROGRAM_ERROR-6501
ROWTYPE_MISMATCH-6504
SELF_IS_NULL-30625
STORAGE_ERROR-6500
SUBSCRIPT_BEYOND_COUNT-6533
SUBSCRIPT_OUTSIDE_LIMIT-6532
SYS_INVALID_ROWID-1410
TIMEOUT_ON_RESOURCE-51
TOO_MANY_ROWS-1422
VALUE_ERROR-6502
ZERO_DIVIDE-1476

 

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.

About Joel Medeiros

I am an experienced Business and Data Analyst, ETL and PL/SQL with over 13 years of experience. My technical skills are focus on Oracle mainly in developing with tools such as PL/SQL, ODI and RIB.You also can check all my post right here https://ittutorial.org/author/joel-medeiros/Please, feel free to keep in touch by e-mail or Linkedin at: [email protected] | https://www.linkedin.com/in/joel-medeiros/

Leave a Reply

Your email address will not be published. Required fields are marked *