Oracle ORA-01403 Error: Causes and Solutions Complete Guide

ORA-01403: No Data Found — What Every Oracle Developer Must Know

ORA-01403 is one of the most common exceptions encountered in Oracle PL/SQL development. It is raised when a SELECT INTO statement returns no rows, because PL/SQL expects exactly one row to be assigned to the target variables. Unlike a plain SQL query that simply returns zero rows, PL/SQL treats the absence of data as an exceptional condition that must be explicitly handled.

---

Top 3 Causes

1. SELECT INTO Returns Zero Rows

The most frequent cause: the WHERE clause filters out all rows, leaving nothing to assign.

DECLARE
  v_name VARCHAR2(100);
BEGIN
  -- This will raise ORA-01403 if employee 9999 doesn't exist
  SELECT emp_name
    INTO v_name
    FROM employees
   WHERE employee_id = 9999;

  DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
END;
/

Output: ORA-01403: no data found

2. Dynamic or User-Driven Conditions With No Matching Data

When queries depend on runtime parameters (user input, date ranges, status codes), certain combinations may yield no results, causing unexpected failures in production even when the code worked fine in testing.

DECLARE
  v_total_sales NUMBER;
  v_dept_id     NUMBER := &department_id; -- runtime input
  v_year        NUMBER := &sales_year;
BEGIN
  -- Fails with ORA-01403 if no sales exist for that dept/year
  SELECT SUM(sale_amount)
    INTO v_total_sales
    FROM sales
   WHERE department_id = v_dept_id
     AND EXTRACT(YEAR FROM sale_date) = v_year;

  DBMS_OUTPUT.PUT_LINE('Total Sales: ' || v_total_sales);
END;
/

Note: SUM() actually returns NULL (not ORA-01403) when no rows match. The risk is higher with non-aggregate SELECT INTO.

3. Missing EXCEPTION Handler in Stored Procedures or Packages

When ORA-01403 is not caught inside a procedure, it propagates up the call stack and can roll back entire transactions — a serious issue in batch jobs and multi-step processes.

-- Dangerous: No exception handling
CREATE OR REPLACE PROCEDURE get_employee_info(p_id IN NUMBER) AS
  v_name   VARCHAR2(100);
  v_salary NUMBER;
BEGIN
  SELECT emp_name, salary
    INTO v_name, v_salary
    FROM employees
   WHERE employee_id = p_id;
  -- If p_id doesn't exist, unhandled ORA-01403 crashes the caller
END;
/

---

Quick Fix Solutions

Fix 1: Add a NO_DATA_FOUND Exception Handler

CREATE OR REPLACE PROCEDURE get_employee_info(p_id IN NUMBER) AS
  v_name   VARCHAR2(100);
  v_salary NUMBER;
BEGIN
  SELECT emp_name, salary
    INTO v_name, v_salary
    FROM employees
   WHERE employee_id = p_id;

  DBMS_OUTPUT.PUT_LINE('Name: ' || v_name || ' | Salary: ' || v_salary);

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No employee found for ID: ' || p_id);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
    RAISE;
END;
/

Fix 2: Use an Explicit Cursor to Avoid the Exception Entirely

DECLARE
  CURSOR cur_emp (p_id NUMBER) IS
    SELECT emp_name, salary FROM employees WHERE employee_id = p_id;
  v_name   VARCHAR2(100);
  v_salary NUMBER;
BEGIN
  OPEN cur_emp(9999);
  FETCH cur_emp INTO v_name, v_salary;

  IF cur_emp%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
  END IF;

  CLOSE cur_emp;
END;
/

Fix 3: Use Aggregate Functions to Return NULL Instead of Error

DECLARE
  v_salary NUMBER;
BEGIN
  -- MAX() returns NULL when no rows match — never raises ORA-01403
  SELECT NVL(MAX(salary), 0)
    INTO v_salary
    FROM employees
   WHERE employee_id = 9999;

  DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary); -- Outputs: 0
END;
/

---

Prevention Tips

• Establish a team coding standard: Every SELECT INTO statement must be accompanied by a NO_DATA_FOUND exception handler. Add this as a mandatory item in your code review checklist.
• Prefer cursors or aggregate functions when data existence is not guaranteed. Use the NVL(MAX(col), default) pattern for safe single-value lookups without needing extra exception handling code.
• Write unit tests that deliberately pass non-existent keys to all procedures, ensuring your exception handling is verified before deployment.

---

Related Errors

Error Code	Description
ORA-01422	SELECT INTO returns more than one row — the opposite of ORA-01403
ORA-06512	Stack trace error that appears alongside ORA-01403, showing the exact line number
ORA-01001	Invalid cursor — often appears in cursor-related code near ORA-01403 scenarios

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.