Solved

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

Posted on 2004-03-26
6
1,519 Views
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);

begin

  -- 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;
  else
     InstanceCount := PS_MI_GL_NVS_INSTANCES(SetID, Scope, ComboTable, EffDate);
  end if;
 
  return(InstanceCount);
 
  -- Error Handling
  exception
 
    -- Return a zero when no data is found
    when no_data_found then
         InstanceCount := 0;
         return(InstanceCount);
 
end PS_MI_GL_NVS_COUNT;



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'

         REPORT_SCOPE      RECNAME_COMB
1      52996PD       

Help!
0
Comment
Question by:jbauer22
[X]
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
6 Comments
 
LVL 2

Accepted Solution

by:
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.
0
 
LVL 2

Author Comment

by:jbauer22
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.
0
 
LVL 15

Expert Comment

by:andrewst
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


0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:jbauer22
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;
0
 
LVL 15

Expert Comment

by:andrewst
ID: 10688035
Just as I said!
0
 
LVL 2

Author Comment

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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 shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

623 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