Oracle Change Column Length Varchar2

Oracle Change Column Length Varchar2 | In this tutorial we will see how to change the column length of varchar2 in Oracle Database. 

Oracle 7.0 introduced the varchar2 data type. The varchar2 datatype is only available for the Oracle database, not for the other database. It stores variable-length alphanumeric data in bytes. The varchar2 data types stores up to a maximum of 4000 bytes.

Let us create a new table with a column of type varchar2. Initially, it will contain a length of 50, and later we will change that column length.

CREATE TABLE test(
   id number,
   name varchar2(50)
);

In the above query, we have created a “test” table with columns:- “id”, and “name”. The “id” column is of number data type and the “name” column is of varchar2 data type. The length of the “name” column is 50 bytes which means the Oracle database can’t store more than 50 characters in the “name” column. 1 character occupies 1 byte, therefore 50 characters will occupy 50 bytes.

If we try to insert more than 50 characters in the “name” column then the Oracle database will give an error:- ORA-12899: value too large for column “DB”.”TEST”.”NAME” (actual: <actual-size>, maximum:50)

If we want to store more than 50 characters in the “name” column then we have to change its length. Using the DESC command we can see the table structure:-

DESC test;

Output:-

Name     Null?    Type
-------- -------- -------------
ID                NUMBER
NAME              VARCHAR2(50)

It shows currently “name” column is of varachar2 data type and it can store a maximum of 50 characters. Now let us see oracle change column length varchar2.

The syntax for Oracle Alter Table Column Size

ALTER TABLE <tableName>
MODIFY <columnName> <DataType>([Size]);

Oracle Alter Table Increase Varchar Column Size

Query to change the column “name” which has data type VARCHAR(50).In Oracle, ALTER TABLE command increases the VARCHAR column size.

ALTER TABLE test
MODIFY name VARCHAR2(100);

Using the Oracle alter table column size can be increased or decreased based on our requirement. Here is the output for the above query:- Table altered.

Now let’s check the “test” table description by using a description query.

DESC test;

Output:-

Name     Null?    Type
-------- -------- -------------
ID                NUMBER
NAME              VARCHAR2(100)

In the above result, we can see that from the ALTER keyword we changed the “name” column length of type VARCHAR2 from 50 to 100. And this column can store a maximum of 100 characters.

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Search