INCONSISTENCY UPDATING all salaries of a table-CodePudding

I'm trying to increase all salaries of the employee table with the following procedure. The problem is that the salary column is doing something weird, is working like a variable, because is saving the salary of the last employee in the cursor and adding up to the next employee, so, at the end I got an error ORA-01438: value larger than specified precision allowed for this column

    PROCEDURE increase_salaries AS
    v_emp NUMBER;
    v_sal NUMBER;
    BEGIN
        FOR r1 IN cur_emps LOOP
        v_emp := r1.employee_id;
        v_sal := r1.salary;
            UPDATE employees_copy
            SET
            salary = salary   salary;
            COMMIT;
               -- salary = salary   salary * v_salary_increase_rate;

        END LOOP;
    EXCEPTION 
        WHEN OTHERS  THEN
            print('Error in employee '||v_emp);
    END increase_salaries;

Thanks

I knwo that I can use first a SELECT INTO for the actual salary and re-initialized it to 0, but I saw many examples on internet using UPDATE salary = salary ... and it works but with my code does not work.

CodePudding user response：

If you have a table with:

CREATE TABLE employees_copy (
  salary NUMBER(6,2)
);

Then you can put values from -9999.99 to 9999.99 into the column. If you try to set a value that is outside those bounds then you will get the error:

ORA-01438: value larger than specified precision allowed for this column

You can either:

Use a smaller value; or
Increase the precision of the column using:
```
ALTER TABLE employees_copy MODIFY (salary NUMBER(7,2));
```
and then you can put values from -99999.99 to 99999.99 into the column.

However, you probably also need to fix your procedure to only update a single employee at each loop iteration (rather than updating all employees every loop iteration):

PROCEDURE increase_salaries AS
BEGIN
  FOR r1 IN cur_emps LOOP
    UPDATE employees_copy
    SET   salary = 2 * salary;
    WHERE employee_id = r1.employee_id;
  END LOOP;
END increase_salaries;
/

Note 1: Don't catch the OTHERS exception. Let the exception happen and then you can debug it.

Note 2: Don't COMMIT in the procedure; if you do then you cannot chain together multiple procedures and ROLLBACK them all if one fails. Instead, COMMIT from wherever you are calling the procedure.