ORA-01403: no data found
A SELECT INTO in PL/SQL returned no rows, or code read an associative array element that doesn't exist. Handle NO_DATA_FOUND or query in a way that allows zero rows.
- Error code
ORA-01403- Category
- PL/SQL
What it means
In PL/SQL, SELECT ... INTO must return exactly one row. When it returns none, Oracle raises the predefined exception NO_DATA_FOUND (ORA-01403). Reading an element of an associative array that was never assigned raises it too. In plain SQL a query with no rows is not an error.
Common causes
- A
SELECT ... INTOfor a key that doesn't exist. - A filter that excludes the row, such as a wrong status or date.
- Reading
my_array(i)for an index that was never set.
How to fix it
-
Handle the case explicitly.
BEGIN SELECT email INTO l_email FROM customers WHERE id = p_id; EXCEPTION WHEN NO_DATA_FOUND THEN l_email := NULL; END; -
Or use an aggregate, which always returns one row.
SELECT MAX(email) INTO l_email FROM customers WHERE id = p_id;
-
Check
my_array.EXISTS(i)before reading an associative array element.
Related errors
ORA-01422exact fetch returns more than requested rowsORA-06512at line (PL/SQL error stack)ORA-06503PL/SQL: function returned without value
More pl/sql errors
ORA-06502PL/SQL: numeric or value errorORA-06550PL/SQL compilation error at line, columnORA-06508PL/SQL: could not find program unit being calledORA-04091table is mutating, trigger may not see itORA-04088error during execution of triggerORA-04068existing state of packages has been discarded
The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.
Work on Oracle in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for Oracle and the other databases you use.