Exception Handling in PL/SQL

 Exception Handling in PL/SQL:

·       ·    Exception is an error which occurs during the program execution.

·       ·    PL/SQL provides Exception block thus helping the programmer to find the problem and resolve it.

     ·     Resolves the error in two ways,

1.    User defined exceptions

2.    System defined exceptions

User defined exceptions:

·       ·    PL/SQL User defined exception to make tour own exception.

·       ·    PL/SQL User defined exception must be declared yourself and raise      explicitly and it must be satisfied the oracle rules.

Syntax:

Declare        User_defined_exception_name    EXCEPTION; Begin       Raise User_defined_exception_name; Exception      When User_defined_exception_name       Then                User defined Statement; End;

 

 

 

 

 

 

 

Syntax Explanation:

Declare exception:

 User_defined_exception_name    EXCEPTION;

Raise exception:

 Raise User_defined_exception_name;

Implement exception conditions:

 Exception

     When User_defined_exception_name Then

              User defined Statement;

Example:

Following program for finding particular department employees count in the emp table if there is no employees in the department then it will raise

Exception.

Declare Emp_ex Exception;          V_count number; Begin           Select count(ename)          Into v_count from emp          Where deptno=10;    If v_count = 0 then         Raise Emp_ex;    End if; Exception   When Emp_ex then   Dbms_output.put_line(‘Error’);   Dbms_output.put_line(‘No employees in the Dept 10’); End;

 

Output:

Error No employees in the Dept 10

 

System defined exceptions:

·    System defined errors are predefined exception in the oracle PL/SQL.

Some exceptions are,

1.    No_data_found

2.    Too_many_rows

3.    Value_error

4.    Zero_divide 

Explanation:

Create table cust( c_id number,c_name varchar2(20),c_city varchar2(20));

1.    Insert into cust Values(1,’tani’,’xxxx’);

2.    Insert into cust values(2,’thara’,’yyyy’);

3.    Insert into cust values(2,’tae’,’zzzz’);

C_id	C_name	C_city
1	tani	Xxxx
2	Thara	Yyyy
3	tae	Zzzz

 

No_data_found:

·    It is Raised when SELECT INTO statement return no rows.

Declare        V_id number; Begin       Select c_id       into v_id       from cust       Where c_name=’dev’ Exception     When no_data_found then          Dbms_output.put_line(‘There is no customer in the table’); End;

Output:

There is no customer in the table

 

Too_Many_rows:

 ·    It is raised when Select INTO statement return Too many rows.

Declare        V_name varchar2(20); Begin       Select c_name       into v_name       from cust Exception     When Too_many_rows then          Dbms_output.put_line(‘Error);          Dbms_output.put_line(‘Returns too many values); End;

Output:

Error Returns too many values

 

Value_error:

·       ·    It is raised when the return value input data type is wrong.

·       ·    Like arithmetic, numeric, string, data, conversion errors.

Declare        V_name  number; Begin       Select c_name       into v_name       from cust       where c_city=’yyyy’ Exception     When value_error  then          Dbms_output.put_line(‘Error);          Dbms_output.put_line(‘Please change the data type of the variable); End;

Output:

Error Please change the data type of the variable

Zero_divide:

·       It is raised when Dividing by 0.

Declare    a Number  := 200;    b Number  := 0;    Result number; Begin    Result :=a/b;    dbms_output.put_line('the answer is'||Result); Exception    When zero_divide  Then       dbms_output.put_line(' Please check the values again');       dbms_output.put_line(‘Because it is dividing by 0 ); End;

Output:

Please check the values again Because it is dividing by 0

 

Share: