Another 12c new feature - Invisible Columns

Introduction

In my previous post I've demonstrated the In-Database archiving feature that was introduced in Oracle 12c (in version 12.1.0.1 to be more specific) and enables you to mark some rows as invisible to the application.
In this post, I'd like to demonstrate the invisible columns feature which allows you to mark a specific column or set of columns as invisible. You can either mark the columns as invisible as part of the CREATE TABLE command, or as part of the ALTER TABLE command.

If you will execute a SELECT * from the table, the invisible columns will not be displayed in the output of the select statement; however, if you will explicitly specify these columns as part of the SELECT statement they will be displayed in the output of the select statement.
If you will execute an INSERT statement and you don't specify the columns, the INSERT will work; however, you can insert value to the invisible columns if you will explicitly specify the invisible columns as part of the INSERT statement.

Demonstration

Let's see a quick demonstration of this feature. In this demonstration I will perform the following steps:

1. create a new table named "TEST" with 2 columns:
• ID - VISIBLE (default)
• NAME - INVISIBLE
2. insert a row to the table without specifying the column - the insert statement will fail because it expects only 1 value for the ID column 
3. insert a row to the tame - this time I explicitly specify the columns and and the insert statement works
4. execute SELECT * from the table and see that only the ID column is displayed
5. execute the select query again - but this time explicitly specify the columns - now both the ID and NAME columns are displayed
6. execute the DESC command on the table - the NAME column is not being displayed as part of the DESC command output
7. Changing the column to be visible using the ALTER TABLE command - this time the DESC command displays the NAME column

Invisible columns and constraints

Oracle enforce integrity constraints for columns that are configured to be invisible like any other column. To demonstrate this, I will make the "NAME" column invisible again and then I will create the NOT NULL constraint and I will try to insert a value to the ID column only. This attempt will fail with ORA-01400 because it's impossible to insert a NULL value to the NAME column:

Conclusions

The Invisible columns is a very simple and straight-forward feature which can be useful if you would like to add a new column to a table but at the first stage you don't want that the application will be affected by the new column. SELECT * and INSERT statements will continue to function normal (unless an integrity constraint on the invisible column was violated), and in the moment that you decide that you want to make the column visible, you can manage to do so in a single command (ALTER TABLE table_name MODIFY column_name VISIBLE).