ORA-01403 is raised when a SELECT INTO statement in PL/SQL returns no row. SELECT INTO must fill its variables with exactly one row, so an empty result is an error, raised as the predefined exception NO_DATA_FOUND. The fix depends on whether a missing row is normal: handle the exception where it can happen, or write the query so that it always returns one row.
Reproduce the Error
Example:
create table demo_fares (route_code varchar2(10) primary key, fare number);
insert into demo_fares values ('DEL-BOM', 120);
insert into demo_fares values ('DEL-DXB', 310);
commit;
set serveroutput on
declare
v_fare demo_fares.fare%type;
begin
select fare into v_fare from demo_fares where route_code = 'DEL-LHR';
dbms_output.put_line('Fare: ' || v_fare);
end;
/Output:
Table DEMO_FARES created. 1 row inserted. 1 row inserted. Commit complete. declare * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 4
Line 4 of the block is the SELECT INTO; no route DEL-LHR exists.
Fix: Handle the Missing Row
Example:
set serveroutput on
-- handle the missing row where it can happen
declare
v_fare demo_fares.fare%type;
begin
begin
select fare into v_fare from demo_fares where route_code = 'DEL-LHR';
exception
when no_data_found then
v_fare := null;
end;
dbms_output.put_line('Fare: ' || nvl(to_char(v_fare), 'not on sale'));
end;
/
-- or use an aggregate, which always returns one row
declare
v_fare demo_fares.fare%type;
begin
select max(fare) into v_fare from demo_fares where route_code = 'DEL-LHR';
dbms_output.put_line('Fare: ' || nvl(to_char(v_fare), 'not on sale'));
end;
/Output:
Fare: not on sale PL/SQL procedure successfully completed. Fare: not on sale PL/SQL procedure successfully completed.
- The first block wraps the SELECT INTO in its own BEGIN ... EXCEPTION ... END, so only that query's missing row is handled, and the code continues.
- The second block uses MAX: an aggregate without GROUP BY always returns one row, NULL when nothing matches.
NO_DATA_FOUND in Functions Called from SQL
When a function raises NO_DATA_FOUND inside a SQL statement, SQL treats it as no value instead of an error:
Example:
-- a function that raises NO_DATA_FOUND, called from SQL
create or replace function demo_fare_of (p_route varchar2) return number is
v_fare number;
begin
select fare into v_fare from demo_fares where route_code = p_route;
return v_fare;
end;
/
select demo_fare_of('DEL-LHR') as fare from dual;Output:
Function DEMO_FARE_OF compiled FARE _______
The query returns NULL without any error, which can hide a real problem. Handle the exception inside such functions and return a deliberate value, or raise a different error.
Things to Know
- Reading an associative array element that does not exist also raises ORA-01403.
- A cursor FETCH that finds no more rows does not raise it; it sets %NOTFOUND instead.
- Avoid WHEN OTHERS THEN NULL as a fix: it hides every other error too.
Related Guides
Conclusion
ORA-01403 means a SELECT INTO found no row. Decide whether that is normal; if it is, handle NO_DATA_FOUND around that query or use an aggregate that always returns a row, and if not, let the error reach the caller with a clear message.
See my articles more often when you search Google.
