ORA-01403: no data found

ORA-01403: no data found

A PL/SQL SELECT INTO returned no row, or the code read a collection element that does not exist.

Lab-verifiedReproduced and fixed on Oracle AI Database 26ai Free 23.26.3.0.0 · 2026-11-14

ORA-01403 comes from PL/SQL. SELECT ... INTO must return exactly one row; with zero rows it raises NO_DATA_FOUND. Reading an index-by table element that was never assigned raises the same error.

The ORA-06512 lines under the error point at the statement that failed. Check that line first: is it a SELECT INTO, or a collection access like v_tab(i)?

Causes and fixes

  1. SELECT INTO finds no row for the given key
  2. A CHAR column compared with a VARCHAR2 variable
  3. Reading an index-by table element that was never assigned

1SELECT INTO finds no row for the given key

The function get_emp_name looks up a name by emp_id. ID 101 returned Kim, but ID 999 does not exist, and the call failed. ORA-06512: at "LAB_A.GET_EMP_NAME", line 4 points at the SELECT INTO inside the function.

Add EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; to the function and decide what the caller shows for NULL. Here the caller used NVL(..., '(not found)') and printed 999: (not found).

Setup
Setuprun as lab_a
CREATE TABLE emp (emp_id NUMBER PRIMARY KEY, name VARCHAR2(30));
INSERT INTO emp VALUES (101, 'Kim');
INSERT INTO emp VALUES (102, 'Lee');
COMMIT;
CREATE OR REPLACE FUNCTION get_emp_name (p_id NUMBER) RETURN VARCHAR2 IS
  v_name emp.name%TYPE;
BEGIN
  SELECT name INTO v_name FROM emp WHERE emp_id = p_id;
  RETURN v_name;
END;
/
OUTPUT
Table created.


1 row created.


1 row created.


Commit complete.


Function created.
Reproducerun as lab_a
SET SERVEROUTPUT ON
BEGIN
  DBMS_OUTPUT.PUT_LINE('101: ' || get_emp_name(101));
  DBMS_OUTPUT.PUT_LINE('999: ' || get_emp_name(999));
END;
/
OUTPUT
101: Kim
BEGIN
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "LAB_A.GET_EMP_NAME", line 4
ORA-06512: at line 3
Help: https://docs.oracle.com/error-help/db/ora-01403/
Fixrun as lab_a
CREATE OR REPLACE FUNCTION get_emp_name (p_id NUMBER) RETURN VARCHAR2 IS
  v_name emp.name%TYPE;
BEGIN
  SELECT name INTO v_name FROM emp WHERE emp_id = p_id;
  RETURN v_name;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;
/
OUTPUT
Function created.

Only catch NO_DATA_FOUND where "no row" is a normal case. If the row must exist, the error is telling you the data is wrong.

After the fixrun as lab_a
SET SERVEROUTPUT ON
BEGIN
  DBMS_OUTPUT.PUT_LINE('101: ' || get_emp_name(101));
  DBMS_OUTPUT.PUT_LINE('999: ' || NVL(get_emp_name(999), '(not found)'));
END;
/
OUTPUT
101: Kim
999: (not found)

PL/SQL procedure successfully completed.

2A CHAR column compared with a VARCHAR2 variable

item_code is CHAR(10), so 'A01' is stored as [A01 ] with 7 trailing spaces (LENGTH = 10). The block compares it with v_code VARCHAR2(10) := 'A01'. With a VARCHAR2 on one side, Oracle does not pad, so the values are different and the SELECT INTO at line 5 found nothing.

Declaring the variable as item.item_code%TYPE (CHAR) makes the comparison match, and the block printed A01: Bolt.

Setup
Setuprun as lab_a
CREATE TABLE item (item_code CHAR(10) PRIMARY KEY, item_name VARCHAR2(30));
INSERT INTO item VALUES ('A01', 'Bolt');
INSERT INTO item VALUES ('A02', 'Nut');
COMMIT;
OUTPUT
Table created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
SET SERVEROUTPUT ON
DECLARE
  v_code VARCHAR2(10) := 'A01';
  v_name item.item_name%TYPE;
BEGIN
  SELECT item_name INTO v_name FROM item WHERE item_code = v_code;
  DBMS_OUTPUT.PUT_LINE(v_code || ': ' || v_name);
END;
/
OUTPUT
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 5
Help: https://docs.oracle.com/error-help/db/ora-01403/
Checkrun as lab_a
SELECT '[' || item_code || ']' AS stored, LENGTH(item_code) AS len FROM item ORDER BY item_code;
OUTPUT
STORED              LEN
------------ ----------
[A01       ]         10
[A02       ]         10

