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 scaleWe 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.