-
Exact fetch returns more than requested number of rows
Hello Guys,
I am working on some production issue and was executing the existing query and got this " exact fetch returns more than requested number of rows"
I think this is caused by using Select INTO clause and found a solution to use Bluck Collect by using a table declaration. But the problem is this is possible only with Oracle 8i and higher and I am using Oracle 7.3.
Here is the full text of the cursor:
my_tot_cred number ;
my_earn_07 number ;
my_bene_sex char(1) ;
my_bene_dob date ;
begin
for r1 in c1 loop
begin
select tot_cred into my_tot_cred
from perf_pen_07
where aftra_no=r1.aftra_no ;
exception
when no_data_found then
my_tot_cred := 0 ;
end ;
begin
select round(pen_earn) into my_earn_07
from pen_hist
where pen_yr='2007'
and aftra_no=r1.aftra_no ;
exception
when no_data_found then
my_earn_07 := 0 ;
end ;
my_bene_sex := null ;
my_bene_dob := null ;
if r1.pay_option_cd in (2,3,4,15) then
begin
select sex, dob into my_bene_sex, my_bene_dob
from perf_benef
where aftra_no=r1.aftra_no ;
exception
when no_data_found then
begin
select sex, dob into my_bene_sex, my_bene_dob
from perf_depen
where aftra_no=r1.aftra_no
and relat_cd in (1,2) ;
exception
when no_data_found then
my_bene_sex := null ;
my_bene_dob := null ;
when too_many_rows then
my_bene_sex := 'X' ;
my_bene_dob := null ;
end ;
end ;
end if ;
-
It looks like your code is expecting to find a single row but when executed you are getting multiple rows.
Question is... what's wrong? is it your code or is it your data?
Q1- Business Requirements are calling for a single row or for multiple rows in such a condition?
Q1.A1- Single Row... your data is wrong, fix your data.
Q1.A2- Multiple Rows... your code is wrong, fix your code.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Code:
my_tot_cred number ;
my_earn_07 number ;
my_bene_sex char(1) ;
my_bene_dob date ;
begin
for r1 in c1 loop
begin
select tot_cred into my_tot_cred
from perf_pen_07
where aftra_no=r1.aftra_no ;
exception
when no_data_found then
my_tot_cred := 0 ;
end ;
begin
select round(pen_earn) into my_earn_07
from pen_hist where pen_yr='2007'
and aftra_no=r1.aftra_no ;
exception
when no_data_found then
my_earn_07 := 0 ;
end ;
my_bene_sex := null ;
my_bene_dob := null ;
if r1.pay_option_cd in (2,3,4,15) then
begin
select sex, dob into my_bene_sex, my_bene_dob
from perf_benef
where aftra_no=r1.aftra_no ;
exception
when no_data_found then
begin
select sex, dob into my_bene_sex, my_bene_dob
from perf_depen
where aftra_no=r1.aftra_no
and relat_cd in (1,2) ;
exception
when no_data_found then
my_bene_sex := null ;
my_bene_dob := null ;
when too_many_rows then
my_bene_sex := 'X' ;
my_bene_dob := null ;
end ;
end ;
end if ;
Where does the for -- loop ends.Send orginal code(or similar to that) so that we could trace it out.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|