DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Exact fetch returns more than requested number of rows

  1. #1
    Join Date
    Mar 2008
    Posts
    1

    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 ;

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.

  3. #3
    Join Date
    Jan 2007
    Posts
    231
    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
  •  


Click Here to Expand Forum to Full Width