ORA-01422: exact fetch returns more than requested number of rows

Message:
ORA-01422: exact fetch returns more than requested number of rows

Cause:
The number specified in exact fetch is less than the rows returned.

Action:
Rewrite the query or change number of rows requested.

Explanation and Troubleshooting Tips:
An exact fetch occurs when an SQL query is run which returns results directly into bind variables (rather than the results being enumerated via a cursor, loop, bulk collect, etc).
Because the result is bound directly to a variable, it is only possible for the value from a single row to be returned into the variable.
If the query returns more than one row, then the predefined exception TOO_MANY_ROWS is raised, and if that exception is not caught and handled in the code, then you will see an ORA-01422: exact fetch… error.

In the following example, if a matching record for some_key is not present, then the exception will be raised.

Depending on the circumstances, you may wish to catch the exception and act on it – such as providing a default value, or executing different code.

Provided the exception is caught and not re-raised, then an ORA-01403 error will not be thrown.

Warning:
Beware that when catching the TOO_MANY_ROWS exception, the value of the bind variables will be left in an undetermined state.
Consider the following code:

In the example above, if more than one row matches the predicate, then the returned value is undetermined.
Two possibilities exist:

  1. l_result is set to the value of some_key from the first row retrieved, and then the exception is raised.
    The function returns this value.
  2. The exception is raised before l_result is set to a value.
    The function returns 0 – i.e. the default value the l_result was initialised with.

Which of the two actually happens may depend on the complexity of the query, the chosen execution plan, or other factors outside your control.

It is also important to note here in this example that in the first possibility, since no ORDER BY clause is specified in the query, if the value of the first row is returned, we do not even know which of the rows that will be.

If writing code that catches a TOO_MANY_ROWS you should ensure that the value of any bind variables that could be used in the code that follows are set to a specific value, or that the exception is allowed to propagate to code where those bind variables are out of scope.

See also:

Posted in Explained, ORA codes, Troubleshooting Tips

Contribute further information: