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
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
|Exception||Oracle Error||SQLCODE Value|
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;
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.
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?
- 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 returns a user-defined error message as well as user-defined error code whenever an exception is encountered.
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.
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.
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.
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.
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 firstname.lastname@example.org. Again I appreciate your visit. Hope to see you again and again!