Can some one helped me with this? I tried to execute the following procedure but it give me the same response:

ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "12960293.RECORDPAYMENT", line 6
ORA-06512: at line 1

I had tried to use distinct to eliminate the duplicates but it wont work. I was actually tring to record payment made by customer and refer to how much customers had owed, update the new amount owed in the invoice table and record payments made into the payment table.


-----------------------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE recordpayment(invoiceno IN NUMBER, amountpaid IN NUMBER)
AS tempinvoiceno d_payment.invoiceno%TYPE;

BEGIN
SELECT invoiceno
INTO tempinvoiceno
FROM d_invoice i
WHERE (i.invoiceno = invoiceno);

INSERT INTO d_payment
VALUES(GeneratePaymentSeqNo.nextval, SYSDATE, amountpaid, tempinvoiceno);

UPDATE d_invoice i
SET totalamount = (totalamount - amountpaid)
WHERE (invoiceno = i.invoiceno);

END;
/

I moved the topic here as more programmers visit this forum & i suggest u posting this in Programming forum as well.
However, where did u put the Distinct?

An 'INTO' in a SELECT cause doesn't look right.

You can use LIMIT 1 to only return the first row. What implentation of the SQL spec are you using?