Skip to Main Content
  • Questions
  • How to handle ORA-01422 exact fetch returns more ....

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, ismail.

Asked: July 14, 2001 - 1:30 pm UTC

Last updated: September 09, 2013 - 10:57 am UTC

Version: 2000

Viewed 100K+ times! This question is

You Asked

Hi Tom

I'm trying to execute SQL statment but the result is :

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.

So How can I handle this proplem ?

Thank you

and Tom said...

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;

Rating

  (23 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

MW, September 24, 2002 - 9:28 am UTC

Hi, Tom

I have a following Problem.

I have a table and it's primary key value get from the trigger ( refer Sequence). It was working file. All of a sudden, when I try to insert values, then I got this Error.

ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "myusr.TRG_LNKPCGRP_PCGRPNO", line 5
ORA-04088: error during execution of trigger 'myusr.TRG_LNKPCGRP_PCGRPNO'

But this fetch value is in trigger is as

Select SEQ_LNKPCGRP_PCGRPNO.NEXTVAL INTO iCounter FROM Dual;

So I am realy in confusing oin this. Could you please help me on this.

Thank you in advance.

Tom Kyte
September 24, 2002 - 3:36 pm UTC

check to see if someone added a row to dual:

ops$tkyte@ORA920.US.ORACLE.COM> @connect "/ as sysdba"
sys@ORA920.US.ORACLE.COM> insert into dual values ( 'y' );

1 row created.

sys@ORA920.US.ORACLE.COM> select * from dual;

D
-
X

sys@ORA920.US.ORACLE.COM> select count(*) from dual;

  COUNT(*)
----------
         2

sys@ORA920.US.ORACLE.COM>


<b>dual is magic, make sure to COUNT(*) it, not just select * from it</b>

sys@ORA920.US.ORACLE.COM> declare x number; begin select my_seq.nextval into x from dual; end;
  2  /
declare x number; begin select my_seq.nextval into x from dual; end;
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 1

will happen when dual has more then 1 row 

MW, September 25, 2002 - 4:27 am UTC

Thank you very much. this solved the problem.

Tom Kyte
September 25, 2002 - 8:18 am UTC

Now, you need to find out who put that row into dual and soundly and thoroughly beat them about the head with a wet noodle.. Doh.

SELECT * and SELECT COUNT(*) from Dual are Inconsistent?

Mac, September 25, 2002 - 9:02 am UTC

Tom,

So someone can insert a second row into DUAL, and when they SELECT * from that table, they only get 1 record back? If so, why? What kind of "magic" is this which goes against established rules?

Why does COUNT(*) from DUAL always return the correct number?

Does this mean SELECT column_name FROM DUAL will always be untrustworthy?

(Since I do not have sysdba privilege, I can not check this for myself.)

I am very grateful for your excellent site. If only more teachers in the world had your logic, patience and approach...

Thank you!

Tom Kyte
September 25, 2002 - 9:56 am UTC

DUAL is special, DUAL is magic.

DUAL is also part of the Oracle data dictionary (the SYS.* tables).

As is true with ALL SYS OWNED TABLES -- the moment you insert/update/delete them directly is the moment your database becomes UNSUPPORTED and UNSUPPORTABLE. Don't ascribe any normal behavior to anything related to SYS, it is special as well.

I suggest not even LOGGING IN as sys ever if you can - always use SYSOPER and only for startup/shutdown (as thats all it can do)

DUAL

Paul, September 25, 2002 - 11:02 am UTC

I once got 4 rows in DUAL after repeatedly doing a full System Import!

But if Oracle have this magic thing (lets call it SQL*Magic for consistency), why don't they use it to prevent anyone adding another row to DUAL?

To be serious, if there was a unique index on DUAL, it would have stopped my Import problem - I guess this isn't done for performance reasons.

Just musing.

Paul

Tom Kyte
September 25, 2002 - 11:19 am UTC

SYS owned objects are never (have never) been exported.

DUAL is owned by SYS (or at least it should be)

Only if DUAL was owned by some user OTHER THEN sys would this, could this have occurred.

Hence, I do not see how this could have happened with EXP/IMP.

Now, if you run sql.bsq more then once, this could happen (our bootstrap, that which creates the data dictionary -- the SYS "real" data dictionary). But, if you run sql.bsq more then once -- that might do it (but that would be a truly bad thing in any case, I would just trash that database and start over, there would be other bad side effects)

True but ...

Paul, September 25, 2002 - 12:01 pm UTC

... this was a long, long time ago when (if I recall correctly) DUAL was owned by SYSTEM rather than SYS.

I think it was V6 (could've been V5!).

Somebody out there must remember - Tom, you can't be that young if you programmed at school on punch cards ;-)

Paul

Tom Kyte
September 25, 2002 - 12:28 pm UTC

but it has been going on 10 years since I've fired up a v6 database! could be and I just don't remember.

I'm sure someone out there has one running and could take a look see for us....

Goto first record

Sikandar Hayat, October 14, 2004 - 6:29 am UTC

Is there any way to goto first record without closing opened cursor. Say there are 100 records in a cursor and we fetched upto 50 for processing. We have an IF check so we can start the loop from first record.

Tom Kyte
October 14, 2004 - 10:23 am UTC

depends on your client language. see what the language you are using provides.

In PLSQL, if you bulk collect into an array, you would be processing an array and have array access to any row. jdbc has scrollable result sets. and so on.

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

Huy, December 12, 2007 - 10:09 am UTC

Hi Tom,
Thank you to get my problem solved with ORA-01422.
While learning RMAN commands as following:
RMAN> run
2> { allocate channel ch1 type disk;
3> backup tablespace users
4> include current controlfile;}

It always returned with ORA-01422- exact fetch returns more than requested number of rows.

until you mentioned do
Select count(*) from dual;

Error ORA-01422 For dbms_xplan.display_cursor

Stan, March 20, 2009 - 9:41 am UTC

select * from table(dbms_xplan.display_cursor('a04q24psvzw3c', 1025)) ;

PLAN_TABLE_OUTPUT
---
SQL_ID a04q24psvzw3c, child number 1025
----------------------------------------
An uncaught error happened in prepare_sql_statement : ORA-01422: exact fetch returns more than requested number of rows

Plan hash value: 2866715059

------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 11 (100)| |
... etc.

I note that when I query:

select * from v$sql where sql_id = 'a04q24psvzw3c' and child_number = 1025;

I get 2 rows - so I guess the display_cursor procedure is expecting only 1 row also.

I thought the sql_id, child_number combination was unique? If not then what combination of columns in in v$sql are unique? It would be a display_cursor bug?

Thanks for your help in advance !!!

Version 10.2.0.4

Tom Kyte
March 24, 2009 - 10:34 am UTC

it should be unique - please utilize support for this one

query process

A reader, June 01, 2009 - 3:27 am UTC

greeting thomas,

and thanks like always.

can you explain how does oracle process implicit cursor, what i mean is does oracle in implicit cursor fetch the first row, then when it fetch the second row the error ORA-01422 occurd.
Please clear.

Tom Kyte
June 01, 2009 - 8:11 pm UTC

this is not implicit versus explicit, this is "select into" processing in 3gl languages that support it (eg: pro*c, plsql)


a select into query is designed to

a) return AT LEAST one row
b) return AT MOST one row

