Oracle Database / SQL & PL/SQL Errors
ORA-01427: investigate a scalar subquery returning several rows
A query that expects one lookup value can fail when data or joins produce multiple candidates. Verify the business uniqueness assumption.
Environment and inputs: Oracle Database 19c baseline · SQL*Plus-compatible SQL unless RMAN, DGMGRL, ADRCI or a host shell is specified. Check release, patch level, edition and container first.
Start with this diagnostic
Use the stated SQL, RMAN, DGMGRL or host shell and replace sample names with approved targets. DBA and V$ views need authorized catalog access in the correct container. Do not grant broad privileges for an example.
SELECT customer_id, COUNT(*) AS rows_per_key FROM app_owner.customer_lookup GROUP BY customer_id HAVING COUNT(*) > 1 FETCH FIRST 20 ROWS ONLY;Read the result
This example detects repeated lookup keys in an illustrative table. Replace the table and grouping columns with those used by the actual scalar subquery.
Resolve and verify
Correct the correlation or join, or resolve invalid duplicates through the data owner. Use an aggregate or multirow operator only when it matches the intended semantics.
Operational precautions
Do not add an arbitrary ROWNUM filter that silently selects the wrong business row.