ALTER TABLE MODIFY column: Modify column datatype, scale, default values, visibility, NULL check in oracle

ALTER TABLE command helps to modify table properties. Similarly, the alter table modify column helps to change column datatype to another datatype.  Apart from that, it also helps to set default values, add NULL checks to an existing column.

In this Oracle Tutorial, we will go through each possibility as well as it’s syntax. So, let us get into it.

How to Modify Column Datatype In Oracle?

Syntax to modify column datatype

ALTER TABLE table_name MODIFY column_name new_datatype;

When we talk about changing the datatype of an existing column, There are two things come into play. One is the type, Another one is the size of the data. The size of the data decides how much Maximum memory will be allocated to store the value in that datatype or in that column.

Now let us create a table, then we will try to use alter table modify column command to change the datatype from one type to another or to increase the size of the same datatype.

Setup for examples

SQL> create table visitor_t (
  2      ip           varchar2(10)
  3    , first_name   varchar2(30) --not null
  4    , last_name    varchar2(30)
  5    , height       number(4)
  6    , gender       char(1)
  7    , region       varchar2(200) --default Unknown
  8    , full_name    varchar2(500) 
  9        generated always as ( first_name || ' ' || last_name )
 10    , primary key ( ip )
 11  );

SQL> desc visitor_t;
Name       Null?    Type 
---------- -------- ------------- 
IP         NOT NULL NUMBER(10) 
FIRST_NAME          VARCHAR2(30) 
LAST_NAME           VARCHAR2(30) 
HEIGHT              NUMBER(4) 
GENDER              CHAR(1) 
REGION              VARCHAR2(200) 
FULL_NAME           VARCHAR2(500)

We inserted some dummy rows into the table.

Alter table modify column datatype to another datatype example

SQL> alter table visitor_t
  2   modify gender varchar2(200);

Table VISITOR_T altered.

SQL> desc visitor_t;
Name       Null?    Type          
---------- -------- ------------- 
IP         NOT NULL NUMBER(10)     
FIRST_NAME          VARCHAR2(30)   
LAST_NAME           VARCHAR2(30) 
HEIGHT              NUMBER(4)     
GENDER              VARCHAR2(200) 
REGION              VARCHAR2(200) 
FULL_NAME           VARCHAR2(500)

In the above example, you can see that we have to change the Data type of gender column from CHAR(1) to VARCHAR2(200). Hence it just worked.

Now, one question might be coming to your mind is, if it will work all the time. Can we change the datatype of it from one type to another irrespective of the column is having any data are not? No, we cannot always do that. It depends on the data in the column, and oracle rules to maintain data integrity. Let us see some of the errors while executing the alter table modify command.

Let us try to convert IP column datatype. As it is of NUMBER(3), We will basically try to convert NUMBER data type to VARCHAR2.

SQL> alter table visitor_t
  2   modify ip varchar2(200);
Error report -
ORA-01439: column to be modified must be empty to change datatype

SQL> alter table visitor_t
2 modify height number(2,2);
Error report -
ORA-01440: column to be modified must be empty to decrease precision or scale

SQL> alter table visitor_t
2 modify first_name char(200);
Error report -
ORA-54033: column to be modified is used in a virtual column expression

Also, please check.

• How to alter or modify NUMBER datatype to add precision of a nonempty table?
• How to alter modify virtual column definition?

Alter table modify multiple columns datatypes at once

Sometimes, we want to change for more than one column data type at once with alter table modify column command. We can do this by using the following syntax.

Modify multiple columns datatype syntax

alter table table_name modify(
  column_name datatype,
  column_name2 datatype,
  ...
);

Now let’s try to apply the concept in the above visitor_t table. We will change the data type of first name and last name from VARCHAR2 (30) to VARCHAR2 (100).

SQL> alter table visitor_t modify (
2    first_name varchar2(100)
3  , last_name varchar2(100)
4 );

