Invisible Columns in Oracle Database 12c

Oracle database 12C Invisible Column

At this article I’ll try to explain one of new features of oracle 12c  invisible column.

This property means no longer visible by Select * or SQL*PLUS..

External, cluster and temporary tables can not have invisible columns.

Virtual columns can be made invisible.

A table can be partitioned by an invisible column, either during or after table creation.

User-defined types can not contain invisible attributes.

You can not make system generated hidden columns visible.

The following example creates a table with an invisible column:

CREATE TABLE  TEST (id number(5), name varchar2(20), salary number(9) INVISIBLE); Table created

DESCRIBE TEST; Name      Null?  Type ----------------------------------------- -------- ---------------------- id               NUMBER(5) name           VARCHAR2(40) salary           NUMBER(9) INVISIBLE

Lets data insert To INVISIBLE COLUMN, insert into TEST (id,name,salary) values (1,'Veysi GÜNAY',500); 1 row created. SELECT * FROM TEST; ID               NAME ----------  ------------------------------      1        Veysi GÜNAY

TO MAKE COLUMN VISIBLE

ALTER TABLE Test MODIFY(salary VISIBLE);     insert into TEST (id,name,salary) values (2,'John SMIT',1100); 1 row created. SELECT * FROM TEST; ID               NAME           SALARY ----------  ----------------  --------------      1        Veysi GÜNAY         500      2       John  SMIT         1100

TO MAKE COLUMN INVISIBLE AGAIN

ALTER TABLE Test MODIFY(salary INVISIBLE);

To show Invısıble Column

*SET COLINVISIBLE ON

* SELECT TABLE_NAME,  COLUMN_NAME, HIDDEN_COLUMN          FROM USER_TAB_COLS WHERE HIDDEN_COLUMN='YES';

Also you can create a view for invisible column.

Hope you doing good…