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 Name | Error 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_FOUND | 100 |
| 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.
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.