Error report -
ORA-54031: column to be dropped or modified is used in a virtual column expression

Basically, we cannot change a Column Datatype which is the base column for any virtual column. So there are two ways to handle this.

1. Firstly, we can drop the virtual column which is dependent on the columns we want to change the datatype of.
2. Secondly, we can change the expression make it independent of the columns which we are going to change the datatype.

We will drop the virtual column and try altering the table to modify the base columns in this case.

SQL> alter table visitor_t drop column full_name;
Table VISITOR_T altered.

SQL> alter table visitor_t modify (
2 first_name varchar2(100)
3 , last_name varchar2(100)
4 );
Table VISITOR_T altered.

Now is the Successfully altered The first name and last name columns. Lets check their data type by using the desc command.

SQL> desc visitor_t;
Name       Null?    Type 
---------- -------- ------------- 
IP NOT NULL         NUMBER(10) 
FIRST_NAME          VARCHAR2(100) 
LAST_NAME           VARCHAR2(100) 
HEIGHT              NUMBER(4) 
GENDER              CHAR(1) 
REGION              VARCHAR2(200)

Also, please see.

• How to check if a column is virtual in Oracle?

Now, let us recreate the FULL_NAME virtual column.

alter table visitor_t 
  add full_name varchar2(500) 
  generated always 
    as ( first_name || ' ' || last_name );

Alter table modify the column to change the scale

Alter table modify column command can help to increase or decrease the scale of the datatype. So, we are going to decrease the scale of the IP column from 10 to 3 in this data type. Please note that we must not be having any data greater than new scale in the IP column else Oracle will complain about the alter command.

SQL> alter table visitor_t modify ip number(3);
Table VISITOR_T altered.

SQL> desc visitor_t;
Name       Null?    Type          
---------- -------- ------------- 
IP         NOT NULL NUMBER(3)     
FIRST_NAME          VARCHAR2(100) 
LAST_NAME           VARCHAR2(100) 
HEIGHT              NUMBER(4)     
GENDER              CHAR(1)       
REGION              VARCHAR2(200) 
FULL_NAME           VARCHAR2(500)

Alter table modify column to make it visible or invisible

Every column has the invisible or visible property associated with it. By default, every column is visible in Oracle. So, we will try to make the full name that is the virtual column invisible.

First of all, let us See the number of columns using the describe command. You can also use this select query to see what data are coming or to see the columns.

SQL> desc visitor_t;
Name       Null?    Type          
---------- -------- ------------- 
IP         NOT NULL NUMBER(3)     
FIRST_NAME          VARCHAR2(100) 
LAST_NAME           VARCHAR2(100) 
HEIGHT              NUMBER(4)     
GENDER              CHAR(1)       
REGION              VARCHAR2(200) 
FULL_NAME           VARCHAR2(500)

SQL> select * from visitor_t;
 
SQL> alter table visitor_t
  2   modify full_name invisible;
Table VISITOR_T altered.

SQL> desc visitor_t;
Name       Null?    Type          
---------- -------- ------------- 
IP         NOT NULL NUMBER(3)     
FIRST_NAME          VARCHAR2(100) 
LAST_NAME           VARCHAR2(100) 
HEIGHT              NUMBER(4)     
GENDER              CHAR(1)       
REGION              VARCHAR2(200) 

SQL> select * from visitor_t;

SQL> alter table visitor_t
  2   modify full_name visible;
Table VISITOR_T altered.

SQL> desc visitor_t;
Name       Null?    Type          
---------- -------- ------------- 
IP         NOT NULL NUMBER(3)     
FIRST_NAME          VARCHAR2(100) 
LAST_NAME           VARCHAR2(100) 
HEIGHT              NUMBER(4)     
GENDER              CHAR(1)       
REGION              VARCHAR2(200) 
FULL_NAME           VARCHAR2(500)

Alter table modify column to set NULL check

