Handling NO_DATA_FOUND exception in PL/SQL

PL/SQL Exception Handling: Exercise-2 with Solution

Handle the NO_DATA_FOUND exception when retrieving a row from a table and no matching record is found.

Sample Solution:

PL/SQL Code:

-- PL/SQL block to handle the NO_DATA_FOUND exception
DECLARE
   emp_name   VARCHAR2(100);
emp_id     NUMBER := 225;
BEGIN
   BEGIN
      SELECT first_name INTO emp_name FROM employees WHERE employee_id = emp_id;
      DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_name);
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         DBMS_OUTPUT.PUT_LINE('Error: No matching record found');
   END;
END;

Sample Output:

Error: No matching record found

Explanation:

The said code in Oracle's PL/SQL when executes that displays the error message 'no matching record was found' if there are NO_DATA_FOUND exception is raised.

The two variables emp_name of type VARCHAR2 that stores the first name and emp_id of type NUMBER that stores the employee ID for which retrieves the record.

A nested BEGIN-END block try to catch the NO_DATA_FOUND exception when the SELECT statement does not find any matching record in the employees table against the given employee_id.

The EXCEPTION block immediately follows the SELECT statement and detect the exception, which is NO_DATA_FOUND.

Inside the EXCEPTION block, the DBMS_OUTPUT.PUT_LINE procedure displays an error message that is 'no matching record was found'.

Flowchart:

Previous: Handling division by zero exception in PL/SQL.
Next: Handling TOO_MANY_ROWS Exception in PL/SQL.