Exception Handling Mechanism in Oracle PL/SQL Programs

---

Exception Handling :
=============
Errors are classified into two types

• Syntax errors
• Logical errros

Syntax errros :
=========
Whenever user applying to violate the rules of a particular language then we are getting syntax errors.
-->these errors will be raised at the time of compiling the program.

Logical errors :
=========
These errors will be raised at the time of executing the program and these errors will be handled by using exception handling mechanism.

-->Exceptions are classified into two types.

• System defined errors
• User defined errors

System defined exceptions :
==================
An exception which is defined by the system along with the software those exceptions can be called as system defined errors.

Ex: no_data_found

1) No_data_found :
=============
This exception is raised when the value is not available in the table which is entered by the end user.

Example :
======

SQL>declare
a emp%rowtype;
begin
select * into a from emp where empno=&empno;
dbms_output.put_line(‘The employee name is’||a.ename);
dbms_output.put_line(‘The employee salary is’||a.sal);
dbms_output.put_line(‘The employee deptno is’||a.deptno);
exception
when no_data_found then
dbms_output.put_line(‘sorry data does not existed try another one!’);
end;

2)Zero_divide:
===========
This exception is raised whenever the end user trying to divide the number by zero.

Example :
======

SQL>declare
a number:=&a;
b number:=&b;
c number;
begin
c:=a/b;
dbms_output.put_line(‘the ruesut is’||c);
exception
when zero_divide then
dbms_output.put_line(‘Sorry ! we can’t divide the number by zero’);
end;

3)Dup_val_on_index:
==============
This exception is raised when the user is trying to enter duplicate values on primary constraint column or unique constraint column.

Example :
======

SQL>begin
insert into emp(empno,ename,sal) value(101,’techsnib’,3400);
exception
when dup_val_on_index then
dbms_output.put_line(‘sorry record is already existed’);
end;

4)value_error:
==========
This exception is raised when the datatypes are mismatched in the program.
Example :
======

SQL>declare
a number;
b number;
c number;
d number;
begin
select ename,sal,deptno into b,c,d from emp where eid=&a;
dbms_output.put_line(‘The employee name is’||b);
dbms_output.put_line(‘The employee salary is’||c);
dbms_output.put_line(‘The employee deptno is’||d);
exception
when value_error then
dbms_output.put_line(‘sorry the datatypes are mismatched’);
end;

5)too_many_rows:
============
This exception is raised when the user trying to fetch more than one record at a time.

Example :
======

SQL>declare
a number;
b varchar2(20);
c number;
d number;
begin
select ename,sal,deptno into b,c,d from emp where deptno=&a;
dbms_output.put_line(‘The employee name is’||b);
dbms_output.put_line(‘The employee salary is’||c);
dbms_output.put_line(‘The employee deptno is’||d);
exception
when value_error then
dbms_output.put_line(‘sorry the datatypes are ztime’);
end;

User defined errors :
=============
An exception which is raised by the user manually those exceptions can be called as user defined exceptions.

Example :
======

SQL>declare
a number:=&a;
user_def_excep exception;
begin
if(a>0) then
dbms_output.put_line(‘a is positive’);
else
if(a=0) then
raise user_def_excep;
else
dbms_output.put_line(‘a is negative’);
end if;
end if;
exception
when user_def_excep then
dbms_output.put_line(‘you entered zero’);
end;

---