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

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

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

Cause:

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

Solution:

Rewrite the query or change number of rows requested.

Example:

declare
    v_name varchar2(255);  
begin
    select name 
    into v_name 
    from books 
    where id=2;
    dbms_output.put_line('The name is: '||v_name);
end;

Output:

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

Correct

declare
    v_name varchar2(255);
    v_rows number;
begin
    select name 
    into v_name 
    from books 
    where id=2;
    dbms_output.put_line('The name is: '||v_name);
exception 
when too_many_rows then
	select count(*) into v_rows 
	from books where id=2;
	dbms_output.put_line('Error, rows = '||v_rows);
end;

Output:

Error, rows = 2