Exception Handling in PL/SQL

What is Exception Handling?

Exception handling is one of the most important things to learn in PL/SQL. There are so many articles written on it on other blogs and websites but still it is always good to refresh what you are already aware of (maybe you find something more!). In this article, I had tried my best to cover up all necessary information that a novice or experienced PL/SQL developer may need on exception handling.

Exceptions are everywhere around us be it in our colleagues or friends or neighbors, anywhere. But still we find out a way to handle them. Let’s take an example of a nerdy friend in a group of cool people. He is an exception, others in the group will handle him in a different way keeping their friendship intact.

In the same way, PL/SQL codes may face exceptions which must be necessarily handled. It ensures smooth working of programs and helps the programmer to identify the exact cause of why the exceptions are raised. Codes in which exceptions are absent are analogous to an aimless arrow which may fire back at you anytime.

What is the syntax of PL/SQL Exceptions? Show the example

Yes, why not. Below is the syntax of standard PL/SQL exception

Syntax

DECLARE
 declaration section
BEGIN
 <code>
EXCEPTION
 <exception>
END;

Above is the basic example of PL/SQL exceptions. We will see little bit more complex ones as and when we move ahead.

What are the types of exceptions?

There are two types of exceptions in PL/SQL

1) System-defined exceptions
2) User-defined exceptions

System Defined Exceptions

Exceptions are nothing but errors or warnings when some violate Oracle standard rules. They are sometimes termed as Pre-defined exceptions also.

List of table of System defined exceptions

ExceptionOracle ErrorSQLCODE Value
ACCESS_INTO_NULLORA-06530-6530
CASE_NOT_FOUNDORA-06592-6592
COLLECTION_IS_NULLORA-06531-6531
CURSOR_ALREADY_OPENORA-06511-6511
DUP_VAL_ON_INDEXORA-00001-1
INVALID_CURSORORA-01001-1001
INVALID_NUMBERORA-01722-1722
LOGIN_DENIEDORA-01017-1017
NO_DATA_FOUNDORA-01403+100
NOT_LOGGED_ONORA-01012-1012
PROGRAM_ERRORORA-06501-6501
ROWTYPE_MISMATCHORA-06504-6504
SELF_IS_NULLORA-30625-30625
STORAGE_ERRORORA-06500-6500
SUBSCRIPT_BEYOND_COUNTORA-06533-6533
SUBSCRIPT_OUTSIDE_LIMITORA-06532-6532
SYS_INVALID_ROWIDORA-01410-1410
TIMEOUT_ON_RESOURCEORA-00051-51
TOO_MANY_ROWSORA-01422-1422
VALUE_ERRORORA-06502-6502
ZERO_DIVIDEORA-01476-1476
Example
DECLARE
   v_per_day_sal NUMBER;
BEGIN
   SELECT salary / 30 INTO v_per_day_sal FROM employee_master;
EXCEPTION
   WHEN no_data_found THEN
      DBMS_OUTPUT.PUT_LINE('No Data Found for above criteria!');
END;

User-Defined Exceptions

The exceptions defined by a user in his own language that suits his requirements are termed as a user-defined exception. They have to be explicitly raised and declared in the declaration section of code.

Example
DECLARE
   candidate_age_reached EXCEPTION;
   v_age NUMBER;
   v_max_age NUMBER:= 35;
   v_emp_num NUMBER;
BEGIN
   SELECT employee_number, TO_NUMBER(TO_CHAR(sysdate, 'RR')) - TO_NUMBER(TO_CHAR(birthdate, 'RR')) 
     INTO v_emp_num , v_age
     FROM employee_master
    WHERE employee_number = 1;

IF v_age > v_max_age THEN
   RAISE candidate_age_reached;
ELSE
   DBMS_OUTPUT.PUT_LINE('Thank you for doing registration. Have a good day!');
END IF;

EXCEPTION
   WHEN candidate_age_reached THEN
      RAISE_APPLICATION_ERROR ('Age limit criteria of candidate '||v_emp_num||' is reached. He/She cannot be registered');
END;

What should I do when I encounter an exception?

Most of the developers use error messages to get popped up in their application for exception notifications. Some of them leave it as it is without returning any message and allows the program to go ahead further. Not-So-Good programmers write exception-less code. Oh, of course, I know you are not one of those Not-So-Goods.

