PL/SQL No_data_found

The NO_DATA_FOUND exception is a predefined exception in Oracle PL/SQL that occurs when a query that is expected to return data does not retrieve any rows. This exception is part of Oracle’s built-in exception handling mechanism and is raised automatically when:

A SELECT INTO statement fails to fetch any rows.

A function returning a value fails to retrieve data.

Since NO_DATA_FOUND is an implicit exception, developers do not need to explicitly raise it; Oracle does it automatically when the conditions are met.

When Does NO_DATA_FOUND Occur?

The NO_DATA_FOUND exception primarily occurs in two scenarios:

1. SELECT INTO Statement without Results

A SELECT INTO statement is used to fetch data into a variable. If the query does not return any rows, the NO_DATA_FOUND exception is raised.

Example:

DECLARE
    v_employee_name VARCHAR2(100);
BEGIN
    SELECT employee_name INTO v_employee_name FROM employees WHERE employee_id = 99999;  -- Assuming no such ID exists
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee found with the given ID.');
END;
/

In this example, if no employee exists with employee_id = 99999, Oracle raises the NO_DATA_FOUND exception.

2. Implicit Cursor Queries

When using implicit cursors such as SELECT INTO, NO_DATA_FOUND is raised automatically. However, when using explicit cursors, developers must manually check for fetched rows.

Example with Explicit Cursor:

DECLARE
    CURSOR emp_cursor IS SELECT employee_name FROM employees WHERE employee_id = 99999;
    v_employee_name employees.employee_name%TYPE;
BEGIN
    OPEN emp_cursor;
    FETCH emp_cursor INTO v_employee_name;
    IF emp_cursor%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee found.');
    END IF;
    CLOSE emp_cursor;
END;
/

Since explicit cursors do not raise NO_DATA_FOUND automatically, handling it manually with %NOTFOUND is required.

Handling NO_DATA_FOUND Exception

Properly handling NO_DATA_FOUND ensures that the program does not terminate unexpectedly. This can be achieved using exception handling blocks.

Using EXCEPTION Block

The EXCEPTION block catches NO_DATA_FOUND and allows the program to continue executing gracefully.

Example:

DECLARE
    v_employee_name VARCHAR2(100);
BEGIN
    SELECT employee_name INTO v_employee_name FROM employees WHERE employee_id = 99999;
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No data found. Please check the employee ID.');
END;
/

Using CASE Statement in SELECT Query

One way to prevent NO_DATA_FOUND is by using NVL or CASE to return a default value.

Example:

DECLARE
    v_employee_name VARCHAR2(100);
BEGIN
    SELECT NVL((SELECT employee_name FROM employees WHERE employee_id = 99999), 'Not Found')
    INTO v_employee_name FROM dual;
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
END;
/

This ensures that if no row is found, ‘Not Found’ is returned instead of raising an exception.

Best Practices for Avoiding NO_DATA_FOUND

To minimize the occurrence of NO_DATA_FOUND, consider the following best practices:

1. Use COUNT(*) Before SELECT INTO

Before using SELECT INTO, check if the record exists to avoid exceptions.

DECLARE
    v_count NUMBER;
    v_employee_name VARCHAR2(100);
BEGIN
    SELECT COUNT(*) INTO v_count FROM employees WHERE employee_id = 99999;
    
    IF v_count > 0 THEN
        SELECT employee_name INTO v_employee_name FROM employees WHERE employee_id = 99999;
        DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
    ELSE
        DBMS_OUTPUT.PUT_LINE('No employee found.');
    END IF;
END;
/

2. Use BULK COLLECT with LIMIT Clause

When retrieving multiple rows, using BULK COLLECT prevents NO_DATA_FOUND while improving performance.

DECLARE
    TYPE emp_table IS TABLE OF employees.employee_name%TYPE;
    v_emp_list emp_table;
BEGIN
    SELECT employee_name BULK COLLECT INTO v_emp_list FROM employees WHERE department_id = 99999;
    
    IF v_emp_list.COUNT = 0 THEN
        DBMS_OUTPUT.PUT_LINE('No employees found.');
    ELSE
        FOR i IN 1..v_emp_list.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emp_list(i));
        END LOOP;
    END IF;
END;
/

3. Use Explicit Cursors

Explicit cursors provide more control over row retrieval and avoid unnecessary exceptions.

DECLARE
    CURSOR emp_cursor IS SELECT employee_name FROM employees WHERE employee_id = 99999;
    v_employee_name employees.employee_name%TYPE;
BEGIN
    OPEN emp_cursor;
    FETCH emp_cursor INTO v_employee_name;
    
    IF emp_cursor%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee found.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
    END IF;
    
    CLOSE emp_cursor;
END;
/

Conclusion

The NO_DATA_FOUND exception is a common error in Oracle PL/SQL that occurs when a SELECT INTO statement or function fails to return data. By understanding its causes and handling it effectively, developers can prevent abrupt program termination and improve database query reliability. Best practices like using COUNT(*), NVL, BULK COLLECT, and explicit cursors can help mitigate the risk of encountering NO_DATA_FOUND. Implementing proper exception handling ensures robust and error-resistant PL/SQL applications.