Clever Geek Handbook

Reason for getting ORA-01422: fine sampling returns more than requested number of rows

So I'm working on an installer where the installer connects to the database and creates tables and populates them. Every aspect of this works correctly except when I'm trying to add rows to a certian table.

declare
  retVal INTEGER;
  rptID INTEGER;
  catID INTEGER;
  wsID INTEGER;
  paramID INTEGER;
  dtID INTEGER;
begin

  select PK into catID from RPT_CATEGORY where KEYVALUE = 'ProductivityReportsCategory';
  select PK into rptID from RPT_REPORT where KEYVALUE = 'ProductivitySummaryReport2';
  select PK into wsID from RPT_WEBSVC where KEYVALUE = 'NotApplicable' and category_fk = catID;

The select statements that populate the database look like this:

  select PK into wsID from RPT_WEBSVC where KEYVALUE = 'GetMachineNameList' and category_fk = catID;
  paramID := RPT_CONFIGURATION.ADD_PARAMETER( rptID, wsID, dtID, 'Machine', 'parameters.GetProductivityDataSet3.inserterid', 4, NULL, NULL, NULL, 0, 0, 0, 'Y', 'Y', 'N', 'N', 'Y' );

There are 13 more expression statements structured this way (I won't add them as they are all the same and the only difference is the stored values ​​that will go into the table.)

My problem is that when I run the installer I get this error in the logs after finishing:

ORA-01422: exact fetch returns more than requested number of rows 
ORA-06512: at line 30

What I would like to know, what exactly is the cause of this error, and what would be the ways to fix this error?

I did some research on this topic and found this to be a common topic in my search:

1. There is an error in the code, and the developer did not understand that you can get more than one line;

2. The data was compromised and not used by the API, so the verification was broken;

3. The software is fine, what the user did was OK, but there were two concurrent updates running at the same time and neither of them could see the uncommitted change that was made by the other, so it was not checked correctly.

I'm sure this is not # 2, but I don't quite understand what the other 2 causes exactly mean or how to fix them.

Any help or suggestions are greatly appreciated.

thank

+1


source to share


3 answers


ORA-01422 fine sampling returns more than requested number of rows

This exception is thrown whenever a SELECT INTO statement is executed and more than one row is found. The SELECT INTO statement expects to find exactly one row, no more or less — otherwise an exception is thrown.

In your example:

select PK into wsID from RPT_WEBSVC
where KEYVALUE = 'GetMachineNameList'
and category_fk = catID;


It seems like there should only be one line per (KEYVALUE, CATEGORY_FK) combination, but it really isn't. If there should be only one, then the table must have a unique constraint for these columns:

alter table RPT_WEBSVC add constraint RPT_WEBSVC_UK
    unique (KEYVALUE, CATEGORY_FK);

This will prevent someone (or some process) from adding the same line again. Of course, you will need to remove duplicate tables before you can add this constraint.

+6


source


This means that the SELECT INTO statement returns more than one row. This statement requires that the query only return one row. Otherwise, you must use a cursor loop to process the lines.



Check your select statements in SQL * Plus to see which one is of the offending query.

+4


source


I would consider number 1 as the cause of the problem here. Without seeing all the assertions, it's hard to tell which of these queries might return multiple rows, but this is a good place to start as the schema database can change ...

0


source






More articles:


All Articles