2 rows selected.

This happens a lot with old tables that use CHAR codes. Check the column type with the [ ] query above when a key that clearly exists is "not found".

After the fixrun as lab_a
SET SERVEROUTPUT ON
DECLARE
  v_code item.item_code%TYPE := 'A01';
  v_name item.item_name%TYPE;
BEGIN
  SELECT item_name INTO v_name FROM item WHERE item_code = v_code;
  DBMS_OUTPUT.PUT_LINE(RTRIM(v_code) || ': ' || v_name);
END;
/
OUTPUT
A01: Bolt

PL/SQL procedure successfully completed.

3Reading an index-by table element that was never assigned

Salaries are loaded into v_sal(emp_id) only for active employees. The second loop reads every employee, and employee 103 is inactive (active = 'N'), so v_sal(103) does not exist. 101 and 102 printed, then the block failed at line 9.

Check with v_sal.EXISTS(r.emp_id) before reading. The block then printed 103: (not loaded).

Setup
Setuprun as lab_a
CREATE TABLE emp_sal (emp_id NUMBER PRIMARY KEY, salary NUMBER, active CHAR(1));
INSERT INTO emp_sal VALUES (101, 5000, 'Y');
INSERT INTO emp_sal VALUES (102, 4200, 'Y');
INSERT INTO emp_sal VALUES (103, 3900, 'N');
COMMIT;
OUTPUT
Table created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
SET SERVEROUTPUT ON
DECLARE
  TYPE t_sal IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  v_sal t_sal;
BEGIN
  FOR r IN (SELECT emp_id, salary FROM emp_sal WHERE active = 'Y') LOOP
    v_sal(r.emp_id) := r.salary;
  END LOOP;
  FOR r IN (SELECT emp_id FROM emp_sal ORDER BY emp_id) LOOP
    DBMS_OUTPUT.PUT_LINE(r.emp_id || ': ' || v_sal(r.emp_id));
  END LOOP;
END;
/
OUTPUT
101: 5000
102: 4200
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 9
ORA-06512: at line 9
Help: https://docs.oracle.com/error-help/db/ora-01403/
Checkrun as lab_a
SELECT emp_id, active FROM emp_sal ORDER BY emp_id;
OUTPUT
    EMP_ID A
---------- -
       101 Y
       102 Y
       103 N

3 rows selected.

There is no SELECT in the failing line, which is why this one is confusing. If ORA-06512 points at a line with v_tab(...), suspect a missing index.

After the fixrun as lab_a
SET SERVEROUTPUT ON
DECLARE
  TYPE t_sal IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  v_sal t_sal;
BEGIN
  FOR r IN (SELECT emp_id, salary FROM emp_sal WHERE active = 'Y') LOOP
    v_sal(r.emp_id) := r.salary;
  END LOOP;
  FOR r IN (SELECT emp_id FROM emp_sal ORDER BY emp_id) LOOP
    IF v_sal.EXISTS(r.emp_id) THEN
      DBMS_OUTPUT.PUT_LINE(r.emp_id || ': ' || v_sal(r.emp_id));
    ELSE
      DBMS_OUTPUT.PUT_LINE(r.emp_id || ': (not loaded)');
    END IF;
  END LOOP;
END;
/
OUTPUT
101: 5000
102: 4200
103: (not loaded)

PL/SQL procedure successfully completed.

FAQ

How do I handle no data found in a PL/SQL SELECT INTO?

Add EXCEPTION WHEN NO_DATA_FOUND THEN ... to the block or function and return a value the caller can check. In this lab the function returned NULL.

Why do I get ORA-01403 when the row clearly exists?

Check the column type. A CHAR(10) code compared with a VARCHAR2 variable does not match because of the trailing spaces. Use table.column%TYPE for the variable.

Why does ORA-01403 occur on a line with no SELECT?

Reading an index-by table element that does not exist raises the same error. Use .EXISTS(i) before reading it.

How do I find which statement raised ORA-01403?

Read the ORA-06512 lines. The first one is the deepest: here "LAB_A.GET_EMP_NAME", line 4, then the caller's line.

How we verify

Each scenario starts from fresh database users. A script runs the setup, confirms the exact error code, applies the fix and runs the statement again. The output shown is what the database printed, saved by the script.

lab_a and lab_b are ordinary users created for the test; system is the DBA account. Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free

Related errors