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

Procedures in PL/SQL

Procedures in PL/SQL (Procedural Language/Structured Query Language) are a way to encapsulate a set of SQL and PL/SQL statements that perform a specific task. They are stored in the database and can be executed as needed, providing a modular and reusable approach to database programming. Here’s an overview of creating and using procedures in PL/SQL: Creating a Procedure To create a procedure, you use the CREATE PROCEDURE statement followed by the procedure's name, parameters (if any), and the body of the procedure, which contains the executable code. Here’s a basic syntax: CREATE [OR REPLACE] PROCEDURE procedure_name     [ (parameter_name [IN | OUT | IN OUT] datatype [, ...]) ] IS    -- Declaration of variables BEGIN    -- Executable statements EXCEPTION    -- Exception handling END procedure_name; Example Here’s a simple example of a PL/SQL procedure that inserts a new employee record into an employees table: CREATE OR REPLACE PROCEDURE add_employee (     p_emp_id IN employees.emp
Read more

Cursors in PL/SQL

In PL/SQL, cursors are a mechanism for accessing and manipulating query result sets. A cursor allows you to fetch and process rows returned by a query one at a time. There are two main types of cursors in PL/SQL: implicit cursors and explicit cursors. Implicit Cursors PL/SQL automatically creates implicit cursors for SELECT INTO statements, and for INSERT, UPDATE, DELETE, and MERGE statements. Example of Implicit Cursor DECLARE     v_employee_name employees.first_name%TYPE; BEGIN     -- This SELECT INTO statement uses an implicit cursor     SELECT first_name     INTO v_employee_name     FROM employees     WHERE employee_id = 100;     DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name); END; In this example, PL/SQL creates an implicit cursor for the SELECT INTO statement and automatically fetches the result into the v_employee_name variable. Explicit Cursors Explicit cursors give you more control over the context area. You can open a cursor, fetch rows from the result s
Read more