We can also add NOT NULL or NULL constraint to a column using alter table modify column command.

SQL> alter table visitor_t
2 modify first_name not null;

Error report -
ORA-02296: cannot enable (HR.) - null values found
02296. 00000 - "cannot enable (%s.%s) - null values found"
*Cause: an alter table enable constraint failed because the table
contains values that do not satisfy the constraint.
*Action: Obvious

Here we cannot change or modify the first name column as it has null data in it. One way to solve it is basically will replace the Nall by executing an update statement.

SQL> update visitor_t
2 set first_name = 'NA' 
3 where first_name is null;
2 rows updated.

SQL> commit;
Commit complete.

Now if you query the table, You will see that all the Null in the first name column Is updated.

SQL> select * from visitor_t;

Now as the column doesn’t have any Null in it, we are good to alter it and add NOT NULL check on it. So, lets to that.

SQL> alter table visitor_t
2 modify first_name not null;
Table VISITOR_T altered.

SQL> desc visitor_t; 
Name       Null?    Type 
---------- -------- ------------- 
IP         NOT NULL NUMBER(3) 
FIRST_NAME NOT NULL VARCHAR2(100) 
LAST_NAME           VARCHAR2(100) 
HEIGHT              NUMBER(4) 
GENDER              CHAR(1) 
REGION              VARCHAR2(200) 
FULL_NAME           VARCHAR2(500)

Alter table modify column to set a default value

We can set a Default Value to a column By using the altar table modify Column. All you need to do is basically add the default clause after the alter table modify statement.

SQL> alter table visitor_t modify
2 height number(4) default 9999;
Table VISITOR_T altered.

Once you have successfully altered it, If you try to insert into the table but not to that column, It will basically insert the default value. In the following, we have omitted height, so it inserted 9999 to that column.

SQL> insert   into visitor_t (
  2      ip, first_name, last_name, 
  3      gender, region
  4  ) values (
  5      4, 'Roz', 'Mishra', 
  6      'F', 'India'
  7  );
1 row inserted.

SQL> commit;
Commit complete.

SQL> select * from visitor_t;

Alter table modify column { BONUS } example

Here is a bonus example for you. We are going to decrease the data size or scale of first name column lesser than the actual data size in it. Let us try and see what will happen in this case.

SQL> alter table visitor_t 
  2    modify first_name varchar2(3);

Error report -
ORA-54031: column to be dropped or modified is used in a virtual column expression

We need to drop or change the virtual column expression so that we can alter the base columns.

SQL> alter table visitor_t drop column full_name; 
Table VISITOR_T altered.

SQL> alter table visitor_t 
2 modify first_name varchar2(3);

Error starting at line : 101 in command -
alter table visitor_t 
  modify first_name varchar2(3)
Error report -
ORA-01441: cannot decrease column length because some value is too big
01441. 00000 -  "cannot decrease column length because some value is too big"
*Cause:    
*Action:

Now also, Oracle complains that it can not alter. The reason is the first_name column has some data which is bigger than the new datatype. So, let us use oracle substr function to update the first_name data. Once that is done, we will be able to alter the column.

SQL> update visitor_t
  2    set first_name = substr(first_name,1,3) 
  3    where length(first_name) > 3;
1 row updated.

SQL> commit;
Commit complete.

SQL> alter table visitor_t 
  2    modify first_name varchar2(3);
Table VISITOR_T altered.

SQL> desc visitor_t;
Name       Null?    Type          
---------- -------- ------------- 
IP         NOT NULL NUMBER(3)     
FIRST_NAME NOT NULL VARCHAR2(3)   
LAST_NAME           VARCHAR2(100) 
HEIGHT              NUMBER(4)     
GENDER              CHAR(1)       
REGION              VARCHAR2(200)

Also see, alter table modify column definition in MySQL.

Briefly let us note so that we do not forget.

ALTER TABLE MODIFY column Oracle Board