How to Resolve ORA-01440: column to be modified must be empty to decrease precision or scale

  • by

ORA-01440

ORA-01440 means that you can't decrease the number precision of the column values directly by modifying the definition of the column. Let's see a case that can reproduce the error by modifying the precision of a column directly.

1. Check the Column's Precision

SQL> desc hr.employees;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

As you can see, the column SALARY is defined with NUMBER data type as NUMBER(8,2). Which means the precision is 8 and the scale is 2 for decimal.

Now we want to decrease the precision of the column to 6, so we modify the column's definition directly like the following.

SQL> alter table hr.employees modify(salary number(6));
alter table hr.employees modify(salary number(6))
                                *
ERROR at line 1:
ORA-01440: column to be modified must be empty to decrease precision or scale

We saw ORA-01440: column to be modified must be empty to decrease precision or scale. So how to decrease the precision of a NUMBER column? Let's continue.

Solution to ORA-01440

The solution is to make a new column defined with the new precision NUMBER(6) to replace the original column.

1. Add a New Column

Of course, with a different name.

SQL> alter table hr.employees add (salary_1 number(6));

Table altered.

2. Migrate Data

We migrate all data from the original column to the new one.

SQL> update hr.employees set salary_1 = salary;

107 rows updated.

SQL> commit;

Commit complete.

Please note that, NOT NULL constraint can be applied on the new column after data is imported if there's any.

3. Drop the Old Column

We can drop or rename the old column. Here we choose dropping.

SQL> alter table hr.employees drop column salary;

Table altered.

4. Replace the Old Column

We replace the old column by renaming the new column into the old one.

SQL> alter table hr.employees rename column salary_1 to salary;

Table altered.

5. Check the Column's Precision

SQL> desc hr.employees;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)
 SALARY                                             NUMBER(6)

We have decreased the precision of the column.

The only drawback is that we changed the column order of the table, of course, we can re-order the column position to make it back.

In fact, the approach that we introduced above is the same as modifying a column from VARCHAR2 into CLOB.

Leave a Reply

Your email address will not be published. Required fields are marked *