PSOUG.org

General Category => PSOUG => Topic started by: Prakash on October 03, 2013, 01:49:11 PM

Title: exact fetch returns more than requested number of rows
Post by: Prakash on October 03, 2013, 01:49:11 PM

-- Example to show error message - "exact fetch returns more than requested number of rows"
Code: [Select]
DECLARE
   x   DUAL.dummy%TYPE;
BEGIN
   SELECT dummy
     INTO x
     FROM DUAL, (SELECT * FROM all_users);
END;

--Solution to correct this is by fetching rows that can be accommodated into the catching variable.
Code: [Select]
DECLARE
   x   DUAL.dummy%TYPE;
BEGIN
   SELECT dummy
     INTO x
     FROM DUAL;
   -- SELECT * FROM all_users; --Can't do this here
END;

-- This error occurs when more number of rows are retrieved and being assigned to a variable
-- that can hold only one single value. You need to retrieve only row(s) that can be held in the
-- catching variable
Title: Re: ORA-01422: exact fetch returns more than requested number of rows
Post by: Prakash on June 17, 2014, 09:21:00 AM
In addition to above details to this error/exception:

ORA-01422: exact fetch returns more than requested number of rows (http://psoug.org/oraerror/ORA-01422.htm)

By default, a SELECT INTO statement must return only one row. Otherwise, PL/SQL raises the predefined exception TOO_MANY_ROWS and the values of the variables in the INTO clause are undefined. Make sure your WHERE clause is specific enough to only match one row

If no rows are returned, PL/SQL raises NO_DATA_FOUND. You can guard against this exception by selecting the result of an aggregate function, such as COUNT  or AVG(), where practical. These functions are guaranteed to return a single value, even if no rows match the condition.
Title: Re: ORA-01422: exact fetch returns more than requested number of rows
Post by: Mike on June 17, 2014, 01:37:57 PM
Thank you, Prakash!

We've added this information into the error code listing for the ORA-01422 (http://psoug.org/oraerror/ORA-01422.htm) error.