Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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

Posted on 2004-03-26
Medium Priority
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 300 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.


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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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.

688 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