if it returns more than one - error "too many rows"
if it returns less than one - error "no data found"

ops$tkyte%ORA10GR2> declare
  2          x dual.dummy%type;
  3  begin
  4          select dummy into x from dual;
  5  end;
  6  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> declare
  2          x dual.dummy%type;
  3  begin
  4          select dummy into x from dual where 1=0;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4


ops$tkyte%ORA10GR2> declare
  2          x dual.dummy%type;
  3  begin
  4          select dummy into x from dual, (select * from all_users);
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4




that is what it does "by definition"

if "less than one" is not an error - just catch it:

ops$tkyte%ORA10GR2> declare
  2          x dual.dummy%type;
  3  begin
  4          select dummy into x from dual where 1=0;
  5  exception
  6          when no_data_found then
  7                  x := 'Y';
  8  end;
  9  /

PL/SQL procedure successfully completed.



I cannot think of a case where too many rows would not be erroneous (so if you can, post ALL OF THE DETAILS - I MEAN ALL OF THE DETAILS - I MEAN BE REALLY CLEAR).....

A reader, June 02, 2009 - 4:02 am UTC

thank you Thomas for your replay and i will clear what i meant.
I need to understand statement processing of implicit cursor.
and i will use your example.
declare
x dual.dummy%type;
begin
select dummy into x from dual, (select * from all_users);
end;
/
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4

i think what generate this error (ORA-01422) was as follow:
1) oracle parse and execute the select
2) oracle start to get the rows (fetch).
3) oracle fetched the first row then it fetched the second row after that the error ORA-01422 occurred.
so the error (ORA-01422) was generated in the fetch phase?

