Oracle 12c - Invisible Column

Oracle 12c - Invisible Column.

Invisible Column, I don't need to explain what this means? This is pretty much self explanatory to everyone including a cave men. Oracle 12c will let you add invisible column to your existing table which is totally invisible to DBA, developer and application. Even select * from table will not catch the new added column. Insert will not throw error either.. Cool huh, now can you think of one reason where/how you will  benefit the invisible column?

Prior to Oracle 12c, adding a column could break an application or procedures if the column dependencies are not revised. This new invisible column is a great way to add a column to a table without breaking the code.

This is a great addition with 12C, we need more features like this on other database objects. You can test your new you application with new columns without breaking. DBA and Developer will love this feature as they can test it without impacting the production.

Lets do the demo of Invisible Column!
Create table demoTable
( columnA int,
  columnB int
);

Table Created.

Insert Data into a table
Insert into demoTable values( 1,2);
commit;
1 rows inserted
committed

Lets Add Invisible Column C to our demoTable.

SQL> alter table demoTable add (columnC int INVISIBLE);
Table Altered
Describe DemoTable;

 Name  Null?    Type
 ————— ———————— ——————————
 ColumnA            NUMBER(38)
 ColumnB              NUMBER(38)


Describe Does not Display the Invisible Column
How do you view the invisible column?
SET COLINVISIBLE ON

Desc DemoTable;
Name  Null?    Type
 ————— ———————— ——————————
 ColumnA            NUMBER(38)
 ColumnB              NUMBER(38)
ColumnC (Invisible) NUMBER(38)

The ColumnC is listed as INVISIBLE, it will not be selected via a SELECT *;  nor it will impact your INSERT(insert into demoTable values(4,6); For backward compatibility, this new capability is a bonus. New applications that need to access this column have full access to it:

SQL> insert into demoTable (columnA, columnB, columnC)
  2  values ( 5,6,7 );

1 row created.

SQL> select columnA, columnB, ColumnC from demoTable;
     ColumnA       ColumnB        ColumnC
   ——————— ————— —————
     1                 2
     5         6         7

Those new applications only need to explicitly reference columnC, which is the right approach (because, again, using SELECT * and INSERT without a column list should be considered a bug—a bug that needs to be fixed).Ultimately, after the legacy code has been corrected, you will want to make this column visible:

SQL> alter table demoTable modify columnC visible;
Table altered.

The table now behave likes a regular table. After these series of lab exercise, list  how you are going to use this feature?