Exact fetch returns more than requested number of rows, PL/SQL

Question

THIS IS THE CODE THAT I TRIED

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

CREATE FUNCTION activeAccounts
RETURN NUMBER    IS
  accountNumber NUMBER;
BEGIN
  FOR rec IN (SELECT account_number, branch FROM ACCOUNT_DATA WHERE status='Active') 
  LOOP
    INSERT INTO ACTIVE_ACCOUNTS VALUES (rec.account_number,rec.branch);
  END LOOP;
END;
/

DECLARE
  accountStatus VARCHAR(20);
  inputuser_accountNumber NUMBER;
  inputuser_branch VARCHAR(20);
  cf varchar(20);
BEGIN
  inputuser_accountNumber := '&input_accountNumber';
  inputuser_branch := '&input_branch';
  SELECT status INTO accountStatus FROM ACCOUNT_DATA;
  IF (accountStatus = 'Active') THEN
    cf := activeAccounts();
  ELSE
    DBMS_OUTPUT.PUT_LINE('The account is Inactive.');
  END IF;
END;
/

AND THE PROBLEM STATEMENT IS

Write a stored function in PL/SQL for given requirement and use the same in PL/SQL block. Account no. and branch name will be accepted from user. The same will be searched in table acct_details. If status of account is active then display appropriate message and also store the account details in active_acc_details table, otherwise display message on screen “account is inactive”.

AND THE ERROR IS

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

ORA-06512: at line 9

1422. 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

Answer 1

There are few flaws in your code which can be corrected and your code will work. Firstly as per your problem statement:

Account no. and branch name will be accepted from user. The same will be searched in table acct_details.

This means your Select query must have some filtering condition to pick the unique record which user inputted.

Also there is no need to have a function in your code rather a procedure will be more suited. You need something like below:

--Passing the record to proc to store records to details table.
CREATE OR REPLACE PROCEDURE activeaccounts (v_acct_num number, v_brnch varchar2)
AS
BEGIN
    FOR rec IN (
        SELECT
            account_number,
            branch
        FROM
            account_data
        WHERE
            status = 'Active'
       and  acct_number =   v_acct_num
       And   branch = v_brnch)

    ) LOOP
        INSERT INTO active_accounts VALUES (
            rec.account_number,
            rec.branch
        );

    END LOOP;
END;
/

--Anonymous Block 
DECLARE
    accountstatus             VARCHAR(20);
    inputuser_accountnumber   NUMBER;
    inputuser_branch          VARCHAR(20);
    cf                        VARCHAR(20);
BEGIN
    inputuser_accountnumber := '&input_accountNumber';
    inputuser_branch        := '&input_branch';

    --As per your problem statement, your select statement must have account number and branch in where clause to pick one unique record.
    SELECT
        status
    INTO
        accountstatus
    FROM
        account_data
    Where acct_number =   inputuser_accountnumber;
    And   branch = inputuser_branch;

    IF
        ( accountstatus = 'Active' )
    THEN
       --Calling Proc to save records
       activeaccounts (inputuser_accountnumber,inputuser_branch);
    ELSE
        dbms_output.put_line('The account is Inactive.');
    END IF;

END;
/

Answer 2

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:\temp\test.sql
Enter the account number : 1
Enter the branch : x

PL/SQL procedure successfully completed.

SQL> sta d:\temp\test.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>