Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Basics / Oracle ALTER TABLE ADD Column By Examples

Oracle ALTER TABLE ADD Column By Examples

Summary: in this tutorial, you will learn how to use the Oracle ALTER TABLE ADD column statement to add one or more columns to a table.

To add a new column to a table, you use the ALTER TABLE statement as follows:

ALTER TABLE table_name ADD column_name data_type constraint;
Code language: SQL (Structured Query Language) (sql)

In this statement:

  • First, you specify the name of the table, which you want to add the new column, after the ALTER TABLE clause.
  • Second, you specify the column name, data type, and its constraint.

Note that you cannot add a column that already exists in the table; trying to do so will cause an error. In addition, the ALTER TABLE ADD column statement adds the new column at the end of the table. Oracle provides no direct way to allow you to specify the position of the new column like other database systems such as MySQL.

In case you want to add more than one column, you use the following syntax:

ALTER TABLE table_name ADD ( column_name_1 data_type constraint, column_name_2 data_type constraint, ... );
Code language: SQL (Structured Query Language) (sql)

In this syntax, you separate two columns by a comma.

Oracle ALTER TABLE ADD column examples

Let’s create a table named members for the demonstration.

CREATE TABLE members( member_id NUMBER GENERATED BY DEFAULT AS IDENTITY, first_name VARCHAR2(50), last_name VARCHAR2(50), PRIMARY KEY(member_id) );
Code language: SQL (Structured Query Language) (sql)

The following statement adds a new column named birth_date to the members table:

ALTER TABLE members ADD birth_date DATE NOT NULL;
Code language: SQL (Structured Query Language) (sql)

In this example, the birth_date column is a DATE column and it does not accept null.

Suppose, you want to record the time at which a row is created and updated. To do so, you need to add two columns created_at and updated_at as follows:

ALTER TABLE members ADD( created_at TIMESTAMP WITH TIME ZONE NOT NULL, updated_at TIMESTAMP WITH TIME ZONE NOT NULL );
Code language: SQL (Structured Query Language) (sql)

The data types of the created_at and updated_at columns are TIMESTAMP WITH TIME ZONE. These columns also do not accept null.

To check whether a column exists in a table, you query the data from the user_tab_cols view. For example, the following statement checks whether the members table has the first_name column.

SELECT COUNT(*) FROM user_tab_cols WHERE column_name = 'FIRST_NAME' AND table_name = 'MEMBERS';
Code language: SQL (Structured Query Language) (sql)

This query comes in handy when you want to check whether a column exists in a table before adding it.

For example, the following PL/SQL block checks whether the members table has effective_date column before adding it.

SET SERVEROUTPUT ON SIZE 1000000 DECLARE v_col_exists NUMBER BEGIN SELECT count(*) INTO v_col_exists FROM user_tab_cols WHERE column_name = 'EFFECTIVE_DATE' AND table_name = 'MEMBERS'; IF (v_col_exists = 0) THEN EXECUTE IMMEDIATE 'ALTER TABLE members ADD effective_date DATE'; ELSE DBMS_OUTPUT.PUT_LINE('The column effective_date already exists'); END IF; END; /
Code language: SQL (Structured Query Language) (sql)

If you execute the block at the first time, the effective_date column is appended at the end of the members table. However, once you execute it from the second time, you will see the following message:

The column effective_date already exists
Code language: SQL (Structured Query Language) (sql)

which is what we expected.

In this tutorial, you have learned how to use the Oracle ALTER TABLE ADD column statement to add one or more column to an existing table.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle ALTER TABLE
Next Oracle Virtual Column

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.