Exact fetch returns more than requested number of rows

Question

I'm trying to create a procedure to pass in the customer number and return the number of purchases and total value for purchases within the last year. If there are no purchases, return zero for number and total and also return the number of contacts the salesmen had with that customer in the last year.

I'm calling it as follows:

DECLARE 
      a_Var NUMBER;
      b_Var NUMBER;
      C_Var NUMBER;
      D_Var NUMBER;    
BEGIN
      three_pr(001116,a_Var, b_Var);

    IF a_Var > 0 THEN
        DBMS_OUTPUT.PUT_LINE('the number of purchases :' || a_Var);
        DBMS_OUTPUT.PUT_LINE('the total value of purchase :' || b_Var);

    ELSE

        SELECT ContactID,Count(contactID)
        INTO
             C_Var,D_Var
        FROM    DD_Contacts
        WHERE  DateofContact  between to_date ('2012/01/01', 'yyyy/mm/dd')
               AND to_date ('2012/12/31', 'yyyy/mm/dd')
      Group By ContactID;

       DBMS_OUTPUT.PUT_LINE (C_Var||D_Var);

    END IF;

END;
/

When using the above code I get the error:

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

and here's the procedure:

CREATE or REPLACE PROCEDURE three_pr

(par_CustomerID IN NUMBER, par_sumpurchase OUT Number,par_totalvalue OUT Number)
IS
BEGIN

        SELECT 
            COUNT(O.OrderID),SUM(Price*Quantity)
        INTO par_sumpurchase,par_totalvalue
            FROM DD_Orders O JOIN DD_OrderLine OL ON O.OrderID = OL.OrderID
            WHERE  DatePurchase  between to_date ('2012/01/01', 'yyyy/mm/dd')
            AND to_date ('2012/12/31', 'yyyy/mm/dd')
            AND CustomerID = par_CustomerID;

END;
/

Hi, and welcome to Stack Overflow. Please always explain exactly what's wrong, including and error messages etc. It's also advisable to stay around and answer any questions people might have; things happen fast around here. Please also make an attempt to create a title that means something... — Ben
Though you haven't given any indication (at all) of what might be incorrect I'm going to guess that you have a TOO_MANY_ROWS (or NO_DATA_FOUND) exception raised by your anonymous block. You're grouping by CONTACT_ID, which means more than one row can be returned but you're placing the returned data into standard numbers rather than arrays of some description. — Ben
So it's as I suggested, in your anonymous block you're selecting multiple rows using an exact fetch... How many rows do you want returned from the query? — Ben

elhobbs elhobbs · Accepted Answer · 2013-11-12T18:56:58

The procedure three_pr does not correctly handle the NO_DATA_FOUND exception when the query does not return results.

    CREATE or REPLACE PROCEDURE three_pr

    (par_CustomerID IN NUMBER, par_sumpurchase OUT Number,par_totalvalue OUT Number)
    IS
    BEGIN
        BEGIN
            SELECT 
                COUNT(O.OrderID),SUM(Price*Quantity)
            INTO par_sumpurchase,par_totalvalue
                FROM DD_Orders O JOIN DD_OrderLine OL ON O.OrderID = OL.OrderID
                WHERE  DatePurchase  between to_date ('2012/01/01', 'yyyy/mm/dd')
                AND to_date ('2012/12/31', 'yyyy/mm/dd')
                AND CustomerID = par_CustomerID;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                par_sumpurchase := 0;
                par_totalvalue := 0;
        END;

    END;
    /

Exact fetch returns more than requested number of rows

1 Answers