What causes ORA-01422?

The WHERE clause is not selective enough to return a single row. Data that was supposed to be unique has duplicates. A lookup table has grown to contain multiple rows for a key that used to be unique. JOIN condition in the query returns a Cartesian product

How do I fix ORA-01422?

Step 1: Add a ROWNUM = 1 or FETCH FIRST 1 ROW ONLY to limit to one row if the first match is sufficient.. Step 2: Investigate why multiple rows match — check for duplicates in the source data.. Step 3: Use a cursor loop instead of SELECT INTO if multiple rows are valid.. Step 4: Add a UNIQUE constraint to prevent duplicates if this should never happen.. Step 5: Handle the TOO_MANY_ROWS exception in PL/SQL: `EXCEPTION WHEN TOO_MANY_ROWS THEN ;`.

Medium severitydata

Oracle Database Error:
ORA-01422

What does this error mean?

A SELECT INTO statement in PL/SQL returned more than one row when exactly one row was expected.

Common causes

1The WHERE clause is not selective enough to return a single row
2Data that was supposed to be unique has duplicates
3A lookup table has grown to contain multiple rows for a key that used to be unique
4JOIN condition in the query returns a Cartesian product

How to fix it

1Step 1: Add a ROWNUM = 1 or FETCH FIRST 1 ROW ONLY to limit to one row if the first match is sufficient.
2Step 2: Investigate why multiple rows match — check for duplicates in the source data.
3Step 3: Use a cursor loop instead of SELECT INTO if multiple rows are valid.
4Step 4: Add a UNIQUE constraint to prevent duplicates if this should never happen.
5Step 5: Handle the TOO_MANY_ROWS exception in PL/SQL: `EXCEPTION WHEN TOO_MANY_ROWS THEN <handle>;`.

Example log output

ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SCHEMA.ENRICHMENT_PROC", line 38

Frequently asked questions

What is the difference between ORA-01403 and ORA-01422?

ORA-01403 means no rows were found; ORA-01422 means too many rows were found. Both occur when a PL/SQL SELECT INTO does not return exactly one row.

Source · docs.oracle.com/error-help/db/ora-01422