PL/SQL Raise Exception

Introduction to PL/SQL Raise Exception

PL/SQL provides the raise exception functionality to the user; many times, exceptions may occur in our application or software that means there is a problem or error in our code to execute the application. There is no control, but when an exception is raised, we can handle all the errors or problems during the execution of an application. See here every error or problem we cannot consider as an exception. Sometimes it occurs internal processing of the database, or sometimes it may occur with different data conditions. In this case, we need to stop the execution of our application and notify the end-user that something may be wrong.

Syntax of PL/SQL Raise Exception

Given below is the syntax mentioned:

DECLARE
Variable deceleration statement;
BEGIN
WHEN [specified exception name] THEN
[code to handle the exception with specified exception name] WHEN OTHERS
THEN
[code to handle all types of exception] END;

Explanation:

• In the above syntax, we use exception handling to handle all types of exceptions. Here we first used the declare section, an optional part of this syntax. After that, we use the execution section, and inside the execution section, we write the WHEN condition to handle the exception. For example, see here WHEN condition follows the specified exception name that we want.
• During the PL/SQL procedure execution, any runtime error may occur when an exception is raised. In the above syntax, the exception starts with the WHEN clause and its searches in a sequential manner. When it finds the specified exception, then it executes the respective code of that exception. If an exception is not found, then the PL/SQL engine executes the other part of syntax as shown.

How to Raise Exceptions in PL/SQL?

Given below shows how we can raise the exception in PL/SQL as follows:

Basically, there are three ways to raise the exception as follows:

1. User-Defined Exception

PL/SQL allows us to define our own exception as per the requirement of the program. In this type, we just need to declare an exception first; then, we are able to raise the exception by using the raise statement or procedure statement.

We declare a user-defined exception by using the following syntax as follows.

DECLARE
Specified exception name EXCEPTION;

Explanation:

• The declaration of exception is similar to the variable declaration, which means we need to declare the exception in the declaration section of the PL/SQL block.

2. Internally Defined Exception

This exception is system defined and internally executed at the runtime that means any runtime error may occur then an internally defined exception is raised with a specified statement.

Syntax:

RAISE specified exception_name;

Now let’s see some predefined exceptions in PL/SQL as follows:

• ACCESS_INTO_NULL: It is raised when the null value object assigns the value automatically.
• CASE_NOT_FOUND: It is raised when no option in the CASE statement is selected as well as there is no else clause.
• INVALID _NUMBER: When converting a character string to a number fails because the string does not represent a valid number, this exception is raised.

3. Current Exception

With the RAISE statement, you may re-raise the current exception. Reassigning an exception to the enclosing block allows it to be dealt with later. Therefore, you don’t need to give the exception name when re-raise an exception.

Examples of PL/SQL Raise Exception

Different examples are mentioned below:

Example #1

Let’s see the first example of a user-defined exception as follows.

Code:

DECLARE
X number(4);
Y number(4);
Z number(4);
one_divide exception;
BEGIN
X:= &X;
Y:= &Y;
If Y=1 then
raise one_divide exception;
End if;
Z:= X/Y;
dbms_output.put_line(Z);
exception
when zero_divide exception then
dbms_output.put_line(‘zero divide exception’);
when one_divide Exception then
dbms_output.put_line(‘one divide exception’);
when other then
dbms_output.put_line(sqlerrm);
END;
/

Explanation:

• By using the above example, we try to implement the user-defined exception. In this example, we implement two user-defined exceptions such as raise exception and raise_application_error. Here first, we need to declare the raise exception that we declare raise Exception as shown. In this example, we declare the three variables in the declaration section to perform the mathematical operation, such as the division of numbers. After inside the execution section, we take the value from the user and perform the division. In this example, we first check the value of Y; if the value Y is 1, then it raises the one_divide Exception.
• Now PL/SQL control goes to the exception section, and it performs the search operation when it finds the one_divide Exception, then it executes the respective statement as shown in the above program. Similarly, when it raises the zero_divide Exception, then it prints the zero_divide Exception message. The final output of the above procedure we illustrated by using the following screenshot as follows.

Output:

If we insert a long value of Y, then it shows the error message like numeric or value error.

Example #2

Now let’s see another example as follows.

set serveroutput on;

Code:

DECLARE
x int;
y int;
z int;
BEGIN
x := &x;
y := &y;
z := x/y;
dbms_output.put_line('output of above statement=' || z);
EXCEPTION
when ZERO_DIVIDE then
dbms_output.put_line('Division by 0 Exception');
END;
/

Explanation:

• In the above example, we use implement divide by zero exception. In this example, we first set serveroutput; after that, in the declaration section, we declare three different variables, x, y, and z, with integer data type as shown in the above example. In the next part of a program in the execution section, we accept the value from the user to perform the division of x and y, and the result store into the z; the stored result we display by using the procedure statement dbms_output.put_line.
• In the exception section, we write an exception for zero_divide, which means we try to perform the division by using 0 the PL/SQL control transfer to the exception section and search for the zero_divide. If control finds out the zero_divide exception, then it prints the respective message; in this example, we display the division by 0 exceptions. The final output of the above procedure we illustrated by using the following screenshot as follows.

Output:

Conclusion

From the above article, we have seen the basic syntax of the raise exception, and we also saw different examples of the raise exception. From this article, we have seen how and when we use PL/SQL to raise exceptions.

Recommended Articles

This is a guide to PL/SQL Raise Exception. Here we discuss the introduction and how to raise PL/SQL and examples, respectively. You may also have a look at the following articles to learn more –

1. Triggers in PL/SQL
2. PL/SQL Collections
3. PL/SQL Data Types
4. Loops in PL/SQL