M3-PPT

Module Overview

Course: Database Systems with Administration Technology Driven by Innovation

Institutions: FEU ALABANG, FEU DILIMAN, FEU TECH

Module 3: PL/SQL Exceptions

Focus on Handling PL/SQL Exceptions

Module 3A: Handling Exceptions

Introduction to PL/SQL Exceptions

This module introduces the concept of exceptions in PL/SQL, a procedural language extension to SQL used in Oracle databases. Exception handling is vital for creating robust applications that can gracefully recover from errors and maintain data integrity.

Intended Learning Outcomes
Key Goals:

  1. Define PL/SQL exceptions:

    • Understand what constitutes an exception and how PL/SQL differentiates between various types of exceptions.

  2. Recognize unhandled exceptions:

    • Identify circumstances where exceptions may occur and understand how unhandled exceptions may disrupt program execution.

Understanding Exceptions

Definition

An exception is an unexpected event that occurs during the execution of a program, causing a disruption in the normal flow of execution. Handling exceptions properly is crucial to maintaining the integrity of the application and preventing data loss.

Causes of Exceptions Include:

  • User Errors: Such as typing mistakes or incorrect input formats.

  • Program Logic Errors: Flaws inherent in the code logic, which might lead to incorrect output.

  • Nonexistent Resources: Issues such as requesting data from a network resource that does not exist.

Common Examples of Exceptions

  • Incorrect User Credentials: Entering an invalid username or password during login attempts.

  • Email Formatting: Forgetting to include special characters (e.g., '@') when inputting email addresses.

  • Data Entry Mistakes: Such as incorrectly entering credit card numbers or expiration dates.

  • SQL Query Issues: Including common mistakes like querying nonexistent tables or columns resulting in empty or unexpected results.

Exceptions in PL/SQL

Example Scenario

To understand exceptions in the context of PL/SQL, consider the following scenario where a country is queried based on its name:

DECLARE 
    v_country_name countries.country_name%TYPE := 'Republic of Korea';
    v_elevation countries.highest_elevation%TYPE; 
BEGIN 
    SELECT highest_elevation INTO v_elevation FROM countries WHERE country_name = v_country_name; 
    DBMS_OUTPUT.PUT_LINE(v_elevation); 
END;

In this example, if the variable v_country_name is changed to 'Korea, South' and there is no matching record in the database, an exception is raised signaling that the search found no results.

Exception Handling

What is an Exception Handler?

An exception handler is a dedicated block of code that dictates the recovery actions to take when an exception occurs. This mechanism allows developers to outline specific responses to errors.

Importance of Exception Handling

  • Protects the application by avoiding unhelpful error messages and software crashes, enhancing user experience.

  • Safeguards databases from potential data corruption and loss by ensuring appropriate actions are taken when errors occur.

  • Minimizes costs associated with handling errors through carefully defined recovery strategies, improving overall application reliability.

Example of Handling Exceptions

Below is an example of how to implement an exception handler in PL/SQL:

DECLARE 
    v_country_name countries.country_name%TYPE := 'Korea, South';
    v_elevation countries.highest_elevation%TYPE; 
BEGIN 
    SELECT highest_elevation INTO v_elevation FROM countries WHERE country_name = v_country_name; 
EXCEPTION 
    WHEN NO_DATA_FOUND THEN 
        DBMS_OUTPUT.PUT_LINE ('Country name, ' || v_country_name || ', cannot be found. Re-enter the country name using the correct spelling.');
END;

In this code, if there is an error during the query, control will transfer to the EXCEPTION section of the code, where a user-friendly message is displayed.

Types of Exceptions

PL/SQL exceptions can generally be categorized into:

  1. System Errors: Such as running out of disk space or accessing a resource that is not available.

  2. Data Errors: Issues like primary key violations during database operations.

  3. User Action Errors: These result from incorrect data input or commands entered by the user.

Guidelines for Trapping Exceptions

To effectively manage exceptions, consider the following guidelines:

  • Always include exception handlers in sections of the code where errors may potentially occur.

  • Prioritize handling specific named exceptions over general (OTHERS) handlers to provide clearer insights into issues.

  • Log important debugging information within handlers to facilitate diagnostics and debugging processes.

  • Extensively test code with a variety of erroneous inputs to ensure reliable error handling.

User-Defined Exceptions

Creation Process

User-defined exceptions can be created by:

  1. Declaring the exception in the declarative section.

  2. Explicitly raising the exception within the executable section using the RAISE statement.

  3. Handling it in the exception-handling section:

DECLARE 
    e_invalid_department EXCEPTION;
    v_name VARCHAR2(20):='Accounting';
    v_deptno NUMBER := 27; 
BEGIN 
    UPDATE departments SET department_name = v_name WHERE department_id = v_deptno;
    IF SQL%NOTFOUND THEN RAISE e_invalid_department; END IF;
EXCEPTION 
    WHEN e_invalid_department THEN 
        DBMS_OUTPUT.PUT_LINE('No such department id.');
END;

In this example, if no matching department_id is found, the custom exception e_invalid_department is raised and handled appropriately.

RAISE_APPLICATION_ERROR Procedure

The RAISE_APPLICATION_ERROR procedure allows for the customization of error messages and error numbers, enabling developers to convey specific issues back to users:

  • Syntax: RAISE_APPLICATION_ERROR (error_number, message[ , {TRUE | FALSE}]);

  • Error numbers must be within the range of -20000 to -20999 for defining user-defined errors.

Exception Logging Example

Using SQLCODE and SQLERRM to log exceptions can provide helpful diagnostics:

DECLARE 
    v_error_code NUMBER;
    v_error_message VARCHAR2(255);
BEGIN 
    ...  
EXCEPTION WHEN OTHERS THEN 
    ROLLBACK;
    v_error_code := SQLCODE;
    v_error_message := SQLERRM;
    INSERT INTO error_log(e_user, e_date, error_code, error_message) VALUES(USER, SYSDATE, v_error_code, v_error_message);
END;

This code enables the logging of error information into an error_log table for analysis.

Conclusion

Effective exception handling in PL/SQL is crucial for developing robust and user-friendly applications. It ensures smooth execution, protects data integrity, and enhances the overall quality of the database systems.