Sunday, December 08, 2013

ORA-01441

This was tested on Oracle 11.2. I created a table with one VARCHAR2 column, which was 15 characters long:

SQL> create table tab1 (col1 varchar2(15))
  2  /
 
Table created.

SQL>

I inserted one row, which was 11 characters in length:

SQL> insert into tab1 values ('Christopher')
  2  /
 
1 row created.
 
SQL> select * from tab1
  2  /
 
COL1
---------------
Christopher

SQL> 

I tried to make the column 10 characters long. This failed, as you might expect, because the row I added earlier had 11 characters: 

SQL> alter table tab1 modify col1 varchar2(10)
  2  /
alter table tab1 modify col1 varchar2(10)
                        *
ERROR at line 1:
ORA-01441: cannot decrease column length because some
value is too big

SQL>

I found the row which was too long, made it a bit shorter then I was able to alter the table successfully. This seems reasonable as VARCHAR2 data is variable length:

SQL> select col1 from tab1 where length(col1) > 10
  2  /
 
COL1
---------------
Christopher
 
SQL> update tab1 set col1 = 'Chris'
  2  where col1 = 'Christopher'
  3  /
 
1 row updated.
 
SQL> alter table tab1 modify col1 varchar2(10)
  2  /
 
Table altered.
 
SQL> select * from tab1
  2  /
 
COL1
----------
Chris
 
SQL>

I did a similar test with a CHAR column. I found that I could not modify it at all unless the column was null. I guess this is because CHAR data is fixed length: 

SQL> create table tab1 (col1 char(15))
  2  /
 
Table created.
 
SQL> insert into tab1 values ('Chris')
  2  /
 
1 row created.
 
SQL> select * from tab1
  2  /
 
COL1
---------------
Chris
 
SQL> alter table tab1 modify col1 char(10)
  2  /
alter table tab1 modify col1 char(10)
                        *
ERROR at line 1:
ORA-01441: cannot decrease column length because some
value is too big
 
SQL> update tab1 set col1 = null
  2  /
 
1 row updated.
 
SQL> alter table tab1 modify col1 char(10)
  2  /
 
Table altered.
 
SQL>
 

No comments: