获得ORA-01422的原因:精确提取返回的行数超过了请求的行数 [英] Reason for geting ORA-01422: exact fetch returns more than requested number of rows

问题描述

因此，我正在研究一个安装程序，其中该安装程序连接到数据库并创建表并填充它们. 除非我尝试将行添加到certian表中，否则每个方面都可以正常工作.

So i am working on an installer where the installer connects to a database and creates tables and populates them. Every aspect of this works correctly except when i attempt 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;

填充数据库的select语句如下:

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' );

还有13条这样的选择语句(我不会添加它们，因为它们都是相似的，唯一的区别是将存储在表中的存储值.)

There are 13 more select statements structured like this (i won't add them since they are all similar and the only difference is the stored values that would go into the table.)

我的问题是，当我运行安装程序时，完成后会在日志中显示此错误:

My problem is that when i run the installer, i get this error in the logs upon completion:

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

我想知道的是此错误发生的确切原因是什么，而解决该错误的方法是什么?

What i would like to know is what exactly is the reason for this error to occur, and what would be the means to fix this error?

我已经对该主题进行了一些研究，发现这是我搜索的共同主题:

I've done some research on the topic, and found this to be the common theme of my search:

1.代码中有一个错误，开发人员没有意识到您可以返回不止一行；

1.There's a bug in the code and the developer did not realise that you could get more than one row returned;

2.数据被黑客入​​侵，而不是使用API​​，从而破坏了验证；

2.The data has been hacked rather than using the API so that validation has been broken;

3.该软件可以运行，用户所做的也可以，但是同时发生了两次并行更新，并且两个都看不到另一个所做的未提交的更改-因此未正确验证.

3.The software is OK, what the user did was OK, but two parallel updates occurred at the same time and neither could see the uncommitted change that the other did - hence not validated correctly.

我很肯定这不是#2，但我不太了解其他两个原因的确切含义或解决方法.

I'm positive it is not #2, but i do not quite understand what exactly the other 2 reasons mean, or how to fix them.

任何帮助或建议，我们将不胜感激.

Any help or suggestions are greatly appreciated.

谢谢

推荐答案

ORA-01422:精确访存返回的行数超过了请求的行数

ORA-01422: exact fetch returns more than requested number of rows

每当执行SELECT INTO语句并发现多个行时，都会引发此异常. SELECT INTO语句希望只找到一行，不多或少-否则会引发异常.

This exception is raised whenever a SELECT INTO statement is executed and finds more than one row. A SELECT INTO statement expects to find exactly one row, no more or less - otherwise an exception is raised.

在您的示例中:

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

似乎每个(KEYVALUE，CATEGORY_FK)组合应该只有一行，但实际上并非如此.如果只有一个，则该表在这些列上应具有唯一约束:

it appears that there should only be one row per (KEYVALUE, CATEGORY_FK) combination, but in fact that is not the case. If there should be only one then the table should have a unique constraint on those columns:

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

这将防止某人(或某些进程)再次添加同一行.当然，在添加该约束之前，您需要对表进行重复数据删除.

That would prevent someone (or some process) adding the same row again. Of course you would need to de-duplicate the table before you could add that constraint.

这篇关于获得ORA-01422的原因:精确提取返回的行数超过了请求的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！