PL/SQL Exception Handling


Prev Tutorial Next Tutorial

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;
ExceptionDescription
divide_zeroThis exception will be raised if the user trying to divide the number by zero.
no_data_foundThis exception will be raised when a SELECT...INTO clause does not return any row from a table.
value_errorThis exception will be raised when the data type are not match.
dup_val_on_indexThis exception will be raised if user trying to enter duplicate value on primary key constraint column.
too_many_rowsThis exception will be raised when the user trying to fetch more than one record at a time.
cursor_already_openThis exception will be raised when you open a cursor that is already open.
invalid_cursorThis 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.

Prev Tutorial Next Tutorial

Advertisements




Facebook Likes

Advertisements