Oracle: Alter Table


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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s