[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1581
  • Last Modified:

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

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       

  • 3
  • 2
1 Solution
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.
jbauer22Author Commented:
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.
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

jbauer22Author Commented:
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;
Just as I said!
jbauer22Author Commented:
I think I posted my message before the thread was refreshed.  Thanks.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now