Exceptions in PL/SQL

In PL/SQL, exceptions are used to handle errors and other exceptional events that occur during the execution of a program. PL/SQL provides predefined exceptions and allows the creation of user-defined exceptions.

Predefined Exceptions

Some common predefined exceptions include:
  • NO_DATA_FOUND: Raised when a SELECT INTO statement returns no rows.
  • TOO_MANY_ROWS: Raised when a SELECT INTO statement returns more than one row.
  • ZERO_DIVIDE: Raised when an attempt is made to divide a number by zero.
  • DUP_VAL_ON_INDEX: Raised when a duplicate value is tried to be inserted into a unique column.

User-Defined Exceptions

You can define your own exceptions using the EXCEPTION keyword and handle them using RAISE.
Syntax

Here's the basic syntax for exception handling in PL/SQL:

BEGIN
   -- Executable statements
EXCEPTION
   WHEN exception1 THEN
      -- Exception handling statements for exception1
   WHEN exception2 THEN
      -- Exception handling statements for exception2
   WHEN OTHERS THEN
      -- Statements for handling all other exceptions
END;


Example

Below is an example demonstrating the use of predefined and user-defined exceptions:

DECLARE
   v_num1 NUMBER := 10;
   v_num2 NUMBER := 0;
   v_result NUMBER;
   e_divide_by_zero EXCEPTION; -- User-defined exception
BEGIN
   BEGIN
      v_result := v_num1 / v_num2;
   EXCEPTION
      WHEN ZERO_DIVIDE THEN
         DBMS_OUTPUT.PUT_LINE('Cannot divide by zero');
         RAISE e_divide_by_zero; -- Raise the user-defined exception
   END;
EXCEPTION
   WHEN e_divide_by_zero THEN
      DBMS_OUTPUT.PUT_LINE('User-defined exception: Division by zero');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('An unexpected error occurred');
END;

Tips for Handling Exceptions
  • Order of Handling: Specific exceptions should be handled before the OTHERS exception to ensure specific errors are caught first.
  • Logging: Use logging mechanisms (such as DBMS_OUTPUT.PUT_LINE or writing to a log table) to record information about exceptions for debugging purposes.
  • Rethrowing Exceptions: Sometimes, after handling an exception, you might want to rethrow it to the calling program. You can do this using the RAISE statement.
Exception handling in PL/SQL is a powerful feature that allows developers to handle errors gracefully, ensuring robust and reliable applications. By understanding and properly implementing exception handling, you can create PL/SQL programs that are easier to debug and maintain

Example 1: Handling NO_DATA_FOUND
DECLARE
  v_name VARCHAR2(50);
BEGIN
  SELECT name INTO v_name FROM employees WHERE employee_id = 999;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No employee found with ID 999');
END;


Example 2: Handling TOO_MANY_ROWS
DECLARE
    v_name VARCHAR2(50);
BEGIN
    SELECT name INTO v_name FROM employees WHERE department_id = 10;
EXCEPTION
    WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('More than one employee found in department 10');
END;

Comments

Post a Comment

Popular posts from this blog

KTU DBMS LAB CSL 333 BTech S5 - Dr Binu V P

 About Me About the course Syllabus and Experiments **************************************** Introduction to RDBMS Working with MySQL ER Diagrams-MySQL Workbench Introduction to SQL Data types in MySQL Built-in Functions MySQL Aggregate Functions Enforcing rules using constraints MySQL -Lab Exercise ****************************************** Joining tables Order by, group by and having clause Nested Queries MySQL -Lab Exercise ******************************************* Insert Data from a CSV file Set Operations Queries using temporary tables TCL commands in MySQL DCL commands in MySQL Views and Assertions Library Database- Lab Exercise ********************************************* PL/SQL and Stored Procedures IF statement in PL/SQL CASE statement in PL/SQL Loops in PL/SQL Procedures in PL/SQL Functions in PL/SQL Triggers in PL/SQL Cursors in PL/SQL Packages in PL/SQL Exception Handling in PL/SQL Backing up and Restoring Databases Query Analysis using Query Plan ***********************
Read more

KTU DBMS LAB CSL 333 Syllabus and Experiments

 Syllabus 1. Design a database schema for an application with ER diagram from a problem description **. 2. Creation, modification, configuration, and deletion of databases using UI and SQL Commands **. 3. Creation of database schema - DDL (create tables, set constraints, enforce relationships, create indices, delete and modify tables). Export ER diagram from the database and verify relationships** (with the ER diagram designed in step 1). 4. Database initialization - Data insert, Data import to a database (bulk import using UI and SQL Commands)**. 5. Practice SQL commands for DML (insertion, updating, altering, deletion of data, and viewing/querying records based on condition in databases)**. 6. Implementation of built-in functions in RDBMS**. 7. Implementation of various aggregate functions in SQL**. 8. Implementation of Order By, Group By & Having clause **. 9. Implementation of set operators nested queries, and join queries **. 10. Implementation of queries using temp tables. 11
Read more

Creating a Database and Table ,executing queries - MySQL Lab Exercise

Create a database school and use it CREATE DATABASE school; USE school; Let's create a simple table schema for a students table in MySQL, followed by a set of queries to interact with the data. id : An integer that uniquely identifies each student (primary key). first_name : The student's first name. last_name : The student's last name. birthdate : The student's birth date. email : The student's email address. gender : The student's gender. enrollment_date : The date when the student was enrolled. CREATE TABLE students (     id INT AUTO_INCREMENT PRIMARY KEY,     first_name VARCHAR(50) NOT NULL,     last_name VARCHAR(50) NOT NULL,     birthdate DATE NOT NULL,     email VARCHAR(100) NOT NULL,     gender ENUM('Male', 'Female', 'Other') NOT NULL,     enrollment_date DATE NOT NULL ); check the schema desc students; Inserting Sample Data INSERT INTO students (first_name, last_name, birthdate, email, gender, enrollment_date) VALUES ('John&
Read more