A clever developer defines a ground rule for himself to include exception command in every SQL block of code he writes even though further processing of code is inevitable or not.

What is the advantage of using PL/SQL Exceptions?

Exceptions are essential and integral part of every coding you do in PL/SQL. There are certain reasons why they are very much required in your code block.

With your vigorous testing attitude your code may be running smooth now but what about the future? You pack your bags and leave for the better opportunity but what about someone who takes your ride and has to literally struggle to scavenge an error in your code?

Let’s say after a year or so you are working on same code to rectify the cause of the error that stopped the code block from running smooth? Ah! Now you understand my point, isn’t it?

Advantages

  • Readability of program is improved with exceptions.
  • Debugging of error / bug becomes fast.
  • Efforts and time can be saved.

SQLCODE and SQLERRM

SQLCODE and SQLERRM are two standard functions that return error number and error message in the PL/SQL code. Except for NO_DATA_FOUND (error number = +100) exception, all the error numbers are negative.

Raise_Application_Error

Raise_Application_Error returns a user-defined error message as well as user-defined error code whenever an exception is encountered.

Read here more about Raise_Application_Error

Few Tips for Exception Handling in PL/SQL

Use Locator variables

Sometimes when we write chunks of code, we write only one exception at the end. So whenever an exception arises it becomes difficult for us to identify the SQL which caused it.

For example,

BEGIN
   SELECT * FROM EMP_MASTER;
   SELECT * FROM DEPT_MASTER;
EXCEPTION
   WHEN NO_DATA_FOUND THEN ...
      RAISE_APPLICATION_ERROR(-20001, 'Data not present.');
      ---Can you identify which select statement raised this exception?
END;

You can use locator variables here if you do not wish to write multiple exception statements.

DECLARE
   v_point1 number := 1;
BEGIN
   SELECT * FROM EMP_MASTER;
     v_point1 := 2;
   SELECT * FROM DEPT_MASTER;
     v_point1 := 3;
   SELECT * FROM ATTENDANCE_MASTER;
EXCEPTION
   WHEN NO_DATA_FOUND THEN ...
      RAISE_APPLICATION_ERROR(-20001, 'Data not present - '||v_point1);
END;

Use of NULL in Exception

There will be times when you handle exceptions by displaying a warning or error message but there will also be some circumstances where you want your program to be processed further even though an exception is raised. Here, NULL keyword can be very useful to you.

Example

BEGIN
   DECLARE
      v_gender varchar2(10);
   BEGIN
      SELECT gender INTO v_gender FROM employee_master;
   EXCEPTION
      WHEN no_data_found THEN
         NULL;
   END;

INSERT INTO emp_mast_1(emp_number, emp_name, gender) VALUES (1, 'XYZ', v_gender);

END;

In this PL/SQL block, data in table emp_mast_1 should be filled even if the gender of an employee is returned or not.

Use Appropriate Names and Messages

The naming convention is one of the crucial things when it comes to writing code irrespective of whatever coding language you use. Improper names and messages could mislead a developer or user. Error messages with half information will not significantly reduce the time taken to find the root cause.

For Example,

DECLARE
   CURSOR c1 IS
   SELECT employee_num
     FROM employee_master
    WHERE DEPT_NUM = 10;

   v_empno NUMBER;
BEGIN
 OPEN c1;
    LOOP
       FETCH c1.employee_num INTO v_empno;
    EXIT WHEN c1%NOTFOUND;
    BEGIN
       SELECT birth_date
         FROM emp_birthdates
        WHERE employee_num = v_empno;
    EXCEPTION
       WHEN others THEN
          RAISE_APPLICATION_ERROR('Error occurred.');
    END;
    END LOOP;
 CLOSE c1;
END;

In the above example, if an exception is raised we will not be able to know for which employee it is caused.

So the use of appropriate error messages is fairly a good practice and an important too. We can write an exception message as below to identify an employee for whom it is raised and due to which reason.

RAISE_APPLICATION_ERROR('Error occurred for employee:'||v_empno||' due to '||SQLERRM);

If you liked the above post, please leave your comments below.

You can also Subscribe here to stay updated on latest posts of OracleMine.com.

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.