Tom Kyte
June 02, 2009 - 7:40 am UTC

the psuedo code logic of a select into is exactly this:


declare
   cursor c is select * from .....;
begin
   open c;
   fetch c into <something>;
   if ( sql%notfound )
   then
       raise no_data_found;
   end if;
   fetch c into <something else>;
   if ( sql%found )
   then
       raise too_many_Rows;
   end if;
   close c;
   return something;
end;





the programming language (pl/sql, pro*c, sql-j) does this under the covers. It is not really part of "sql", it is a programming environment thing that a precompiler does for us.



and it is only ONE example of an implicit cursor:



...
for x in (select * from t)
loop
   ...



select * from t is an implicit cursor - but it'll NEVER raise no_data_found, nor will it ever raise too_many_rows - those two exceptions are unique to select into

How to handle ORA-01422 exact fetch returns more than one row

A reader, September 15, 2010 - 3:23 pm UTC

consider having the table listed below:

CREATE TABLE employee
(
emp_id,
first_name VARCHAR2(50),
salary FLOAT(126)
)

populated with many records (table can have a primary key etc.- out of scope)

If you try to execute the pl/sql below you should get an error

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


declare

name varchar2(40);
sal number;

begin

select salary into sal from
employee;
dbms_output.put_line(sal);

end;

This is because the sql query returns more than 1 rows. In order to get all records you could use cursors as shown below.

declare

cursor c1 is
select first_name, salary
from employee;

name varchar2(40);
sal number;
counter number;

begin

--find number of records
select count(emp_id) into counter
from employee;

open c1;
--print all
for i in 1..counter loop
fetch c1 into name,sal;
dbms_output.put_line(name||' '||sal);
end loop;
close c1;
end;

Results
Tasos 2000
Spyros 2500
Panos 5000
George 1200
Jenny 2300
Julia 9000
Mary 9000

You can find it here as well:
http://rodenthood.blogspot.com/2010/09/ora-01422-exact-fetch-returns-more-than.html
Tom Kyte
September 15, 2010 - 3:32 pm UTC

Umm,

you haven't really "handled" anything here at all - you have two utterly and completely different bits of code that would have different intents behind them.


select into is used to get at least one row and at most one row from a query. If a select into (without bulk collect) does not return any rows - it throws an exception "no_data_found". If a select into returns MORE than one row - it throws an exception "too_many_rows"

You would ONLY use select into (without bulk collect) if you meant to get at least one row and at most one row from a query - or if getting NO ROWS is "ok", you would handle that exception (and the same with too_many_rows - although I cannot think of a real life situation where that would be "ok").


Your second bit of code is horrible - horrendous, the worst way to do it.


The code SHOULD be simply:

begin
  for x in (select first_name, salary from employee ) 
  loop
     dbms_output.put_line( x.first_name || ' ' || x.salary );
  end loop;
end;
/



Nothing more, nothing less. Your approach of "get a count, then fetch that many" is

o the least performant way to approach the problem, why run a query to count rows???? ugh, I hate that.

o the most buggy way to approach the problem. What if - in between the time you counted and the time you open the other query 'c1', the number of rows changes???? You would end up printing out the last fetched row over and over if the number of rows was decreased, you would end up NOT printing out some records if the number of rows was increased. All without any errors whatsoever. Ugh, I hate that even more.


Please don't do that. Just use a simple cursor for loop - less change for you to code as many bugs as you have. Less code = Less bugs.

A reader, September 15, 2010 - 4:52 pm UTC

Hello again,

The point is not about handling, just how to avoid
ORA-01422 exact fetch returns more than requested number of rows

When you declare a cursor, you have to open it and to fetch it. So the for i in 1..number (number could be 10 or 15) how do you know that you have retrieved all records? This is why I used count(). How can you avoid this using a cursor?

Thanks

Tom Kyte
September 15, 2010 - 5:01 pm UTC

The point was about handling (the original question was "how to handle", YOU wrote "how to handle").

The entire thing is about "how to handle"

and your code is just *wrong*, totally. I showed you how to do this, you simply code:

begin
  for x in (select first_name, salary from employee ) 
  loop
     dbms_output.put_line( x.first_name || ' ' || x.salary );
  end loop;
end;
/



period. Please read what I wrote - tell us - what happens in your code when between the time your count query started and the time the second select query started THE NUMBER OF ROWS CHANGES??? Your code is totally wrong.

