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 MODIFY Column

Oracle ALTER TABLE MODIFY Column

Summary: in this tutorial, you will learn how to use the Oracle ALTER TABLE MODIFY column statement to change the definition of existing columns.

To change the definition of a column in a table, you use the ALTER TABLE MODIFY column syntax as follows:

ALTER TABLE table_name MODIFY column_name action;

The statement is straightforward. To modify a column of a table, you need to specify the column name, table name, and action that you want to perform.

Oracle allows you to perform many actions but the following are the main ones:

  • Modify the column’s visibility
  • Allow or not allow null values
  • Shorten or widen the size of the column
  • Change the default value of a column
  • Modify expression of the virtual columns

To modify multiple columns, you use the following syntax:

ALTER TABLE table_name MODIFY ( column_name_1 action, column_name_2 action, ... );

Oracle ALTER TABLE MODIFY column examples

First, create a new table named accounts for the demonstration:

CREATE TABLE accounts ( account_id NUMBER GENERATED BY DEFAULT AS IDENTITY, first_name VARCHAR2(25) NOT NULL, last_name VARCHAR2(25) NOT NULL, email VARCHAR2(100), phone VARCHAR2(12) , full_name VARCHAR2(51) GENERATED ALWAYS AS( first_name || ' ' || last_name ), PRIMARY KEY(account_id) );

Second, insert some rows into the accounts table:

INSERT INTO accounts(first_name,last_name,phone) VALUES('Trinity', 'Knox', '410-555-0197'); INSERT INTO accounts(first_name,last_name,phone) VALUES('Mellissa', 'Porter', '410-555-0198'); INSERT INTO accounts(first_name,last_name,phone) VALUES('Leeanna', 'Bowman', '410-555-0199');

Third, verify the insert operation by using the following SELECT statement:

SELECT * FROM accounts;
Oracle ALTER TABLE MODIFY Column - example

A) Modify the column’s visibility

In Oracle Database 12c, you can define table columns as invisible or visible. Invisible columns are not available for the query like:

SELECT * FROM table_name;

Or statement like:

DESCRIBE table_name;

in SQL*Plus.

However, you can query the invisible columns by specify them explicitly in the query:

SELECT invisible_column_1, invisible_column_2 FROM table_name;

By default, table columns are visible. You can define invisible column when you create the table or using ALTER TABLE MODIFY column statement.

For example, the following statement makes the full_name column invisible:

ALTER TABLE accounts MODIFY full_name INVISIBLE;
Oracle ALTER TABLE MODIFY Column visibility example

The following statement returns data from all columns of the accounts table except the full_name column:

SELECT * FROM accounts;

This is because the full_name column is invisible.

To change a column from invisible to visible, you use the statement below:

ALTER TABLE accounts MODIFY full_name VISIBLE;

B) Allow or not allow null example

The following statement changes the email column to accept non-null values:

ALTER TABLE accounts MODIFY email VARCHAR2( 100 ) NOT NULL;

However, Oracle issued the following error:

SQL Error: ORA-02296: cannot enable (OT.) - null values found

Because when you changed a column from nullable to non-nullable, you must ensure that the existing data meets the new constraint.

To fix this, we update the values for the email column first:

UPDATE accounts SET email = LOWER(first_name || '.' || last_name || '@oracletutorial.com') ;

Note that the LOWER() function converts a string to lowercase.

And then change the column’s constraint:

ALTER TABLE accounts MODIFY email VARCHAR2( 100 ) NOT NULL;

Now, it is working as expected.

C) Widen or shorten the size of a column example

Suppose, we want to add international code to the phone numbers. Before doing it, we must widen the size of the phone column by using the following statement:

ALTER TABLE accounts MODIFY phone VARCHAR2( 15 );

Now, we can update the phone numbers:

UPDATE accounts SET phone = '+1-' || phone;

The following statement verifies the update:

SELECT * FROM accounts;
Oracle ALTER TABLE MODIFY column - widen column size example

To shorten the size of a column, you make sure that all data in the column fits the new size.

For example, we try to shorten the size of the phone column down to 12 characters:

ALTER TABLE accounts MODIFY phone VARCHAR2( 12 );

Oracle Database issued the following error:

SQL Error: ORA-01441: cannot decrease column length because some value is too big

To fix this, first, we should remove the international code from the phone numbers:

UPDATE accounts SET phone = REPLACE( phone, '+1-', '' );

The REPLACE() function replaces a substring by a new substring. In this case, it replaces the ‘+1-‘ by an empty string.

And then shorten the size of the phone column:

ALTER TABLE accounts MODIFY phone VARCHAR2( 12 );

D) Modify virtual column

Suppose, we the full name in the following format:

last_name, first_name

To do this, we can change the expression of the virtual column full_name as follows:

ALTER TABLE accounts MODIFY full_name VARCHAR2(52) GENERATED ALWAYS AS (last_name || ', ' || first_name);

The following statement verifies the modification:

SELECT * FROM accounts;
Oracle ALTER TABLE MODIFY Column - modify virtual column example

E) Modify the default value of a column

Let’s add a new column named status to the accounts table with default value 1.

ALTER TABLE accounts ADD status NUMBER( 1, 0 ) DEFAULT 1 NOT NULL ;
Oracle ALTER TABLE MODIFY Column - Add status column

Once you executed the statement, the values in the status column are set to 1 for all existing rows in the accounts table.

To change the default value of the status column to 0, you use the following statement:

ALTER TABLE accounts MODIFY status DEFAULT 0;

We can add a new row to the accounts table to check whether the default value of the status column is 0 or 1:

INSERT INTO accounts ( first_name, last_name, email, phone ) VALUES ( 'Julia', 'Madden', 'julia.madden@oracletutorial.com', '410-555-0200' );

Query data from the accounts table:

SELECT * FROM accounts;
Oracle ALTER TABLE MODIFY Column - change column default value

As you can see, the value in the status column for the account with id 4 is 0 as expected.

In this tutorial, you have learned how to use the Oracle ALTER TABLE MODIFY column statement to change the definition of existing columns in a table.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle Virtual Column
Next Oracle Drop 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.