How to handle user defined exceptions in PLSQL? User defined exceptions

In my previous article I have given the implicit exception with real world examples. I have already explained that programmers also can handle and create their own exceptions. These exceptions are very important to handle when you are handling complex business logic. In this article we can see the answer of the question – How to handle user defined exceptions in PL SQL?

What you will find in this article?

1.How to handle user defined Exceptions?

2.Examples of User defined exceptions

How to handle user defined Exceptions?

You can also handle the user defined exceptions in PLSQL. There are so many requirements as per your application and business logic. For example if you can only open bank account if 100 rupees are there in the bank. You can handle the exception using or defining user defined exception.

There are following steps to define and handle user defined exceptions :

Step 1 : Declare the exception :

The step 1 to handle the exception is to declare the exception in declaration section.

Step 2 : RAISE exception :

The second step is to RAISE exception using the RAISE keyword.

Step 3 : Handle raised exception :

The third step is to handle the exception. All three steps are defined in following example.

If you can see the above example at no.1 step is to declare the exception in declare section. If you are not able to find the department no in department table then you need to RAISE exception using RAISE. Then in exception section you can handle the exception.

RAISE_APPLICATION_ERROR procedure :

The RAISE_APPLICATION_ERROR procedure is used to issue the user defined error message from stored subprograms.

Raise_application_Error(error_no,Message,True/False);

Error_number : It is user defined number between -20000 to -20999.

Message : It is user defined message up to 2048 bytes.

These procedure will be used in executable and exception section.

How to use this :

Example :

declare
v_employee_id number:=1;
—e_invalid_no exception;
begin

update employees
set salary=20000
where employee_id=v_employee_id;

if sql%notfound then
—raise e_invalid_no;
raise_application_error(-20000, ‘invalid emp ID’);
end if;

commit;

end;

These are few examples of User defined exceptions. With using these two procedures you can handle user-defined exceptions. I hope you like this article. If you like this article or if you have any issues with the same kindly comment in comments section.