ORA-01422 is raised when a SELECT INTO statement in PL/SQL returns more than one row. SELECT INTO can hold only one row, so Oracle raises the predefined exception TOO_MANY_ROWS. Usually the WHERE clause does not use the full key of the table. The fix is to identify exactly one row, choose one by a rule, or read all rows into a collection.
Reproduce the Error
The route DEL-BOM has two fares, one per cabin:
Example:
create table demo_fares (route_code varchar2(10), cabin varchar2(10), fare number);
insert into demo_fares values ('DEL-BOM', 'ECONOMY', 120);
insert into demo_fares values ('DEL-BOM', 'BUSINESS', 480);
commit;
declare
v_fare number;
begin
select fare into v_fare from demo_fares where route_code = 'DEL-BOM';
end;
/Output:
Table DEMO_FARES created. 1 row inserted. 1 row inserted. Commit complete. declare * ERROR at line 1: ORA-01422: exact fetch returned more than the requested number of rows ORA-06512: at line 4
Fix: One Row, or All of Them
Example:
set serveroutput on
declare
v_fare number;
type fare_tab is table of number;
v_fares fare_tab;
begin
-- the full key: one row
select fare into v_fare from demo_fares
where route_code = 'DEL-BOM' and cabin = 'ECONOMY';
dbms_output.put_line('Economy: ' || v_fare);
-- the row you want, by rule: the cheapest one
select fare into v_fare from demo_fares where route_code = 'DEL-BOM'
order by fare fetch first 1 row only;
dbms_output.put_line('Cheapest: ' || v_fare);
-- all rows, into a collection
select fare bulk collect into v_fares from demo_fares where route_code = 'DEL-BOM';
dbms_output.put_line('Fares found: ' || v_fares.count);
end;
/Output:
Economy: 120 Cheapest: 120 Fares found: 2 PL/SQL procedure successfully completed.
- The full key, route and cabin, matches one row.
- ORDER BY with FETCH FIRST 1 ROW ONLY picks one row by a rule you state, here the lowest fare.
- BULK COLLECT INTO reads every matching row into a collection.
Things to Know
- Do not add FETCH FIRST 1 ROW ONLY without ORDER BY: Oracle then returns any one of the rows, not a chosen one.
- A WHEN TOO_MANY_ROWS handler is useful when duplicates point to bad data that must be reported.
- A unique constraint on the lookup columns prevents the duplicates in the first place.
Related Guides
- How to Fix ORA-01403: No Data Found in Oracle PL/SQL
- How to Fix ORA-01427: Single-Row Subquery Returns More Than One Row in Oracle
Conclusion
ORA-01422 means SELECT INTO found several rows. Filter on the full key, pick one row with ORDER BY and FETCH FIRST, or use BULK COLLECT when every row is needed.
