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.
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()?