This simple article describes how Oracle change column data type in different ways. First, I will explain simple syntax for changing column properties. Then, I plan to explain how to change column data type with data or in a production environment.
According to oracle you can change column constraints, length, data type, not null constraints, default values on insert and many other properties of a column. Here focus is to oracle change column data type. Before that lets understand how to use ALTER TABLE.
-- Sample table for Oracle Change Column Data Type
CREATE TABLE employee_table(
employee_id VARCHAR2(50) ,
employee_nic VARCHAR2(50) ,
employee_name VARCHAR2(100) ,
manager_id VARCHAR2(50) ,
department_id VARCHAR2(50) ,
PRIMARY KEY(employee_id)
);
--Change the size of the datatype in SQL
If you need to change the size of a column mostly in VARCHAR2, there is a small code block able to change it, but if there is data with a size greater with compared to new size, an error is returned, something like below
-- SQL Error: ORA-01441: cannot decrease column length because some value is too big ALTER TABLE employee_table MODIFY employee_name VARCHAR2( 100 ); --
Change the column datatype to number from varchar2
If you want to change column type from one to another, you can’t delete and create a column in a single execution. Because then you will lose the data in the column. Therefore, you need to add a column first and then copy the data second. Then you can copy all the data from the old column to the new column.
Then you make sure all the data is copied. because data is are main important thing. You can roll back this process again. After that, you can delete the old column. After that, rename the new column to an old name like below in the code.
-- alter table employee_table add (ColATemp NUMBER); update employee_table set ColATemp = employee_nic; alter table employee_table drop column employee_nic; ALTER TABLE employee_table RENAME COLUMN ColATemp TO employee_nic; --
This is the best way to change the Oracle column data type. If you try to change the type directly below the error message will raise if you have data in the column. Therefore code below only works if a column is empty.
-- alter table employee_table modify employee_nic NUMBER * ERROR at line 1: ORA-01439: column to be modified must be empty to change datatype --
Oracle change column data type from number to varchar2
The earlier example shows changing column type from VARCHAR2 to NUMBER, this is the other way round. But the steps are similar. Just follow the same steps as below.
-- alter table employee_table add (test1_new varchar2(100)); update employee_table set test1_new = to_char(employee_nic); alter table employee_table drop column employee_nic cascade constraints; alter table employee_table rename column test1_new to employee_nic; --
Oracle change column data type DATE columns to TIMESTAMP with timezone
This is also possible and useful when working with dates and times. Sometimes, when you need to upgrade the column with TIMEZONE, you have to follow the same steps and additionally need to cast values with the new datatype. The example below is backward compatible, then you can change the column date to timestamp and timestamp to date.
-- ALTER TABLE employee_table ADD date_as_timestamp TIMESTAMP WITH TIME ZONE; UPDATE employee_table SET date_as_timestamp = CAST(date_column AS TIMESTAMP WITH TIME ZONE); ALTER TABLE employee_table DROP COLUMN date_column ; ALTER TABLE employee_table RENAME COLUMN date_as_timestamp TO date_column ; --
Oracle change column data type from VARCHAR2 column to CLOB
I think now you know how to change the column type in Oracle. This example changes the column type to CLOB from VARCHAR. As you know, CLOB is used to keep a large type of text or object in a table. If VARCHAR is not sufficient for your requirement, then you can change the type to CLOB, like below
-- ALTER TABLE employee_table ADD (clob_column CLOB); UPDATE employee_table SET clob_column = old_column; ALTER TABLE employee_table DROP COLUMN old_column; ALTER TABLE employee_table RENAME COLUMN clob_column TO old_column; --
Adding/removing NOT NULL constraints
If you want to add a NOT NULL constraint, you must have values in all the rows. Otherwise, you have to update all the values first. Then you can create the constraint. Below is the code to add a NOT NULL a column
-- ALTER TABLE employee_table MODIFY employee_name VARCHAR2( 100 ) NOT NULL; SQL Error: ORA-02296: cannot enable (OT.) - null values found --
Accessential ALTER TABLE Statement Syntaxes
-- ALTER TABLE Statement --Add column in table ALTER TABLE table_name --ADD column_name column_definition --Modify column in table ALTER TABLE table_name MODIFY column_name column_type; --Drop column in table ALTER TABLE table_name DROP COLUMN column_name; --Rename column in table ALTER TABLE table_name RENAME COLUMN old_name TO new_name; --
Special Note
If you are doing these kinds of scenarios, use off-peak time, and if you have a lot of records in the table, you can update the column by using bulk collect. If you have an index created on this column first, you have to remove it
Because once you’re doing this in a huge table, then the database can slow down and will take hours to complete, so please consider such kind of performance situations if you’re doing this in big tables
Conclusion
This small article focuses on Oracle changing column data type using a SQL script. I have included different types of examples to change the column type to different data types. You can try out similar steps for other data types.
But when you try this as a habit, you can test those in a test environment first. If you write a step in the wrong way, you can lose the data. Therefore, according to my experience, we use a test environment for your experiments without doing those in the live environment.
Ref Stack
