ORA 1422, 1427, 2112 (query returns two many rows)

Three error codes, which all mean the same thing: a query which syntactically must return a single row returns multiple rows.

In a 3GL program, a SELECT ... INTO ... must return just one row. If a PL/SQL routine returns multiple rows, a 1422 is returned. The 2112 is returned from a precompilier program. The 1427 is returned when a subquery must return one row, and it does not. For example:

SELECT ....
WHERE column_name = (some subquery).

This error is easy to verify: just run the query. If it returns more than one row there is nothing more to investigate. One situation where it may be tricky to identfy is a PL/SQL program with this construct:

SELECT ... INTO ... FROM ...
WHERE column_name = variable_name

and the column_name and the variable_name are identical. For example:

DECLARE
this_column number;
BEGIN
SELECT COUNT(*) INTO KOUNT FROM SOME_TABLE
WHERE this_column = this_column;

This is ambiguous: does the right hand side represent SOME_TABLE.THIS_COLUMN,
or the variable? PL/SQL takes it as the column name, and all rows get returned.

The 1427 can also arise in an UPDATE statement:

UPDATE ....
SET column_name = (some subquery)

If the subquery returns two or more rows, a 1427 results.

What to do:

Knowing what is wrong is easy; fixing is another matter. In theory, you have misstated your query; just add additional predicates to make it return a single row. Problems arise when there is bad data (the query should return just one row, but it doesn't) or when you expect multiple rows, but really don't care. The first case is simple: just delete the duplicate data and add enough constraints to the application so that it can't happen again. Where the designer decided to have the application enforce constraints (because it is too much work for the poor database to enforce uniqueness) this may be difficult, and things may devolve into the second case.

In the second case, you really can get multiple rows back; you just thought (possibly because of incomplete test cases) that you would just get one row back. As an example, you are given an e-mail address, and need to decide whether to go to the new account screen, or bring up an existing account. Normally, there is just one account, but a few people may have multiple accounts. If you need to know every account you have no choice but to replace the singleton select with a cursor. But, you may only need to know if there are multiple accounts; you do one thing if there are no accounts, and something else if there is one or more accounts, and you do the same thing regardless of the value of the accounts.

The easiest trick is the old SELECT count (*). If this can be satisfied by an index, and you don't care what the account value is, then this is probably the best approach. If you have to do a full table scan, this is less desirable. If you do need the value, then you will have to do a second query to get the value, and this simply does not work.

The best approach, if you need a value from a query, and you don't care what the value is, is to add

AND ROWNUM = 1

to your WHERE clause. If there are multiple values, this returns the first one found (and does it as quickly as possible). If there is one value, you get it. And, if there are no values, it raises the NO_DATA_FOUND exception, which is what your code was already expecting (you were prepared for this exception, were you not?) In particular, this avoids any sort which would be produced if the DISTINCT operator were used instead, and it works if there are different values present.

One trick which is more trouble than it is worth to use the MAX or MIN function to make all values
the same. This may introduce a sort, and will always return a row, so you will have to modify your
error checking.