ORA-01403 no data found solution and commands

The Oracle error ORA-01403: no data found occurs when a query in a PL/SQL block does not return any rows. This error commonly appears in SELECT INTO statements where a query is expected to return exactly one row, but no rows are found.

Causes:

A SELECT INTO statement retrieves no rows.

The query is executed with conditions that do not match any records in the database.

You can handle the ORA-01403 error using exception handling or modify your query to ensure it returns valid data.

Solution 1:

Exception Handling (Using PL/SQL)

You can catch this error using an EXCEPTION block in PL/SQL.

Example:

DECLARE
  v_employee_name VARCHAR2(50);
BEGIN
  -- Select into statement that might not return any rows
  SELECT employee_name INTO v_employee_name
  FROM employees
  WHERE employee_id = 1000;
    -- Output result if found
  DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
EXCEPTION
  -- Catch the no data found error
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No employee found with the specified ID.');
END;
/

In this example, the NO_DATA_FOUND exception is handled, and instead of the script failing, it prints a message saying no employee was found.

Solution 2: 

Modify the Query (Using Aggregation or Count)

If you expect multiple rows or none, consider changing your query logic. For example, using COUNT to check for the presence of data.

Example:

DECLARE
  v_count NUMBER;
BEGIN
  -- Use count to check if any records exist
  SELECT COUNT(*)
  INTO v_count
  FROM employees
  WHERE employee_id = 1000;
  
  IF v_count > 0 THEN
    DBMS_OUTPUT.PUT_LINE('Employee exists.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('No employee found with the specified ID.');
  END IF;
END;
/

Here, instead of expecting one row, you count the records and act based on the count.

Solution 3: 

Use CURSOR (for multiple rows)

If multiple rows are expected and you want to avoid ORA-01403, you can use a cursor to fetch rows.

Example:

DECLARE
  CURSOR emp_cursor IS
    SELECT employee_name
    FROM employees
    WHERE department_id = 10;

  v_employee_name employees.employee_name%TYPE;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO v_employee_name;
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
  END LOOP;
  CLOSE emp_cursor;
END;
/

This example handles multiple rows without causing the NO_DATA_FOUND error.