Why I receive "NO DATA FOUND" exception when I use RETURNING clause after update statement?

Question

I'm coding a simple LOOP FOR to update a column from a table using information populated in an associative array. All seems right when I only use UPDATE statement but when I add a RETURNING clause I receive "NO DATA FOUND" error. Thanks!

DECLARE
  TYPE emps_info IS TABLE OF employees23%ROWTYPE
    INDEX BY PLS_INTEGER;

  t_emps_current_info emps_info;
  t_emps_new_info     emps_info;
BEGIN
  SELECT *
  BULK COLLECT INTO t_emps_current_info
  FROM   employees;

  FOR emps_index IN t_emps_current_info.FIRST .. t_emps_current_info.LAST
  LOOP
    IF
      NVL(t_emps_current_info(emps_index).commission_pct, 0) = 0 THEN
        UPDATE employees23
        SET    commission_pct = 0.3
        WHERE  employee_id = t_emps_current_info(emps_index).employee_id;
    ELSIF
      t_emps_current_info(emps_index).commission_pct BETWEEN 0.1 AND 0.3 THEN
        UPDATE employees23
        SET    commission_pct = 0.5
        WHERE  employee_id = t_emps_current_info(emps_index).employee_id;
    END IF;
  END LOOP;
END;

Now When I add RETURNING clause I receive following error:

DECLARE
  TYPE emps_info IS TABLE OF employees23%ROWTYPE
    INDEX BY PLS_INTEGER;

  t_emps_current_info emps_info;
  t_emps_new_info     emps_info;
BEGIN
  SELECT *
  BULK COLLECT INTO t_emps_current_info
  FROM   employees;

  FOR emps_index IN t_emps_current_info.FIRST .. t_emps_current_info.LAST
  LOOP
    IF
      NVL(t_emps_current_info(emps_index).commission_pct, 0) = 0 THEN
        UPDATE employees23
        SET    commission_pct = 0.3
        WHERE  employee_id = t_emps_current_info(emps_index).employee_id
        RETURNING commission_pct
        INTO      t_emps_new_info(emps_index).commission_pct;
    ELSIF
      t_emps_current_info(emps_index).commission_pct BETWEEN 0.1 AND 0.3 THEN
        UPDATE employees23
        SET    commission_pct = 0.5
        WHERE  employee_id = t_emps_current_info(emps_index).employee_id
        RETURNING commission_pct
        INTO      t_emps_new_info(emps_index).commission_pct;
    END IF;

    DBMS_OUTPUT.PUT_LINE('EMPLOYEE_ID: '                                || 
                         t_emps_current_info(emps_index).employee_id    ||
                         ' OLD COMMISSION: '                            || 
                         NVL(t_emps_current_info(emps_index).commission_pct, 0) 
                         || ' NEW COMMISSION: '                            ||
                         t_emps_new_info(emps_index).commission_pct);
  END LOOP;
END;

Informe de error - ORA-01403: no data found ORA-06512: at line 22 01403. 00000 - "no data found" *Cause: No data was found from the objects. *Action: There was no data from the objects which may be due to end of fetch.

Answer 1

I'm not sure why it should be necessary, but I would change each UPDATE statement return the value into a simple local variable. Then I'd set the table record field to the simple variable.

Probably unrelated, but I noticed that your ELSIF test isn't doing an NVL() on the existing t_emps_current_info(emps_index).commission_pct, as your IF test does.

If it was me, I'd log the updates of both sides of the IF-ELSEIF to see if one update is working while the other fails. Maybe it's just failing on the ELSIF, because of the missing NVL()?