Handling exceptions is one of the most basic and essential points to learn in PLSQL and in general, with all programming languages. We, as programmers, want to tell the code what to do in case the end user misuses something or doesn't follow the proper set of instructions to do something. We also want to tell the user to do it correctly when this happens so the user doesn't see the error and instructions to avoid it in the future.
I will not list down all the exceptions here, but I will put the most commonly used when coding with PLSQL. These are the following,
1. NO_DATA_FOUND: this handles situations when the select statement cannot find any data, with a given set of WHERE clauses.
2. TOO_MANY_ROWS: this is to handle situations when the select statement retrieves more than one row when only one was expected.
3. CASE_NOT_FOUND: this exception is to handle a CASE WHEN statement in a select statement that does not have an ELSE in it, resulting in not having a default value in case the first WHEN condition is not met.
4. OTHERS: this exception handles all kinds of exceptions, including the previous ones. This is great if you combine it with logging the SQL error message by calling "sqlerrm" This last command tells Oracle to print the error message, whatever that is.
Another critical point is to know how and where to place the exceptions. These exceptions are always inside "begin and end" blocks. It is beneficial to use these blocks because if the select statement is without one and gives an error, it will prevent the rest of the code from being executed.
To show how to use the exceptions, I can place the following examples,
The query above tries to get the corporate account number from the creditplans table corresponding to the given creditplanNo and store it into a local variable with the INTO statement. When the corporate account is not found, we handle the exception with NO_DATA_FOUND, and we tell Oracle to exit from the code by using the RETURN instruction with the message that no corporate account was found for that credit plan.
The query above tries to get a barcode from the skuxref table based on a parameter p_entry and within a range of dates. It jumps to the WHEN OTHERS THEN exception when it doesn't find it. When doing it, we are telling Oracle to assign the variable t_barcode as null instead of crashing completely, to print the error with the procedure log_output, which is user procedure using a single table to log the errors, and to further continue with the code without crashing.
In Oracle PL/SQL, exceptions are a mechanism to handle errors and exceptional conditions that might occur during the execution of a program. They provide a structured way to gracefully handle unexpected situations and prevent the program from crashing. Here's how you can explain the use of exceptions in Oracle PL/SQL with examples:
1. Defining Exceptions:
In PL/SQL, you can define exceptions using the DECLARE block. This allows you to name and categorize errors in your code.
DECLARE
custom_exception EXCEPTION;
PRAGMA EXCEPTION_INIT(custom_exception, -20001);
BEGIN
-- Your code here
EXCEPTION
WHEN custom_exception THEN
-- Handling code for custom_exception
END;
2. Handling Exceptions:
You can use EXCEPTION blocks to catch and handle specific exceptions that might occur during the execution of your PL/SQL code.
BEGIN
-- Your code here
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- Handling code for no data found
WHEN OTHERS THEN
-- Handling code for other exceptions
END;
3. RAISE_APPLICATION_ERROR:
This is a helpful command to raise custom exceptions with user-defined error messages. This can provide more informative error messages to users or developers.
BEGIN
IF some_condition THEN
RAISE_APPLICATION_ERROR(-20001, 'Custom error message');
END IF;
EXCEPTION
WHEN OTHERS THEN
-- Handling code for other exceptions
END;
4. Example:
Here's an example of using exceptions to handle division by zero:
DECLARE
numerator NUMBER := 10;
denominator NUMBER := 0;
result NUMBER;
BEGIN
-- Handling division by zero exception
BEGIN
result := numerator / denominator;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Error: Division by zero');
END;
END;
In this example, an exception block is used to catch the ZERO_DIVIDE exception, which occurs when attempting to divide by zero. Instead of crashing the program, the code within the exception block displays an error message.
In summary, Oracle PL/SQL exceptions are a crucial tool for managing errors and exceptional situations in your code. They allow you to gracefully handle errors, provide informative messages, and ensure your program doesn't crash unexpectedly.
Hope you all have a wonderful day!!! Stay safe!
