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.

The Right and Wrong Ways to Add a NOT NULL Constraint

I tested these examples in an Oracle 11.2 database. The first one shows how to add a NOT NULL constraint retrospectively. You start by creating a table:

SQL> create table andrew (col1 varchar2(1))
  2  /

Table created.

SQL>

Then at some point in the future, you add a NOT NULL constraint like this: 

SQL> alter table andrew modify (col1 not null)
  2  /

Table altered.

SQL>

Doing it this way, the constraint is obvious when you describe the table:

SQL> desc andrew
Name                       Null?    Type
-------------------------- -------- ------------------
COL1                       NOT NULL VARCHAR2(1)

SQL>

… and, if you try to add a null value, the error message is self-explanatory:

SQL> insert into andrew values (null)
  2  /
insert into andrew values (null)
                           *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("ORACLE"."ANDREW"."COL1")

SQL>

The second example, which I saw recently, shows how NOT to do it. You start, as before, by creating a table:

SQL> create table fred (col1 varchar2(1))
  2  /

Table created.

SQL>

… then you add a CHECK constraint as follows:

SQL> alter table fred
  2  add constraint con1
  3  check (col1 is not null)
  4  /

Table altered.

SQL>

You cannot see this constraint when you describe the table:

SQL> desc fred
Name                       Null?    Type
-------------------------- -------- ------------------
COL1                                VARCHAR2(1)

SQL>

… and when you try to add a null value, the error message is not very helpful:

SQL> insert into fred values (null)
  2  /
insert into fred values (null)
*
ERROR at line 1:
ORA-02290: check constraint (ORACLE.CON1) violated

SQL>

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>

 

Constraints (Part 7) - On Delete Set Null

This example was tested on Oracle 9. It creates another foreign key constraint. First create emp and dept tables as before. Note the on delete set null clause when the employee table is created. We will see what it does later: 

SQL> create table dept
  2  (dept_no  varchar2(4) unique,
  3  dept_desc varchar2(10))
  4  /

Table created.

SQL> create table emp
  2  (emp_no     varchar2(4),
  3  emp_name    varchar2(10),
  4  emp_dept_no varchar2(4)
  5  constraint fk_dept_no
  6  references dept(dept_no)
  7  on delete set null)
  8  /

Table created.

SQL>

And create a department with one employee as in earlier examples: 

SQL> insert into dept values ('D001','IT')
  2  /

1 row created.

SQL> insert into emp values ('E001','Andrew','D001')
  2  /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dept
  2  /

DEPT DEPT_DESC
---- ----------
D001 IT

SQL> select * from emp
  2  /

EMP_ EMP_NAME   EMP_DEPT_NO
---- ---------- -----------
E001 Andrew     D001

SQL> 

Delete the IT department and see what happens to its one employee:

SQL> delete dept
  2  /

1 row deleted.

SQL> select * from dept
  2  /

no rows selected

SQL> 

The employee is still in the emp table but his department number has been set to null. As with the on delete cascade clause in the previous example, you need to be really sure that this is what you want to do:

SQL> select * from emp
  2  /

EMP_ EMP_NAME   EMP_DEPT_NO
---- ---------- -----------
E001 Andrew

SQL>

Rollback the transaction, set the employee's department number to be not null, then try again:

SQL> rollback;

Rollback complete.

SQL> alter table emp modify emp_dept_no not null
  2  /

Table altered.

SQL>

As you might expect, the delete dept statement fails because the employee's department number can no longer be set to null:

SQL> delete dept
  2  /
delete dept
      *
ERROR at line 1:
ORA-01407: cannot update
("ORACLE"."EMP"."EMP_DEPT_NO") to NULL

SQL> select * from dept
  2  /

DEPT DEPT_DESC
---- ----------
D001 IT

SQL> select * from emp
  2  /

EMP_ EMP_NAME   EMP_DEPT_NO
---- ---------- -----------
E001 Andrew     D001

SQL>

Constraints (Part 4) - ORA-02299

Go to part 3

This was tested on an Oracle 11.2 database. As I have already said, you can add a constraint when a table is created or you can modify an existing table as shown below: 

SQL> create table tab1 as

  2  select trunc(sysdate) today from dual

  3  /

 

Table created.

 

SQL> alter table tab1 modify today unique

  2  /

 

Table altered.

 

SQL> select constraint_name from user_constraints

  2  where table_name = 'TAB1'

  3  /

 

CONSTRAINT_NAME

------------------------------

SYS_C00153494

 

SQL>

You can use a system generated name for the constraint as shown above or you can choose a name yourself as shown below:

SQL> create table tab2 as

  2  select trunc(sysdate) today from dual

  3  /

 

Table created.

 

SQL> alter table tab2

  2  modify today constraint con2 unique

  3  /

 

Table altered.

 

SQL> select constraint_name from user_constraints

  2  where table_name = 'TAB2'

  3  /

 

CONSTRAINT_NAME

------------------------------

CON2

 

SQL>

If you try to add a unique constraint to a column with duplicate values, you get an ORA-02299:

SQL> create table tab3 as

  2  select trunc(sysdate) today from dual

  3  /

 

Table created.

 

SQL> insert into tab3 select * from tab3

  2  /

 

1 row created.

 

SQL> alter table tab3

  2  modify today constraint con3 unique

  3  /

modify today constraint con3 unique

                        *

ERROR at line 2:

ORA-02299: cannot validate (ORACLE.CON3) - duplicate

keys found

 

SQL>

To get round this, you have to delete the duplicate values:

SQL> delete from tab3 a

  2  where rowid >

  3  (select min(rowid) from tab3

  4   where today = a.today)

  5  /

 

