ORA-01403: no data found
ORA-01403: no data foundA 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
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
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;
/Table created.
1 row created.
1 row created.
Commit complete.
Function created.SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE('101: ' || get_emp_name(101));
DBMS_OUTPUT.PUT_LINE('999: ' || get_emp_name(999));
END;
/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/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;
/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.
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;
/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
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;Table created.
1 row created.
1 row created.
Commit complete.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;
/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/SELECT '[' || item_code || ']' AS stored, LENGTH(item_code) AS len FROM item ORDER BY item_code;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".
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;
/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
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;Table created.
1 row created.
1 row created.
1 row created.
Commit complete.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;
/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/SELECT emp_id, active FROM emp_sal ORDER BY emp_id; 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.
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;
/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