• Skip to primary navigation
  • Skip to main content

Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

  • Home
  • HTML
    • Tags in HTML
    • HTML Attributes
  • OA Framework
    • Item in OAF
    • Regions in OAF
    • General OAF Topics
  • Oracle
    • statement
    • function
    • clause
    • plsql
    • sql
You are here: Home / Oracle / statement / ALTER TABLE Statement in Oracle SQL – PLSQL

ALTER TABLE Statement in Oracle SQL – PLSQL

October 22, 2012 by techhoneyadmin

The ALTER TABLE statement in PLSQL allows us to alter or change the following:

1. Rename an already existing table in database.
2. Add, modify or delete/drop column(s) from an already existing table in database.

It is a Data Definition Language (DDL) statement.
Syntax for the ALTER TABLE to rename an existing table in PLSQL is:

ALTER TABLE table_name
RENAME TO new_table_name;

Let’s take an example for understanding:
Suppose we have a table named “Employee” in database as shown below.

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Scenario 1:

Here we will see how we can use ALTER TABLE statement to rename the ’employee’ table.

view source
print?
1ALTER TABLE employee
2RENAME TO employee_new;

The above ALTER TABLE statement will rename table ’employee’ as ’employee_new’ in the database. It does not affect the data of the table, hence if you query ’employee_new’ table as;

view source
print?
1SELECT *
2FROM employee_new;

You will get the following result:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Scenario 2:

Here we will see how we can use ALTER TABLE statement to add a column to already existing table named ’employee’.

The syntax for adding a column to already existing table is:

ALTER TABLE table_name
ADD column_name datatype;

Suppose our ’employee’ table has the data as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Now we will add one more column named “nick_name” in the ’employee’ table by writing the ALTER TABLE statement as:

view source
print?
1ALTER TABLE employee
2ADD nick_name VARCHAR2(100);

If we query the ’employee’ table as;

view source
print?
1SELECT *
2FROM employee;

We will get the following output:

Employee_ID Employee_Name Salary Department Commission Nick_Name
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Here we have successfully added a new column “nick_name” to ’employee’ table.


Scenario 3:

Adding multiple columns to existing database table, the syntax for adding multiple columns using ALTER TABLE statement is:

ALTER TABLE table_name
ADD(column_name1 datatype
,column_name2 datatype
,column_name3 datatype
.
.
.
,column_nameN datatype
);

Suppose we have ’employee’ table as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Now we want to add 3 columns namely “nick_name”, “city” and “state” to the ’employee’ table. We can achieve the same by writing ALTER TABLE statement as:

view source
print?
1ALTER TABLE employee
2ADD (nick_name VARCHAR2(100)
3     ,city VARCHAR2(200)
4     ,state VARCHAR2(200)
5    );

If we query the ’employee’ table as;

view source
print?
1SELECT *
2FROM employee;

We will get the following output:

Employee_ID Employee_Name Salary Department Commission Nick_Name City State
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Here we have successfully added 3 new columns “nick_name”,”city” and “state” to ’employee’ table using ALTER TABLE statement.


Scenario 4:

Altering or modifying already existing columns in a table.
The syntax of the ALTER TABLE statement to achieve this is;

ALTER TABLE table_name
MODIFY column_name datatype;

Suppose we create an ’employee’ table using the code shown below;

view source
print?
1CREATE TABLE employee
2(employee_id   NUMBER(10)    NOT NULL
3,employee_name VARCHAR2(500) NOT NULL
4,salary        NUMBER(20)    NOT NULL
5,department    VARCHAR2(300) NOT NULL
6,commission    VARCHAR2(100)
7);

But then we realize that the “commission” column of the ’employee’ table should have been a NUMBER type rather than being a VARCHAR2 type and it should not allow NULL values.
So instead of deleting the ’employee’ table and creating the same again, we can use the ALTER TABLE statement to change the datatype of the “commission” column from VARCHAR2 to NUMBER as follows:

view source
print?
1ALTER TABLE employee
2MODIFY commission NUMBER(20) NOT NULL;

The NOT NULL in the code above will force the commission column to have values and will not allow NULL.


Scenario 5:

Altering or modifying multiple columns in a table at once using ALTER TABLE statement.
The syntax of the ALTER TABLE statement to achieve this is;

ALTER TABLE table_name
MODIFY (column_name1 datatype,
,column_name2 datatype
,column_name3 datatype
.
.
,column_nameN datatype
) ;

Suppose we create an ’employee’ table using the code shown below;

view source
print?
1CREATE TABLE employee
2(employee_id   NUMBER(10)    NOT NULL
3,employee_name VARCHAR2(500) NOT NULL
4,salary        NUMBER(20)    NOT NULL
5,department    VARCHAR2(300) NOT NULL
6,commission    VARCHAR2(100)
7);

But then we realize that the “commission” column of the ’employee’ table should have been a NUMBER type rather than being a VARCHAR2 type and should not allow NULL values. Also we want the employee_name column to be of 300 length instead of 500.
So instead of deleting the ’employee’ table and creating the same again, we can use the ALTER TABLE statement to make the necessary changes as follows:

view source
print?
1ALTER TABLE employee
2MODIFY (employee_name VARCHAR2(300)
3        ,commission NUMBER(20) NOT NULL
4       );

Scenario 6:

Deleting or dropping column in a table using ALTER TABLE statement
The syntax of the ALTER TABLE statement to achieve this is;

ALTER TABLE table_name
DROP COLUMN column_name

) ;

Suppose we create an ’employee’ table using the code shown below;

view source
print?
1CREATE TABLE employee
2(employee_id   NUMBER(10)    NOT NULL
3,employee_name VARCHAR2(500) NOT NULL
4,salary        NUMBER(20)    NOT NULL
5,department    VARCHAR2(300) NOT NULL
6,commission    VARCHAR2(100)
7);

But then we realize that the “commission” column is not required and can be dropped from the ’employee’ table.

So instead of deleting the ’employee’ table and creating the same again, we can use the ALTER TABLE statement to make the necessary changes as follows:

view source
print?
1ALTER TABLE employee
2DROP COLUMN commission;

Scenario 7:

Renaming a column in a table using ALTER TABLE statement

The syntax of the ALTER TABLE statement to rename a column in a table is;

ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;

Suppose we create an ’employee’ table using the code shown below;

view source
print?
1CREATE TABLE employee
2(employee_id   NUMBER(10)    NOT NULL
3,employee_name VARCHAR2(500) NOT NULL
4,salary        NUMBER(20)    NOT NULL
5,department    VARCHAR2(300) NOT NULL
6,commission    VARCHAR2(100)
7);

But then we realize that the “commission” column should be named as “comm”, so instead of deleting the ’employee’ table and creating the same again, we can use the ALTER TABLE statement to make the necessary changes as follows:

view source
print?
1ALTER TABLE employee
2RENAME COLUMN commission TO comm;

Filed Under: statement Tagged With: alter table statement in oracle plsql, alter table statement in oracle sql, ALTERTABLEPLSQL, how to use alter table statement in oracle database query, how to use alter table statement in oracle plsql, how to use alter table statement in oracle sql, syntax and example of alter table statement in oracle database query, syntax and example of alter table statement in oracle plsql, syntax and example of alter table statement in oracle sql, using alter table statement in oracle database query, using alter table statement in oracle plsql, using alter table statement in oracle sql

Copyright © 2025 · Parallax Pro on Genesis Framework · WordPress · Log in