🧩 How to Fix ORA-01403: No Data Found in Oracle PL/SQL

🧩 Introduction:

The ORA-01403 error occurs when a SELECT INTO statement in PL/SQL returns no rows. This is a common issue when the queried data does not exist, and proper handling is essential to prevent application failures and maintain smooth execution of PL/SQL programs.

Step 1: Identify the Statement Causing the Error

Locate the SELECT INTO statement in your procedure or function:

SELECT salary INTO v_salary

FROM employees

WHERE employee_id = p_emp_id;

➡️ If p_emp_id does not exist in the employees table, ORA-01403 is triggered.

Step 2: Add Exception Handling

Use an exception block to handle cases where no data is found:

BEGIN

  SELECT salary INTO v_salary

  FROM employees

  WHERE employee_id = p_emp_id;

EXCEPTION

  WHEN NO_DATA_FOUND THEN

    v_salary := 0; -- default value or alternative logic

END;

Step 3: Use Conditional Checks

Alternatively, check if the data exists before performing the SELECT INTO:

IF EXISTS (SELECT 1 FROM employees WHERE employee_id = p_emp_id) THEN

  SELECT salary INTO v_salary

  FROM employees

  WHERE employee_id = p_emp_id;

ELSE

  v_salary := 0;

END IF;

Step 4: Example Scenario

A procedure tries to fetch an employee’s salary using an invalid ID:

EXEC update_salary(9999);

Since employee ID 9999 does not exist, ORA-01403 occurs. Adding exception handling or pre-checks ensures the procedure handles such cases gracefully.

Step 5: Preventive Measures

Validate input parameters before querying.

Implement robust exception handling in PL/SQL.

Consider using cursors with FOR loops for multi-row queries instead of SELECT INTO.

Conclusion:

ORA-01403 “No Data Found” errors indicate a missing row in a SELECT INTO query. Proper validation, conditional checks, and exception handling are key to preventing runtime errors and ensuring reliable PL/SQL execution.