Oracle alter table modify column Syntax example

http://www.dba-oracle.com/t_alter_table_modify_column_syntax_example.htm

 

For complete tips on Oracle alter table syntax, see the book "​​Easy Oracle Jumpstart​​".  Oracle provides "alter table" syntax to modify data columns in-place in this form:

alter table

   table_name

modify

   column_name  datatype;

If you are brave you can use a single "alter table" syntax to modify multiple columns:

alter table

   table_name

modify

   (

   column1_name  column1_datatype,

   column2_name  column2_datatype,

   column3_name  column3_datatype,

   column4_name  column4_datatype

   );

Here are some examples of Oracle "alter table" syntax to modify data columns and note that you can add constraints like NOT NULL:

ALTER TABLE 

   customer 

MODIFY 

   ( 

   cust_name varchar2(100) not null,

   cust_hair_color  varchar2(20)

   )

;

We can also use Oracle "alter table" syntax in dynamic PL/SQL to modify data columns

BEGIN 

SQL_STRING := 'ALTER TABLE '||:TABLE_NAME||' MODIFY '||:COLUMN_NAME||' VARCHAR2(100)'; . . . 

END;