Syntax Error On A Procedure ORA-01422

Question

As a beginner, I am not able to see any error logically. All I am doing here is saving data into a variable and comparing that in if statements. However, at some point it returns more than requested rows and causes error.

Thanks in advance for your help.

CREATE OR REPLACE PROCEDURE Late_Payment_Days(Inv_ID Varchar2) 
AS
    Payment_Exist Number;
    Late_Payment Number;
BEGIN
    DBMS_OUTPUT.Enable();
    SELECT Payment_Total 
    INTO Payment_Exist 
    FROM Invoices Where Invoice_ID = Inv_ID;

    SELECT 
        ( (SELECT PAYMENT_DATE FROM INVOICES WHERE INVOICE_ID = Inv_ID) 
          - (SELECT INVOICE_DUE_DATE FROM INVOICES WHERE INVOICE_ID= Inv_ID)
        )
    INTO Late_Payment 
    FROM Invoices;

    IF Payment_Exist=0 THEN
        DBMS_OUTPUT.PUT_LINE('No Payment!');

        IF Late_Payment > 0 THEN
            DBMS_OUTPUT.PUT_LINE(Late_Payment || 'Overdue Days');
        ELSE
            DBMS_OUTPUT.PUT_LINE('Zero overdue days');
        END IF;
    END IF;
End;
/

Show Error;

EXEC Late_Payment_Days(5);

Answer 1

I think that this part is responsible for the error;

SELECT
    ( (SELECT PAYMENT_DATE FROM INVOICES WHERE INVOICE_ID = Inv_ID) 
      - (SELECT INVOICE_DUE_DATE FROM INVOICES WHERE INVOICE_ID= Inv_ID)
    )
INTO Late_Payment 
FROM Invoices;

If there is more than 1 row in Invoices, the result of the query will be many rows (with the same value) and we can only insert one value into the variable Late_Payment.

You can change the FROM Invoices to FROM dual (a special Oracle table with a single row):

SELECT
    ( (SELECT PAYMENT_DATE FROM INVOICES WHERE INVOICE_ID = Inv_ID) 
      - (SELECT INVOICE_DUE_DATE FROM INVOICES WHERE INVOICE_ID= Inv_ID)
    )
INTO Late_Payment 
FROM dual ;

or - since both subqueries use the same FROM and WHERE - simplify to:

SELECT PAYMENT_DATE - INVOICE_DUE_DATE 
INTO Late_Payment
FROM INVOICES 
WHERE INVOICE_ID = Inv_ID ;