1 row deleted.

 

SQL>

Then you can add the constraint without Oracle giving you an error:

SQL> alter table tab3

  2  modify today constraint con3 unique

  3  /

 

Table altered.

 

SQL>

Go to part 5

ORA-02260

This was tested on Oracle 11.2. I saw ORA-02260 for the first time today and decided to investigate further. If you want to ensure that more than 1 column holds unique values, you can do this by creating unique indexes or modifying the columns to be unique. However, if you try to explicitly set up more than one primary key on a table, you get this error:

 

SQL> create table andrews_table

  2  (col1 number,

  3   col2 number,

  4   col3 number,

  5   col4 number,

  6   col5 number,

  7   col6 number)

  8  /

 

Table created.

 

SQL> create unique index andrews_index1

  2  on andrews_table(col1)

  3  /

 

Index created.

 

SQL> create unique index andrews_index2

  2  on andrews_table(col2)

  3  /

 

Index created.

 

SQL> alter table andrews_table

  2  modify (col3 unique)

  3  /

 

Table altered.

 

SQL> alter table andrews_table

  2  modify (col4 unique)

  3  /

 

Table altered.

 

SQL> alter table andrews_table

  2  add constraint pk1 primary key(col5)

  3  /

 

Table altered.

 

SQL> alter table andrews_table

  2  add constraint pk2 primary key(col6)

  3  /

add constraint pk2 primary key(col6)

                   *

ERROR at line 2:

ORA-02260: table can have only one primary key

 

SQL>

Long to LOB Conversion

I went on an Oracle 11g release 2 seminar recently. They said that LONG data types are still supported but that Oracle recommends converting them to LOB (i.e. CLOB or NCLOB). I’m not totally convinced by this as they are still using LONG columns themselves e.g. in table SYS.VIEW$, which is one of the underlying tables for the DBA_VIEWS view:

 

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Dec 29 14:27:22 2011

 

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> desc sys.view$

Name                       Null?    Type

-------------------------- -------- ------------------

OBJ#                       NOT NULL NUMBER

AUDIT$                     NOT NULL VARCHAR2(38)

COLS                       NOT NULL NUMBER

INTCOLS                    NOT NULL NUMBER

PROPERTY                   NOT NULL NUMBER

FLAGS                      NOT NULL NUMBER

TEXTLENGTH                          NUMBER

TEXT                                LONG

 

SQL>

 

Converting a LONG to a CLOB is easy:

 

SQL> create table andrews_table

  2  (name varchar2(10),

  3   address long)

  4  /

 

Table created.

 

SQL> insert into andrews_table values

  2  ('Noddy', '10 High St, Anytown')

  3  /

 

1 row created.

 

SQL> alter table andrews_table modify (address clob)

  2  /

 

Table altered.

 

SQL> desc andrews_table

Name                       Null?    Type

-------------------------- -------- ------------------

NAME                                VARCHAR2(10)

ADDRESS                             CLOB

 

SQL> select * from andrews_table

  2  /

 

NAME       ADDRESS

---------- --------------------

Noddy      10 High St, Anytown

 

SQL>

 

But you need to be certain that you want to do this as there is no going back:

 

SQL> alter table andrews_table modify (address long)

  2  /

alter table andrews_table modify (address long)

                                  *

ERROR at line 1:

ORA-22859: invalid modification of columns

 

SQL>

How to Change a Column from Number to Varchar2

This post, tested on Oracle 9, shows how to change a column definition from number to varchar2. First, create a test table:

SQL> col my_letter format a9
SQL> col my_number format 999999999
SQL> create table andrew1
  2  (my_letter varchar2(1),
  3   my_number number(1))
  4  /

Table created.

SQL> desc andrew1
Name                    Null?    Type
----------------------- -------- ----------------
MY_LETTER                        VARCHAR2(1)
MY_NUMBER                        NUMBER(1)

SQL>

Then add three rows of data:

SQL> insert into andrew1 values('A',1)
  2  /

1 row created.

SQL> insert into andrew1 values('B',2)
  2  /

1 row created.

SQL> insert into andrew1 values('C',3)
  2  /

1 row created.

SQL> select * from andrew1
  2  /

MY_LETTER  MY_NUMBER
--------- ----------
A                  1
B                  2
C                  3

SQL>

Next, try to modify the number column. This will fail and display an appropriate error message:

SQL> alter table andrew1 modify(my_number varchar2(1))
  2  /
alter table andrew1 modify(my_number varchar2(1))
                           *
ERROR at line 1:
ORA-01439: column to be modified must be empty to
change datatype

SQL>

Create another table and store the values in the column to be modified along with their rowids: 

SQL> create table andrew2 as
  2  select rowid my_rowid, my_number from andrew1
  3  /

Table created.

SQL>

Now remove the values from the column being modified and try the modify command again. This time it works:

SQL> update andrew1 set my_number = null
  2  /

3 rows updated.

SQL> alter table andrew1 modify(my_number varchar2(1))
  2  /

Table altered.

SQL> desc andrew1
Name                    Null?    Type
----------------------- -------- ----------------
MY_LETTER                        VARCHAR2(1)
MY_NUMBER                        VARCHAR2(1)

SQL>

Finally, using the rowid, reinstate the values in the modified column:

SQL> update andrew1 x
  2  set my_number =
  3  (select my_number from andrew2
  4   where my_rowid = x.rowid)
  5  /

3 rows updated.

SQL> col my_number format a9
SQL> select * from andrew1
  2  /

MY_LETTER MY_NUMBER
--------- ---------
A         1
B         2
C         3

SQL>