In 12c, Oracle has introduced new feature called Invisible column. It is used to protect data from application users.
- Columns can be made invisible in the CREATE TABLE statement or subsequently using an ALTER TABLE statement. By default columns are visible.
- Invisible columns can be made visible again using an ALTER TABLE statement.
- Any generic queries on table (like SELECT or DESCRIBE) will not show invisible columns.
- The invisible columns are not included in the column order for the table.
- Invisible columns are not seen unless specified explicitly in the SELECT list.
Example:
1) Create table with invisible column
SQL> create table test (a number, b number invisible, c number);
Table created
- Normal describle or select query will not show hidden column
SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER
C NUMBER
SQL> select * from test;
no rows selected
- We can insert into hidden column without specifying it.
SQL> insert into test values(1,2,3);
insert into test values(1,2,3)
*
ERROR at line 1:
ORA-00913: too many values
SQL> insert into test values(1,2);
1 row created.
SQL> select * from test;
A C
---------- ----------
1 2
- By default NULL value will be inserted for hidden column. We can insert value to hidden column explicitly by specifying the column.
SQL> select a,b,c from test;
A B C
---------- ---------- ----------
1 2
SQL> alter table test modify b visible;
Table altered.
- Since invisible changes the column id, the order it stored in database gets changed.
SQL> select * from test;
A C B
---------- ---------- ----------
1 2
SQL> insert into test values (1,2,3);
1 row created.
SQL> select * from test;
A C B
---------- ---------- ----------
1 2
1 2 3
SQL> insert into test(a,b,c) values (1,2,3);
1 row created.
SQL> select * from test;
A C B
---------- ---------- ----------
1 2
1 2 3
1 3 2
Making Invisible column with NOT NULL constraint
---------------------------------------------------
SQL> alter table test modify c number not null;
Table altered.
SQL> alter table test modify (c invisible);
Table altered.
SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER
B NUMBER
SQL> select * from test;
A B
---------- ----------
1
1 3
1 2
SQL> insert into test values(1,2);
insert into test values(1,2)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."TEST"."C")
- Since column 'C' has not null constraint, default NULL value can not be inserted.
SQL> insert into test values(1,2,3);
insert into test values(1,2,3)
*
ERROR at line 1:
ORA-00913: too many values
--- we can insert value to hidden column explicitly by specifying the column
SQL> insert into test(a,b,c) values (1,2,3);
1 row created.
SQL> select * from test;
A B
---------- ----------
1
1 3
1 2
1 2
SQL> select a,b,c from test;
A B C
---------- ---------- ----------
1 2
1 3 2
1 2 3
1 2 3
-- To know hidden columns of a table query user_tab_cols
SQL> col table_name format a5
SQL> col column_name format a5
SQL> col hidden_column format a5
SQL> select table_name, column_name, hidden_column from user_tab_cols where table_name='TEST';
TABLE COLUM HIDDE
----- ----- -----
TEST A NO
TEST B NO
TEST C YES
