ORA-01427 means a subquery used where one value is expected, such as in the select list or after =, returned several rows. Oracle cannot pick one for you. It is a data problem as much as a SQL problem: the query assumed a key that is not unique. Decide which row you mean, or use an operator that works with many rows.
Reproduce the Error
Tea has two prices, valid from different dates, and the scalar subquery asks for "the" price of tea:
Example:
create table demo_prices (product varchar2(10), price number, valid_from date);
insert into demo_prices values ('Tea', 3, date '2026-01-01');
insert into demo_prices values ('Tea', 4, date '2026-06-01');
insert into demo_prices values ('Milk', 2, date '2026-01-01');
commit;
select 'Tea' as product, (select price from demo_prices where product = 'Tea') as price from dual;Output:
Table DEMO_PRICES created. 1 row inserted. 1 row inserted. 1 row inserted. Commit complete. Error starting at line : 7 In command - select 'Tea' as product, (select price from demo_prices where product = 'Tea') as price from dual Error at Command Line : 7 Column : 27 Error report - SQL Error: ORA-01427: single-row subquery returns more than one row
Fix: Pick One Row, or Accept Many
Example:
-- pick the one row you mean: the latest price
select 'Tea' as product,
(select price from demo_prices where product = 'Tea'
order by valid_from desc fetch first 1 row only) as price
from dual;
-- or compare with all of them
select product, price from demo_prices
where price in (select price from demo_prices where product = 'Tea');Output:
PRODUCT PRICE __________ ________ Tea 4 PRODUCT PRICE __________ ________ Tea 3 Tea 4
- ORDER BY with FETCH FIRST 1 ROW ONLY picks the latest price, the one the query really meant.
- IN instead of = accepts every row the subquery returns.
Other Ways
- Aggregate in the subquery, with MAX or MIN, when any one of the values will do.
- Add the missing condition, often a date or status, that makes the subquery return one row.
- Join instead of using a scalar subquery when you want one output row per match.
Things to Know
- The error can appear only for some rows, after data changes; a query that worked yesterday fails today because a second row arrived.
- A unique constraint on the subquery's key columns guarantees one row and makes the assumption explicit.
- In PL/SQL, SELECT ... INTO that returns several rows raises a different error, ORA-01422.
Related Guides
Conclusion
ORA-01427 means a subquery that should return one row returned several. Choose the row you mean with ORDER BY and FETCH FIRST, an aggregate, or a stricter condition, or switch to IN or a join when many rows are expected.
