Exception Handling In PL/SQL.
Today we will discuss about Exceptions Handling in PL/SQL block As we are already aware about PL/SQL block in my previous blogs so you can easily able to understand Exception Handling .
Exception Handling can be used in Procedures, functions and sub-programs . So for reference of PL/SQL block and Procedures you can see my below blog.
https://dheeraj60.blogspot.com/2020/05/overview-of-plsql-and-its-benefit-in.html
https://dheeraj60.blogspot.com/2020/05/overview-of-plsql-stored-procedure-and.html
First we should understand what is Exception .Exception is an error condition during a program execution , In other word we can say exception is an error which disrupts the normal flow of program instructions.
PL/SQL supports developer to catch such errors/condition using EXCEPTION block in the PL/SQL program and can trace action against the error condition.
There are Two Types of Exception in PL/SQL
1. System-Defined Exception
2. User- Defined Exception
System- Defined Exceptions are predefined in PL/SQL which get raised WHEN certain database rule is violated. While in User-defined Exception users can create their own exceptions according to the need and to raise these exceptions explicitly by using raise command.
Note: PL/SQL doesn’t provide name for some system exceptions that is known as unnamed system exceptions. This type of exceptions don’t occur frequently and exceptions have two parts code with associate message.
The way to handle to these exceptions is to assign name to them using Pragma EXCEPTION_INIT
Syntax:
PRAGMA EXCEPTION_INIT(exception_name, -error_number);
Syntax for Exception Handling in PL/SQL Block:
In System Defined exception exceptions handled by when others and then . Syntax in simple we can write as below :
WHEN exception THEN
statement;
Proper Syntax for Exception handling is as below in PL/SQL block :
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling starts here >
WHEN exception1 THEN
exception1 statements
WHEN exception2 THEN
exception2 statements
........
WHEN others THEN
/* default exception handling code */
END;
Let’s start with one simple example for exceptional handling in PL/SQL block as in previous blogs we have created table employee table. See the below example for Exception Handling in PL/SQL block.
DECLARE
v_id employee.emp_id%type := 10009;
v_name employee.Name%type;
v_address employee.address%type;
BEGIN
SELECT name, address INTO v_name, v_address
FROM customers
WHERE emp_id = v_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| v_name);
DBMS_OUTPUT.PUT_LINE ('Address: ' || v_address);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No Name with Address!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
Once this code is executed in Anonymous PL/SQL block in SQL prompt , it will give the below result.
No Name with Address!
The above program should display the name and address of a employee which emp_id has passed in BEGIN select statement , since there is no employee id 10009 in employee table , the program will raise the run-time exception NO_DATA_FOUND, but we have captured this in the EXCEPTION block that’s why we are able to see the output of the above program by using Exceptional handling.
Raising Exceptions in PL/SQL block.
As we know Exceptions are raised by the database server automatically when there is any internal database error occurs , exception can be raised explicitily by the developer by simply using RAISE command .
Simple Syntax for Raising an exception is as below :
DECLARE
exception_name EXCEPTION;
BEGIN
IF condition THEN
RAISE exception_name;
END IF;
EXCEPTION
WHEN exception_name THEN
statement;
END;
We can use this syntax in raising the Oracle database standard exception or any user-defined Exception. We can use this syntax when we use user-defined exception.
User- Defined Exception in PL/SQL.
PL/SQL allows programmer to define own exception as per need in the program. User –defined Exception must be declared and then raised explicitily by using Raise Statement or the the Procedure RAISE_APPLICATION _ERROR.
Note : RAISE_APPLICATION_ERROR: It is used to display user-defined error messages with error number whose range is in between -20000 and -20999. When RAISE_APPLICATION_ERROR executes it returns error message and error code which looks same as Oracle built-in error.
Syntax for User-Defined exception is as below:
Declare
my-exception EXCEPTION;
Here my-exception is the name of EXCEPTION.
Example:
IN Below program we use same employee table where in this program we use substitution variable where program ask for Employee ID and when the user will enter an any invalid ID , then exception invalid id is raised.
DECLARE
v_id employee.emp_id%type := &v_id;
v_name employee.Name%type;
v_address employee.address%type;
exc_invalid_id EXCEPTION; -- User defined exception
BEGIN
IF v_id <= 0 THEN
RAISE ex_invalid_id;
ELSE
SELECT name, address INTO v_name, v_address
FROM employee
WHERE emp_id = v_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| v_name);
DBMS_OUTPUT.PUT_LINE ('Address: ' || v_address);
END IF;
EXCEPTION
WHEN exc_invalid_id THEN
dbms_output.put_line('ID should be greater than zero!');
WHEN no_data_found THEN
dbms_output.put_line('No Name with address exist!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
When we Run this PL/SQL code in sql prompt , it will ask user to give employee id , if they inter negative value then exc_invalid_id exception raised and output will be ID should be greater than zero! And if user enter any ID value which is not in employee table then output will be No Name with address exist! .
And if there is no invalid ID passed and matched the criteria then output will be displayed as Name and address from the table against emp_id .
Pre-Defined Exceptions in PL/SQL : PL/SQL provides many pre-defined exceptions , which was automatically excecuted when database rules are violated by a program . Simple example NO_DATA_FOUND is predefined exception when a select statement returns no rows . There are several pre-defined exceptions in Oracle database with return with Oracle error , code with messages such as CASE_NOT_FOUND , INVALID_NUMBER , LOGIN_DENIED, ZERO_DIVIDE etc which will give error code with messages.
This is all about PL/SQL exceptions in database. Exception is useful for programmers to trace the exact error and it’s very helpful for programmer to use the Exception handling in Program so that they can easily know where the error is .
Please read this blog carefully and let me know if you have any doubts.
Thanks
Comments
Post a Comment