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;