PL/SQL Exception Handling
Advertisements
PL/SQL Exception Handling
The process of converting system error messages into user friendly error message is known as Exception handling. This is one of the powerful feature of PL/SQL to handle run time error and maintain normal flow of program.
Exception
An exception is an event, which occurs during the execution of a program, that disrupts the normal flow of the program's Instructions.
Type of Exception in PL/SQL
There are two type of exception in pl/sql.
- System defined Exception
- User defined exception
System defined Exception
An exception which is defined by the system along with the software. Those exception can be called as system defined exception. For example: NoDataFoundException.
User defined Exception
An exception which is defined by the user manually or programatically. Those exception are called as user defined exception.
Example
declare
a emp%rowtype;
begin
select * into a from emp where empno=&empno;
dbms_output.put_line(a.ename || ' ' a.sal || ' ' a.dept);
exception
when no_data_found then
dbms_output.put_line('Sorry data not available');
end;
Handling exception when number is divided by zero.
Example
declare
a number :=&a;
b number :=&b;
c number;
begin
c := a/b;
dbms_output.put_line('Result: ' || c);
exception
when zero_divide then
dbms_output.put_line('Number is not divided by zero');
end;
| Exception | Description |
|---|---|
| divide_zero | This exception will be raised if the user trying to divide the number by zero. |
| no_data_found | This exception will be raised when a SELECT...INTO clause does not return any row from a table. |
| value_error | This exception will be raised when the data type are not match. |
| dup_val_on_index | This exception will be raised if user trying to enter duplicate value on primary key constraint column. |
| too_many_rows | This exception will be raised when the user trying to fetch more than one record at a time. |
| cursor_already_open | This exception will be raised when you open a cursor that is already open. |
| invalid_cursor | This exception will be raised when you perform an invalid operation on a cursor like closing a cursor, fetch data from a cursor that is not opened. |
Google Advertisment