Oracle 12c New Features - Invisible Columns

Oracle 12c allows us to make a column invisible.
The invisible column is not allowed for the below table types

External tables
Cluster tables
Temporary tables

Let us check how to make a column invisible.

SQL> CREATE TABLE t1(eid NUMBER, ename VARCHAR2(30)) TABLESPACE users;

Table created.

SQL> DESC t1
 Name      Null?    Type
 -------------------------------- -------- ----------------------
 EID                 NUMBER
 ENAME         VARCHAR2(30)

SQL> SELECT table_name,column_id,column_name FROM user_tab_columns WHERE table_name='T1' order by column_id;

TABLE_NAME       COLUMN_ID COLUMN_NAME
--------------------   ----------         --------------------
T1                                         1           EID
T1                                         2           ENAME

SQL> 

SQL> INSERT INTO t1 VALUES(100,'Test Employee 1');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM t1;

       EID ENAME
---------- ------------------------------
       100 Test Employee 1

-- > Set the column invisible

SQL> ALTER TABLE t1 MODIFY (ename INVISIBLE);

Table altered.

SQL> DESC t1
 Name      Null?    Type
 -------------------------------- -------- ----------------------
 EID                     NUMBER

-- > Setting the sqlplus to show invisible column in DESCRIBE output (set colinvisible off | on)

SQL> set colinvisible on 
SQL> DESC t1
 Name      Null?                   Type
 -------------------------------- -------- ----------------------
 EID                                    NUMBER
 ENAME (INVISIBLE)      VARCHAR2(30)


-- > The column_id in user_tab_columns does not show any value after making column invisible

SQL> SELECT table_name,column_id,column_name FROM user_tab_columns WHERE table_name='T1' order by column_id;

TABLE_NAME   COLUMN_ID COLUMN_NAME
------------------------------ ---------- ------------------------------
T1                                            1   EID
T1                                                 ENAME

-- > The ename column is not visible to the "SELECT *" query.

SQL> SELECT * FROM t1;

       EID
----------
       100

-- > Still the ename column shows value by explicitly specifying the ename column in query 

SQL> select ename from t1;

ENAME
------------------------------
Test Employee 1
Test Employee 2

-- > The below error shows that ename column is not avilable to insert statement


SQL> INSERT INTO t1 VALUES(200,'Test Employee 2');
INSERT INTO t1 VALUES(100,'Test Employee 2')
            *
ERROR at line 1:
ORA-00913: too many values

-- > Let us make the column visible as below

SQL> ALTER TABLE t1 MODIFY (ename VISIBLE);

Table altered.

SQL> DESC t1
 Name      Null?    Type
 -------------------------------- -------- ----------------------
 EID                    NUMBER
 ENAME            VARCHAR2(30)


SQL> INSERT INTO t1 VALUES(200,'Test Employee 2');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM t1;

       EID ENAME
---------- ------------------------------
       100 Test Employee 1
       200 Test Employee 2

SQL> 

-- > The column_id in user_tab_columns shows value as 2 after making column visible

SQL> SELECT table_name,column_id,column_name FROM user_tab_columns WHERE table_name='T1' order by column_id;

TABLE_NAME       COLUMN_ID COLUMN_NAME
-------------------- ---------- --------------------
T1                                       1           EID
T1                                       2           ENAME