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

Problem ——–

 

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

Cause: More rows were returned from an exact fetch than specified.

Action: Rewrite the query to return fewer rows or specify more rows in the exact fetch.

 

From Tom Kyte:
———————

 

If you EXPECT the query to return more then one row, you would code:

for x in ( select * from t where … )

loop

— process the X record here

end loop;

 

 

 

 

If you expect the query to return AT LEAST one record and AT MOST one record, you would
code:

begin
select * into ….
from t where ….

process….
exception
when NO_DATA_FOUND then
error handling code when no record is found
when TOO_MANY_ROWS then
error handling code when too many records are found
end;

If you just want the FIRST record

declare
c1 cursor for select * from t where …
begin
open c1;
fetch c1 into ..
if ( c1%notfound ) then
error handling for no record found
end if;
close c1;
end;

 

It also happened:

 

select myseq.nextval into id from dual raises that error and it turned out that sys.dual contains 2 rows

 

select count(*) from dual;

count(*)

———

2

Advertisements
Tags:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: