ORA-01422

A vast majority of Oracle errors can be broken down into two categories: memory & network problems and issues arising from improper use of syntax & phrasing. Many of the former errors involve interacting with a network administrator to determine where faulty connections and misaligned partitioning of memory are stemming from. The latter, which the ORA-01422 can most aptly identify with, concerns a user-initiated mistake resulting from either a typo or a misunderstanding of how Oracle functions may work.

So what syntax mistakes are causing the ORA-01422 error? Why are they a problem? And most importantly, how can we fix it? Well, let’s start at the beginning and talk briefly about just what exactly an ORA-01422 really is.

The Problem

Oracle describes the ORA-01422 error as the “exact fetch” returning “more than requested number of rows”. The type of vague contextual clue that Oracle attaches to this message concerning the error’s origins can be quite infuriating initially.

The basics of the problem derive from a failed SELECT INTO statement. This statement pulls data from one or more database tables and subsequently assigns the information to specified variables. In its default setting, the SELECT INTO statement will return one or more columns from a single specified row.  This is where the error is being thrown.

When an ORA-01422 is triggered, your SELECT INTO statement is retrieving multiple rows of data or none at all. If it is returning multiple, the predefined exception TOO_MANY_ROWS will be raised, and for no returns the PL/SQL will raise NO_DATA_FOUND. Because the SELECT INTO statement in its default setting is designed to retrieve only one row, the system responds with an error at either one.

The Solution

There are a number of fixes and preemptive adjustments that you can make in order to prevent the issuance of an ORA-01422 error. How you choose to approach it is entirely dependant on your data tables, but we will go over some strategies.

First, to protect against no rows being returned, you can select the result of any type of aggregating function (such as AVG or COUNT). This will be helpful because a function like COUNT will at the least be guaranteed to return some type of value.

Much more common will be an error resulting from multiple rows being returned. An initial step can be to check that the WHERE clause in your statement is exclusive enough to only matchup one row. This will require having knowledge of your rows and how they are similar & different so that you can know how to target results from a particular row.

Another step that you can take to offset multiple row quandaries is to run a BULK COLLECT in a table to pull more variables.  This will pull more rows and variables but in a concise manner. However, be wary of using BULK COLLECT excessively as it can use a great deal of memory.

Finally, a great recommendation would be to replace your SELECT INTO statement with a cursor. A cursor is a tool through which you can give a name to a SELECT statement and change the data within that statement as you please. This will effectively create a loop that will fetch multiple rows without generating the error. Below is an example of what this would look like in action.

Example of ORA-01422 Error

SQL> declare

v_student number;

begin

select student into v_student from michael.std where deptno=12;

end;

/

declare

*

ERROR at line 1:

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

ORA-06512: at line 4

Example of Cursor Solution

SQL>declare

v_student number;

begin

for c in (select student into v_student from Michael.std where deptno=12)

loop

v_student := c.student;

end loop;

end;

/

Looking forward

The ORA-01422 error differs from many in that it could involve a bit of coding on your end to resolve. This is a far cry from the simple Oracle errors that simply require changing the punctuation of a statement quickly and moving on. With that said, the tools above should be plenty to get started and work to develop a solution. In the event that you find yourself completely lost, it can never hurt to contact a licensed Oracle consultant for more information.