getting ORA-01422: exact fetch returns more than requested number of rows. while setting up UK bank branch

15 pts.
Tags:
Database
Oracle
Oracle installation
Hi All iam getting ORA-01422: exact fetch returns more than requested number of rows. while setting up UK bank branch. Please,let me know if someone faced the similar issue. Thanks Priya
ASKED: January 8, 2012  11:33 PM
UPDATED: February 28, 2012  12:10 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

As the error states, your query is returning more than one row, when a single row/value is expected, the options to resolve this error are:

1. Rewrite your SELECT INTO statement so that only one row is returned.
2. Replace your SELECT INTO statement with a cursor.

Additional helpful information about this error with examples of how to resolve it can be found at the link below -

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:981494932508

Here is an example in PLSQL of the error. Notice that the data being returned clearly has more than one row. As was described above, a SELECT INTO is supposed to have one and only one row. If there is more than one row you get that error. So look in your code for a SELECT INTO and figure out why you are fetching more than one row.

<pre>SQL> declare
2 v1 integer;
3 begin
4 select c1
5 into v1
6 from (
7 select 1 c1 from dual
8 union all
9 select 2 c1 from dual
10 )
11 ;
12 end;
13 /
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4</pre>

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Pdweep
    Thanks Techtalker for the help. But,actually we are setting the bank branch setup from frontend and we are not using any api or code,we just setting the bank branch from the bank form in R12
    15 pointsBadges:
    report
  • carlosdl
    If this is a software you have successfully implemented in other branches, then the problem most likely lies in the data. As mentioned by TechTalker, the program is getting 2 rows when one row is expected. If you have no access to the code, then you will have to review the data to solve it. Where ? We have no idea, as we don't even know what software you are implementing, and the error message is not an application message, but an unhandled Oracle error.
    65,110 pointsBadges:
    report
  • carlosdl
    "...the program is getting 2 rows..." I meant: 2 or more rows.
    65,110 pointsBadges:
    report
  • FlaviusMaximus
    Based on your reply, I would suggest that possibly your form through which you are doing an update, is trying to refetch the row after update and is getting two or more hits based on the SQL it is generating by default. This suggests that your application has the wrong key definition for the underly rowsource. For example, in Oracle Forms, you rely on constraints in the database, or on the PRIMARY KEY attribute of the fields on a block. You appliaction whatever it is might be doing the same kind of things. Thus you need to get your app updated to have the right key knowledge about the underlying rowsource. In forms this would be either to regenerate the form after a PRIMARY KEY change in the database, or to modify the correct set of form to indicate which field make up the PRIMARY KEY for the block. This is just a guess though.
    220 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following