NO DATA FOUND in loop cursor

Question

I got 'No data found' error when I ran the following codes in PLSQL.

 declare

    v_firm_id number;
    amount number;

    begin

    OPEN MT_CURSOR FOR
    SELECT firm_id
    FROM t_firm;

    LOOP

    FETCH MT_CURSOR INTO v_firm_id;

    EXIT WHEN MT_CURSOR%NOTFOUND;

    Select sum(TRN_AMOUNT) into amount 
    from t_sales
    where FIRM_ID = v_firm_id;

    update t_firm
    set matching_amount = amount
    where firm_id = v_firm_id; 

    END LOOP;


 end;

Anything wrong with this codes? Could anyone let me know the correction? Thanks!

Solution

It appears that the problem is there is no data in the table T_SALES for one of the firms returned by the cursor. To handle this you need to include an handler for the NO_DATA_FOUND exception so that if there are not sales transactions for a customer the total sales amount is set to zero:

declare
  v_firm_id number;
  amount number;

begin
  OPEN MT_CURSOR FOR
    SELECT firm_id
    FROM t_firm;

  LOOP
    FETCH MT_CURSOR INTO v_firm_id;

    EXIT WHEN MT_CURSOR%NOTFOUND;

    BEGIN
      Select sum(TRN_AMOUNT) into amount 
        from t_sales
        where FIRM_ID = v_firm_id;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        AMOUNT := 0;
    END;

    update t_firm
      set matching_amount = amount
      where firm_id = v_firm_id; 

  END LOOP;
end;

Share and enjoy.

Comments

[crchin]

hi I could get sum(trn_amount) from that query if i run separately. I tried to print the amount as follow: dbms_output.put_line('amount: ' || amount); I got the output as follow: (by right if no transaction sales it will show 0 but seems like it's null) amount: (BLANK)

[Bob Jarvis - Слава Україні]

Change Select sum(TRN_AMOUNT) into amount to Select NVL(sum(TRN_AMOUNT), 0) into amount.