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
  • 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Bash Script to Analyze Oracle Schemas 11 102
Export table into csv file in oracle 10 74
clob to char in oracle 3 34
Help on model clause 5 27
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

813 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now