How to resolve ORA-01422 Error Message in Oracle database

Learn the reason and how to resolve the ORA-01422 error message in Oracle.

Description

When you stumble upon an ORA-01422 error, the following error message will appear:

ORA-01422: actual fetch returns extra than requested range of rows

Cause

You tried to execute a SELECT INTO announcement and greater than one row was returned.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Rewrite your SELECT INTO declaration so that solely one row is returned.

Option #2

Replace your SELECT INTO statement with a cursor.

For example, if you tried to execute the following SQL statement:

SELECT supplier_id
INTO cnumber
FROM suppliers
WHERE supplier_name = 'IBM';

And there was more than one record in the suppliers table with the supplier_name of IBM, you would obtain the ORA-01422 error message.

In this case, it would possibly be greater prudent to create a cursor and retrieve each row if you are not sure of how many information you may retrieve.