12C New Feature: Invisible Columns

Starting from 12c, Oracle allows columns to be hidden from application.
Columns can be made invisible in the CREATE TABLE statement or  using an ALTER TABLE statement.By default columns are visible. Invisible columns can be made visible again using an ALTER TABLE statement.Any generic access of a table (such as a SELECT * FROM table or a DESCRIBE) will not show invisible columns.

General characteristics for Invisible Columns:

 1.  Any generic access of a table (such as a SELECT * FROM table or a DESCRIBE) will not show invisible columns.
 2.  The database usually stores columns in the order in which they were listed in the CREATE TABLE statement. If you add a new column to a table, then the new column becomes the last column in the table’s column order. When a table contains one or more invisible columns, the invisible columns are not included in the column order for the table.
3.Invisible columns are not seen unless specified explicitly in the SELECT list.
 
 
Create Table with INVISIBLE column

SQL> CREATE TABLE test1 (a INT, b INT INVISIBLE, c INT Not NULL);


Table created.


SQL> desc test1;
Name Null? Type
—————————————– ——– —————————-
A NUMBER(38)
C NOT NULL NUMBER(38)




SQL> select COLUMN_NAME,COLUMN_ID,HIDDEN_COLUMN from user_tab_cols where table_name =’TEST1′;


COLUMN_NAME COLUMN_ID HIDDEN_COLUMN
————— ———- —————
C               2          NO
B                          YES
A               1          NO

 The invisible column wont be displayed in a DESCRIBE statement. The column id for an invisible (or hidden ) column will be NULL.

Changing the column to visible / invisible using the ALTER TABLE statement


SQL> ALTER TABLE test1 MODIFY (b VISIBLE);


Table altered.

While inserting data into table with INVISIBLE Columns, proper care need to be taken.
Otherwise,data will be inserted to unexpected columns.