Thursday, September 17, 2015

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.

No comments: