Simple PL/SQL Statement: exact fetch returns more than requested number of rows

Posted on 2004-03-26
Last Modified: 2010-05-18
I have a stored function that is giving me an error: "exact fetch returns more than request number of rows".  When I run the sql outside of the procedure it only returns 1 row.  I feel like Oracle is lieing to me.  I'm only trying to get the value of the one row and put them into 2 variables: Scope & ComboTable.

create or replace function PS_MI_GL_NVS_COUNT(SetID in ps_nvs_scope.setid%type,
                                              BU in ps_nvs_report.business_unit%type,
                                              Report in ps_nvs_report.report_id%type,
                                              EffDate in pstreenode.effdt%type)
  return number is
  -- Declare local variables
  Scope ps_nvs_scope.report_scope%type;
  ComboTable ps_nvs_scope.recname_comb%type;
  InstanceCount number(16);


  -- Get Scope and Combination table
  -- If there is a bad report request name, then the no_data_found exception is raised
  select s.report_scope, s.recname_comb --<---- Error occurs here
  into Scope, ComboTable
  from ps_nvs_report r, ps_nvs_scope s
  where r.report_scope = s.report_scope (+)  -- Display scope even if null
  and r.business_unit = BU
  and r.report_id = Report
  and s.setid = SetID;
  -- Get Instance Count
  -- If the Scope is null then the report requests generates 1 instance
  if Scope is null then
     InstanceCount := 1;
     InstanceCount := PS_MI_GL_NVS_INSTANCES(SetID, Scope, ComboTable, EffDate);
  end if;
  -- Error Handling
    -- Return a zero when no data is found
    when no_data_found then
         InstanceCount := 0;

When I run the SQL outside of this procedure I get one row.

 select s.report_scope, s.recname_comb
  from ps_nvs_report r, ps_nvs_scope s
  where r.report_scope = s.report_scope (+)  -- Display scope even if null
  and r.business_unit = '52'
  and r.report_id = '52996PDF'
  and s.setid = 'MIDIV'

1      52996PD       

Question by:jbauer22
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2

Accepted Solution

aabbas earned 75 total points
ID: 10687461
You might be getting some NULL record thus invisible in the SQL*PLUS environment. Try ROWNUM to see in this further.

As far as any appropriate solution is concerned, I cannot, until I have the same situation at my side. I will need to have your functions and tables and all data to check it and debug. That would take too long, I guess.

A couple of suggestions may help you handling with this error.

1. Use TOO_MANY_ROWS exception to handle this situation and do what is required, accordingly.

2. Use explicit CURSOR instead. Using this, you will fetch just the first record and no such error will be encountered.

Hope, you will get rid of this wierd error. :)

Best of luck.

Author Comment

ID: 10687607
I'm using PL/SQL Developer and I can see the rownum.  The SQL is only producing 1 row.  I don't understand why I'm getting the error.
LVL 15

Expert Comment

ID: 10687682
It is getting confused because your parameter names are the same as the column names in some cases:

...and s.setid = SetID

which Oracle sees as:

...and s.setid = s.setid

which is equivalent to:

...and 1=1

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now


Author Comment

ID: 10687700
I found the problem.  The SQL statement was getting confused with my SetID paramenter.  So I changed it to vSetID and it worked.

select s.report_scope, s.recname_comb
  into vScope, vComboTable
  from ps_nvs_report r, ps_nvs_scope s
  where r.report_scope = s.report_scope (+)  -- Display scope even if null
  and r.business_unit = vBU
  and r.report_id = vReport
  and s.setid = vSetID;
LVL 15

Expert Comment

ID: 10688035
Just as I said!

Author Comment

ID: 10688188
I think I posted my message before the thread was refreshed.  Thanks.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question