ORA-01440

I was asked to run some SQL today and it generated an ORA-01440. I did not remember having seen this error before so I decided to check it out in an Oracle 11.2 database. First I created a table and added a row of data to it:

SQL> create table tab1(col1 number)

  2  /

 

Table created.

 

SQL> insert into tab1 values(1)

 2  /

 

1 row created.

 

SQL>

Then I tried to change col1 to number(5), Oracle gave me an ORA-01440. The reason for this should be obvious:

SQL> alter table tab1 modify (col1 number(5))

  2  /

alter table tab1 modify (col1 number(5))

                         *

ERROR at line 1:

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

 

SQL>

 

I removed the data from the table and when I tried to modify it again, I did not get an error:

SQL> delete tab1

  2  /

 

1 row deleted.

 

SQL> alter table tab1 modify (col1 number(5))

  2  /

 

Table altered.

 

SQL>

Instead of removing the data, I could have set the value(s) to null:

 

SQL> drop table tab1

  2  /

 

Table dropped.

 

SQL> create table tab1(col1 number)

  2  /

 

Table created.

 

SQL> insert into tab1 values(1)

  2  /

 

1 row created.

 

SQL> alter table tab1 modify (col1 number(5))

  2  /

alter table tab1 modify (col1 number(5))

                         *

ERROR at line 1:

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

 

SQL> update tab1 set col1 = null

  2  /

 

1 row updated.

 

SQL> alter table tab1 modify (col1 number(5))

  2  /

 

Table altered.

 

SQL>

Whichever method you choose, you should consider saving the data beforehand and reinstating it afterwards.