You really do need to read up on how to do this stuff and get a bit more experience before you start blogging. Please...



If you want to use an explicit cursor, you would code:

declare
   cursor c is select ... from ...;
   l_rec c%rowtype;
begin
   open c;
   loop
      fetch c into l_rec;
      EXIT WHEN C%NOTFOUND;
      process l_rec here...
   end loop;
   close c;
end;
/


that is "cursor coding 101" stuff - entry level. Whoever taught you the "count them, then loop that many times" did you a huge disservice, you have a lot of bad practices to "unlearn" from that teacher. It is not only wrong, it is poorly performing and wrong.




A reader, September 15, 2010 - 5:07 pm UTC

I will follow your advice

Thanks

ORA-01422

A reader, May 08, 2011 - 8:39 am UTC

Hi Tom,

This is a performance testing.I have the need to write the output to a text file.
The emp table contains 10Million records,similarly dept contains huge record.(All these for test only)
My sql query looks as below:

DECLARE
CURSOR c_data IS
select e.empno empno,e.ename ename,d.location location
where e.deptno=d.deptno;

v_file UTL_FILE.FILE_TYPE;
BEGIN
v_file := UTL_FILE.FOPEN(location => 'TEST',
filename => ''LOCATION.dat,
open_mode => 'w',
max_linesize => 32767);
FOR cur_rec IN c_data LOOP
UTL_FILE.PUT_LINE(v_file,
cur_rec.empno || ',' ||
cur_rec.ename || ',' ||
cur_rec.location)
END LOOP;
UTL_FILE.FCLOSE(v_file);

Exceptions.......

..........

I am getting "ORA-01422: exact fetch returns more than requested number of rows"

Thanks.
Tom Kyte
May 09, 2011 - 7:19 am UTC

give full example, REMOVE ALL CODE that is not relevant to getting the answer.

The above code shown WOULD NOT raise that error.


Also, I can tell you what the performance of the above would be. It will stink, it will be slow, it will take a really long time to execute. I don't think you want to use plsql to write 10,000,000 records to a file.

exact fetch returns

lalu121212, June 30, 2011 - 6:13 am UTC

Hi Tom,

With regards to your below comment
"
I don't think you want to use plsql to write 10,000,000 records to a file.
"

Can you let us know how this can be handled.
How can we write to a file without using plsql, in other words we can make it write/run faster?


Tom Kyte
July 01, 2011 - 8:34 am UTC

see http://laurentschneider.com/wordpress/2011/06/csv-part-4-fast.html for some ideas, but I think you'd find Java or C to be 'faster' over all.

ORA-01422

abhishek kumar, December 17, 2011 - 11:20 pm UTC

Thanks Tom.

I was suddenly facing ORA-01422 and I checked the above post of "September 24, 2002 - 3pm Central time zone" and issue got resolved within minutes.. :)

Thanks a lot..!!

Merry Christmas..

How to handle ORA-01422 exact fetch returns more ....

Marlon Bohol, May 07, 2013 - 3:19 am UTC

It answer the question.

Thank you so much.

why am i getting this error? ORA-01422: exact fetch returns more than requested number of rows

Ravi B, May 31, 2013 - 6:33 pm UTC

CREATE TABLE T(INVENTORY_ID number,SUBSCRIPTION_ID number, TECHNOPEDIA_STRUCTURE_MODE varchar2(100));
INSERT INTO T values(107647595,10009,'All');
INSERT INTO T values(107647595,0,'All');

select * from T;

set serveroutput on
DECLARE 
l_tps_structure_mode varchar2(100);
l_program_name varchar2(100):='TEST';
subscription_id NUMBER:=10009;
inv_id NUMBER:=107647595;
BEGIN
    SELECT TECHNOPEDIA_STRUCTURE_MODE
      INTO l_tps_structure_mode
      FROM T tm
     WHERE tm.SUBSCRIPTION_ID = subscription_id
       AND tm.INVENTORY_ID = inv_id;
    DBMS_OUTPUT.PUT_LINE(l_tps_structure_mode);
   EXCEPTION
   WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('------------------------');
     DBMS_OUTPUT.PUT_LINE('subscription_id:'||subscription_id);
     DBMS_OUTPUT.PUT_LINE('inv_id:'||inv_id);
     DBMS_OUTPUT.PUT_LINE('Error in selecting TECHNOPEDIA_STRUCTURE_MODE');
     DBMS_OUTPUT.PUT_LINE(SQLERRM);
     DBMS_OUTPUT.PUT_LINE('------------------------');
     RAISE;
   END;
   /


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

