Ошибка ora 01422 точная выборка возвращает количество строк больше запрошенного

A SELECT INTO statement will throw an error if it returns anything other than 1 row. If it returns 0 rows, you’ll get a no_data_found exception. If it returns more than 1 row, you’ll get a too_many_rows exception. Unless you know that there will always be exactly 1 employee with a salary greater than 3000, you do not want a SELECT INTO statement here.

Most likely, you want to use a cursor to iterate over (potentially) multiple rows of data (I’m also assuming that you intended to do a proper join between the two tables rather than doing a Cartesian product so I’m assuming that there is a departmentID column in both tables)

BEGIN
  FOR rec IN (SELECT EMPLOYEE.EMPID, 
                     EMPLOYEE.ENAME, 
                     EMPLOYEE.DESIGNATION, 
                     EMPLOYEE.SALARY,  
                     DEPARTMENT.DEPT_NAME 
                FROM EMPLOYEE, 
                     DEPARTMENT 
               WHERE employee.departmentID = department.departmentID
                 AND EMPLOYEE.SALARY > 3000)
  LOOP
    DBMS_OUTPUT.PUT_LINE ('Employee Nnumber: ' || rec.EMPID);
    DBMS_OUTPUT.PUT_LINE ('---------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE ('Employee Name: ' || rec.ENAME);
    DBMS_OUTPUT.PUT_LINE ('---------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE ('Employee Designation: ' || rec.DESIGNATION);
    DBMS_OUTPUT.PUT_LINE ('----------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE ('Employee Salary: ' || rec.SALARY);
    DBMS_OUTPUT.PUT_LINE ('----------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE ('Employee Department: ' || rec.DEPT_NAME);
  END LOOP;
END;

I’m assuming that you are just learning PL/SQL as well. In real code, you’d never use dbms_output like this and would not depend on anyone seeing data that you write to the dbms_output buffer.

In my previous articles, I have given the proper idea of different oracle errors, which are frequently come. In this article, I will try to explain another common error, which has been searched on google approximately 10 k times per month. ORA-01422 is another simple error, which is commonly come in database when select into statement when it retrieves more than one row. All oracle errors are categorized in to two types one is network and memory issues and other are syntax errors come due to bad syntax. ORA-01422 is user-initiated mistake resulting from either a typo or a misunderstanding of how Oracle functions may work.

Cause and resolution of this error:

The ORA-01422 error is most common error, which will come because of the multiple reasons .The main reason of this error, is ‘SELECT INTO’ statement. Oracle has one important inbuilt error named ‘TOO_MANY_ROWS’ error.

1.More than requested rows in “Select into”:

If ‘Select Into’ statement returns more than one rows in variable then this error will come. The error says fetch returns more than requested number of rows means ‘Exact Fetch’ will return ‘More than one row’. The basic problem will come from ‘Select Into’ statement from oracle, which will return more than one rows. The select statement will fetch the record from multiple databases and multiple tables and it will store into variable. The Oracle engine will fetch more than one record for single specified row. The ORA-01422 error will trigger when PLSQL engine returns multiple rows of data. The select into statement has default setting, which is designed to retrieve one row, but when it retrieves more than one rows, the ORA-01422 error will trigger.

Consider following real life example:

If table named ‘test_error’ has more than one records and user tries to fetch all records in to one variable this error will come.

Procedure :

DECLARE

v_test VARCHAR2(30);

BEGIN

SELECT roll_no INTO v_test FROM test_error;    —-Error statement

end;

Output:

Error report:

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

ORA-06512: at line 4

01422. 00000 –  “exact fetch returns more than requested number of rows”

*Cause:    The number specified in exact fetch is less than the rows returned.

*Action:   Rewrite the query or change number of rows requested

Resolution of this error:

Handle the exception:

Just make the small change in the specified code and write the exception block for the same.

Procedure :

DECLARE

V_SRNUM VARCHAR2(20);

DECLARE

v_test VARCHAR2(30);

BEGIN

SELECT roll_no INTO v_test FROM test_error;

exception WHEN too_many_rows THEN

dbms_output.put_line(‘Errors fetching are more than one’);

end;

The above procedure will handle the error and anonyms block will complete successfully.So if this kind of error will occure handle user defined exception named ‘TOO_MANY_ROWS’.

2. Add cursor with loop:

There are many primitive adjustments to resolve this error. The approach needs to be chosen by the user dependent on the database tables and scenarios. The error will occur because of multiple rows are returning so user will change the code by adding the cursor.

Therefore, the above procedure will be:

Declare

v_test VARCHAR2(30);

begin

for c in (SELECT roll_no INTO v_test FROM test_error)

loop

v_test := c.roll_no;

end loop;

end;

3. Recommend to use aggregate function:

This error will come because of multiple rows selection. Therefore, as per requirement if user uses the aggregate function like sum, count then it will fetch only one row.

Therefore, the above procedure will be:

DECLARE

v_test VARCHAR2(30);

BEGIN

SELECT count(roll_no) INTO v_test FROM test_error;    —-Error statement

end;

4. Use of bulk collect:

This will pull more rows and variables but in a concise manner. However, be wary of using BULK COLLECT excessively as it can use a great deal of memory.

So there are different ways to deal with this error, my best recommendation is to use the cursor for fetching more than one rows and processing it.

A function is meant to return something, while you need to do something, without returning anything, so I would interpret the requirement
as «build a stored procedure».

Now, say you have your procedure, it only has to look for some specific account branch and number, so it would need some input parameters.
Then this procedure should check the (unique?) row in the table to get the status of the account (a select with a WHERE condition based on the values of branch and account number).
Once known the status, the procedure simply has to print a message or do an insert.

With tables like the following

create table ACCOUNT_DATA(account_number, branch, status) as (
    select 1, 'x', 'Active'   from dual union all
    select 2, 'x', 'Inactive' from dual 
)   

create table active_accounts (account_number number, branch varchar2(10))

you could create a procedure like this:

create or replace procedure checkAccount(p_acc_number IN number, p_acc_branch IN varchar2) is
    v_status varchar2(10);
begin
    -- get the status, assuming that the couple (account_number, and branch) is a key for the table 
    select status
    into v_status
    from ACCOUNT_DATA
    where account_number = p_acc_number
      and branch = p_acc_branch;
    -- check the status
    if v_status = 'Active' then
        insert into active_accounts
        values (p_acc_number, p_acc_branch);
    else
       dbms_output.put_line('The account is Inactive.');
    end if;
end;     

Your script could be (test.sql):

SET SERVEROUTPUT ON;
ACCEPT input_accountNumber NUMBER PROMPT 'Enter the account number : '
ACCEPT input_branch CHAR PROMPT 'Enter the branch : '

begin
    checkAccount('&input_accountNumber', '&input_branch');
end;    
/

How it works:

SQL> select * from active_accounts;

no rows selected

SQL> sta d:temptest.sql
Enter the account number : 1
Enter the branch : x

PL/SQL procedure successfully completed.

SQL> sta d:temptest.sql
Enter the account number : 2
Enter the branch : x
The account is Inactive.

PL/SQL procedure successfully completed.

SQL> select * from active_accounts;

ACCOUNT_NUMBER BRANCH
-------------- ----------
             1 x

SQL>

A function is meant to return something, while you need to do something, without returning anything, so I would interpret the requirement
as «build a stored procedure».

Now, say you have your procedure, it only has to look for some specific account branch and number, so it would need some input parameters.
Then this procedure should check the (unique?) row in the table to get the status of the account (a select with a WHERE condition based on the values of branch and account number).
Once known the status, the procedure simply has to print a message or do an insert.

With tables like the following

create table ACCOUNT_DATA(account_number, branch, status) as (
    select 1, 'x', 'Active'   from dual union all
    select 2, 'x', 'Inactive' from dual 
)   

create table active_accounts (account_number number, branch varchar2(10))

you could create a procedure like this:

create or replace procedure checkAccount(p_acc_number IN number, p_acc_branch IN varchar2) is
    v_status varchar2(10);
begin
    -- get the status, assuming that the couple (account_number, and branch) is a key for the table 
    select status
    into v_status
    from ACCOUNT_DATA
    where account_number = p_acc_number
      and branch = p_acc_branch;
    -- check the status
    if v_status = 'Active' then
        insert into active_accounts
        values (p_acc_number, p_acc_branch);
    else
       dbms_output.put_line('The account is Inactive.');
    end if;
end;     

Your script could be (test.sql):

SET SERVEROUTPUT ON;
ACCEPT input_accountNumber NUMBER PROMPT 'Enter the account number : '
ACCEPT input_branch CHAR PROMPT 'Enter the branch : '

begin
    checkAccount('&input_accountNumber', '&input_branch');
end;    
/

How it works:

SQL> select * from active_accounts;

no rows selected

SQL> sta d:temptest.sql
Enter the account number : 1
Enter the branch : x

PL/SQL procedure successfully completed.

SQL> sta d:temptest.sql
Enter the account number : 2
Enter the branch : x
The account is Inactive.

PL/SQL procedure successfully completed.

SQL> select * from active_accounts;

ACCOUNT_NUMBER BRANCH
-------------- ----------
             1 x

SQL>

Вопрос:

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

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

Лучший ответ:

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

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

Ответ №1

Ответ №2

Я бы рассмотрел номер 1 как причину проблемы здесь. Не видя всех утверждений, трудно сказать, какой из этих запросов может возвращать несколько строк, но это хорошее место для запуска, поскольку база данных схемы могут меняться…