Changing the data type of a column in Oracle

Question

I created the following table

CREATE TABLE PLACE(   POSTCODE VARCHAR(10) PRIMARY KEY,   STREET_NAME VARCHAR(10),   COUNTY VARCHAR(10),   CITY VARCHAR(10));   

I want to change the name, county and city from varchar(10) to varchar(20). How do I do that?

Answer 1

ALTER TABLE place   MODIFY( street_name VARCHAR2(20),           county      VARCHAR2(20),           city        VARCHAR2(20) ) 

Note that I am also changing the data type from VARCHAR to VARCHAR2 to be more conventional. There is no functional difference at present between the two though the behavior of VARCHAR may change in the future to match the SQL standard.

Answer 2

if you want to change only type of column use below:

ALTER TABLE <table_name> MODIFY (<column_name> <new_Type>) in your case:     ALTER TABLE place MODIFY (street_name VARCHAR2(20),                           county      VARCHAR2(20),                           city        VARCHAR2(20)) 

If your table has data you could act below:

1. add a column with new type to table.
2. copy data from old column to new column.
3. drop old column.
4. rename new column to old.

For rename a column use below:

ALTER TABLE <table_name> rename column <column_name> to <new_column_name>