Exception Handling in PL/SQL

Errors are classified into two types those are

1. Syntactical errors

These errors will be raise when the user violate the language rules, these errors will be raise at compile time.

2. Logical Errors

These errors will be raise if the program contains any logical mistakes and those will be raised at run time. We can handle these errors by using the exception handling mechanism.

Exceptions are classified into two types those are

1. System defined exceptions

An exception which is defined by the system internally those exceptions can be called as System defined exceptions.

2. User defined Exceptions

An exception which is defined by the user manually and programmatically those exceptions are called User defined exceptions.

1. NO_DATA_FOUND EXCEPTION

This exception will be raised when the user’s data not available in the table.

Write a PL/SQL block to raise NO_DATA_FOUND exception.

declare
   a emp%rowtype;
begin
   select * into a from emp where empno=&a;
   dbms_output.put_line('ename is'||a.ename);
   dbms_output.put_line('salary is'||a.sal);
   dbms_output.put_line('department no is'||a.deptno);
exception
   when no_data_found then
   dbms_output.put_line('Data is not available for given number please enter another number');
end;

2. Too many rows

This exception will be raised when the user is trying to fetch more than one record at a time.

E.g

declare
   a number;
   b varchar2(10);
   c number;
begin
   select ename,sal into b,c from emp where deptno=&a;
   dbms_output.put_line(b||' ' ||c);
end;

ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 6

3. Dup_val_on_index

This exception will be raised if the user trying to enter duplicate values under primary key constraint column or unique key constraint column.

E.g

declare
   a emp.empno%type;
   b emp.ename%type;
   c emp.sal%type;
   d emp.deptno%type;
begin
   insert into emp(empno,ename,sal,deptno) values(&a,&b,&c,&d);
   dbms_output.put_line('Values inserted');
exception
   when dup_val_on_index then
   dbms_output.put_line('the entered number already in the table');
end;

4. Value error

This exception will be raised if the data types are not matching.

E.g

declare
   a number;
   b number;
   c number;
   d number;
begin
   select ename,sal,deptno into b,c,d from emp where empno=&a;
   dbms_output.put_line(b||' ' ||c||' '||d);
Exception
   when value_error then
   dbms_output.put_line('type not mathced here');
end;

Output 
Enter value for a: 7788
old   7:  select ename,sal,deptno into b,c,d from emp where empno=&a;
new   7:  select ename,sal,deptno into b,c,d from emp where empno=7788;
type not mathced here
PL/SQL procedure successfully completed.

User Defined Exceptions

declare
   a number:=&a;
   ashok exception;
begin
   if(a=10) then
      dbms_output.put_line('it is positive');
   end if;
   if (a=0) then
      raise ashok;
   end if;
exception
   when ashok then
      dbms_output.put_line('a is nuetral');
end;

Exception Handling in PL/SQL