Exceptions are runtime errors or unexpected events that occur during the execution of a PL/SQL code block.
The oracle engine is the first one to identify such an exception and it immediately tries to resolve it by default exception handler.
The default exception handler is a block of code predefined in the memory to take the appropriate action against exceptions.
Exception handling can be done in the EXCEPTION part of PL/SQL program code block.
Following is the syntax for it:
DECLARE -- Declaration statements; BEGIN -- SQL statements; -- Procedural statements; EXCEPTION -- Exception handling statements; END;
Let's cover both types of exceptions one by one.
In order to handle common exceptions that occur while running PL/SQL code, there are two types of exception handlers in oracle:
Such exceptions are the predefined names given by oracle for those exceptions that occur most commonly.
Following is the syntax for handling named exception:
EXCEPTION WHEN <exception_name> THEN -- take action
There are number of pre-defined named exceptions available by default. Few of them are shown in the table below, along with their meanings:
||Occurs when invalid username or invalid password is given while connecting to Oracle.|
||Occurs when select statement returns more than one row.|
||Occurs when invalid datatype or size is given by the user.|
||Occurs when no records are found.|
||Occurs when a unique constraint is applied on some column and execution of Insert or Update leads to creation of duplicate records for that column.|
||Occurs when internal error arise in program.|
||Occurs when the division of any variable value is done by zero.|
Time for an Example!
Below we have a simple PL/SQL code block, to demonstrate the use of Named Exception Handler,
set serveroutput on; DECLARE a int; b int; c int; BEGIN a := &a; b := &b; c := a/b; dbms_output.put_line('RESULT=' || c); EXCEPTION when ZERO_DIVIDE then dbms_output.put_line('Division by 0 is not possible'); END;
Enter the value for a:10 Enter the value for b:0 Division by 0 is not possible PL/SQL procedure successfully completed.
In oracle, some of the pre-defined exceptions are numbered in the form of four integers preceded by a hyphen symbol. To handle such exceptions we should assign a name to them before using them.
This can be done by using the Pragma exception technique in which a numbered exception handler is bound to a name. For this purpose, we use a keyword in PL/SQL program and write a statement that binds a name to a numbered exception using the following syntax and this statement is written in the DECLARE section of program:
pragma exception_init(exception_name, exception _number);
pragma exception_init(case doesn't matter) is a keyword indicating Pragma exception technique with two arguments:
Time for an Example!
Below we have a table with Student's data in it.
In the PL/SQL program below, we will be using the above table student to demonstrate the use of Numbered Exception,
set serveroutput on; DECLARE sno student.rollno%type; snm student.sname%type; s_age student.age%type; cr student.course%type; -- Exception name declared below already_exist EXCEPTION; -- pragma statement to provide name to numbered exception pragma exception_init(already_exist, -1); BEGIN sno:=&rollno; snm:='&sname'; s_age:=&age; cr:='&course'; INSERT into student values(sno, snm, s_age, cr); dbms_output.put_line('Record inserted'); EXCEPTION WHEN already_exist THEN dbms_output.put_line('Record already exist'); END;
Enter the value for sno:11 Enter the value for snm:heena Enter the value for s_age:20 Enter the value for cr:bsc Record already exist PL/SQL procedure successfully completed.
In the above program, whenever a primary key concept(records should be unique and not null) is violated oracle generates a numbered exception by -1 and that is why when rollno entered by user during execution of above program was 11. The exception section of the program comes into action and message is displayed before the user Record already exist.
pragma keyword in the declare section of the program
already_exist string is mapped to a numbered exception -1.
In any program, there is a possibility that a number of errors can occur that may not be considered as exceptions by oracle. In that case, an exception can be defined by the programmer while writing the code such type of exceptions are called User-defined exception.
User defined exceptions are in general defined to handle special cases where our code can generate exception due to our code logic.
Also, in your code logic, you can explicitly specify to genrate an exception using the
RAISE keyword and then handle it using the
Following is the syntax for it,
DECLARE <exception name> EXCEPTION BEGIN <sql sentence> If <test_condition> THEN RAISE <exception_name>; END IF; EXCEPTION WHEN <exception_name> THEN -- some action END;
Let's take an example to understand how to use user-defined exception. Below we have a simple example,
In the PL/SQL program below, we will be using the above table student to demonstrate the use of User-defined Exception,
set serveroutput on; DECLARE sno student.rollno%type; snm student.sname%type; crno student.total_course%type; invalid_total EXCEPTION; BEGIN sno := &rollno; snm := '&sname'; crno:=total_courses; IF (crno > 3) THEN RAISE invalid_total; END IF; INSERT into student values(sno, snm, crno); EXCEPTION WHEN invalid_total THEN dbms_output.put_line('Total number of courses cannot be more than 3'); END;
Enter the value for sno:15 Enter the value for snm:Akash Enter the value for crno:5 Total number of courses cannot be more than 3 PL/SQL procedure successfully completed.
In the above program,
User-defined exception called invalid_total is used which is generated when total number of courses is greater than 3(when a student can be enrolled maximum in 3 courses)
And with this we are done with exceptions.