Tom Kyte
May 31, 2013 - 7:09 pm UTC

because you are just saying:

where column1 = column1 and column2 = column2;


tm.subscription_id = subscription_id

is the same as

subscription_id = subscription_id

or

tm.subscription_id = tm.subscription_id


if you want to use plsql variables named the same as database objects, you'll need to qualify them, for example:


ops$tkyte%ORA11GR2> <b><<main>></b>
  2  DECLARE
  3  l_tps_structure_mode varchar2(100);
  4  l_program_name varchar2(100):='TEST';
  5  subscription_id NUMBER:=10009;
  6  inv_id NUMBER:=107647595;
  7  BEGIN
  8      SELECT TECHNOPEDIA_STRUCTURE_MODE
  9        INTO l_tps_structure_mode
 10        FROM T tm
 11       WHERE tm.SUBSCRIPTION_ID = <b>main.subscription_id</b>
 12         AND tm.INVENTORY_ID = <b>main.inv_id;</b>
 13      DBMS_OUTPUT.PUT_LINE(l_tps_structure_mode);
 14  END;
 15  /
All

PL/SQL procedure successfully completed.



or, even better:



ops$tkyte%ORA11GR2> create or replace <b>procedure my_procedure</b>( subscription_id in number, inv_id in number )
  2  as
  3  l_tps_structure_mode varchar2(100);
  4  l_program_name varchar2(100):='TEST';
  5  BEGIN
  6      SELECT TECHNOPEDIA_STRUCTURE_MODE
  7        INTO l_tps_structure_mode
  8        FROM T tm
  9       WHERE tm.SUBSCRIPTION_ID = <b>my_procedure</b>.subscription_id
 10         AND tm.INVENTORY_ID = <b>my_procedure</b>.inv_id;
 11      DBMS_OUTPUT.PUT_LINE(l_tps_structure_mode);
 12  END;
 13  /

Procedure created.

ops$tkyte%ORA11GR2> exec my_procedure( 10009, 107647595 );
All

PL/SQL procedure successfully completed.





or you could use a naming convention like I do.

All parameters to a procedure/function start with P_
All local variables start with L_
All global variables in a package start with G_


exact fetch returns more than requested number of rows

Prakash, August 23, 2013 - 10:32 am UTC

HAS ANYONE TRIED THIS ??

WHEN WE GET exact fetch returns more than requested number of rows
WHAT VALUE GET ASSIGNED TO VARIBALE.

MOST OF MIGHT THINK IT SHOULD BE NULL.BUT TRY TO PRINT THE VALUE IN EXCEPTION BLOCK YOU SHOULD HAVE VALUE FROM FIRST ROW OF THE SELECT STATEMENT.

Hi Tom,

why the value from the first row of the query gets assigned to variable
Tom Kyte
August 28, 2013 - 6:21 pm UTC

... WHAT VALUE GET ASSIGNED TO VARIBALE.
...


nothing, the fetch never worked. the statement failed. the variable state is "undefined"


when that statement failed, you should just know as a programmer that any value in the host variables is "not defined", 'unusable', 'not something you should consider looking at'


can we count how many rows returned

sairam, September 04, 2013 - 8:37 pm UTC

Hi tom,
The answer to my problem is an extension of this error message and i rectified it in my code however I need to perform validation against 3 different accounts (say) in the select statement which returns multiple records into variable and when the exception is raised it is performing well but is there any way I can be sure that the rows returned is 3 before or after I go into exception.
Tom Kyte
September 09, 2013 - 10:57 am UTC

I'm not sure what you mean here.

if you know you are going to get 3 records - there is no way you would be using select into.

maybe select bulk collect into - but not select into

ponka

chubaba, October 01, 2013 - 5:39 am UTC

lol okie im not like this
u uncle tommy blind man

MOST OF MIGHT THINK IT SHOULD BE NULL.BUT TRY TO PRINT THE VALUE IN EXCEPTION BLOCK

A reader, October 05, 2013 - 6:04 pm UTC

hi,

found this page searching for this same issue.
try it, we will all be gratefull in checking this out
thanks.

select *from v$version;
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

Check IT

User, October 19, 2013 - 9:03 am UTC

hi,

found this page searching for this same issue.
try it, we will all be gratefull in checking this out
thanks.

select *from v$version;
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production