HOW TO USE EXCEPTIONS IN ORACLE?

 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!

HOW TO USE WITH IN ORACLE?

 The WITH clause creates a temporary table, or it can be seen as an inline or embedded view. Its purpose is to reduce the number of times a particular query is called in a Select statement. The end result is to improve performance significantly. 

In Oracle PL/SQL, the WITH clause creates a temporary result set called a Common Table Expression (CTE). It's beneficial when you want to break down a complex query into more manageable parts, improve query readability, and avoid writing subqueries multiple times. Here's how to use the WITH clause in Oracle PL/SQL:

WITH cte_name (column1, column2, ...) AS ( 
    SELECT ... 
    FROM ... 
    WHERE ... 
)
SELECT * 
FROM cte_name;

Let's break down this structure:

• cte_name: This is the name you give to your Common Table Expression.

• (column1, column2, ...): These are the columns you define for your CTE. They don't need to be the same as the columns in the underlying tables; you can transform data in the CTE.

• 
  

• SELECT ... FROM ... WHERE ...: This is the query that defines your CTE. You can use any valid SQL statements here.

• 
  

• SELECT * FROM cte_name;: Finally, you use the CTE in the main query, treating it like a table.

Here's an example:

Suppose you have a table named employees with columns employee_id, first_name, last_name, and salary. You want to retrieve employees with a salary greater than the average salary.

WITH avg_salary AS ( 
    SELECT AVG(salary) AS average_salary 
    FROM employees 
) 
SELECT employee_id, first_name, last_name, salary 
FROM employees 
WHERE salary > (SELECT average_salary FROM avg_salary);

In this example, the CTE named avg_salary calculates the average salary from the employees table. The main query then selects employees with salaries greater than the calculated average salary from the CTE.

Remember that CTEs are only available within the context of the query where they are defined. They can't be referenced outside of that query. This makes them a great tool for simplifying complex queries while maintaining readability.

Hope you have a nice day!!!