Search notes:
Oracle: Invisible columns
A
column can be created to be invisible. The
invisible keyword must(?) be placed right after the column's
data type:
create table tq84_tab_with_hidden_col (
id integer generated by default on null as identity,
num number,
txt varchar2(20),
ins_dt date INVISIBLE default sysdate not null,
--
constraint tab_with_hidden_col_pk primary key (id)
);
insert into tq84_tab_with_hidden_col(num, txt) values (42, 'forty-two' );
insert into tq84_tab_with_hidden_col(num, txt) values (99, 'ninety-nine');
By default, invisible columns are not shown when selecting from a table with the star (*):
select * from tq84_tab_with_hidden_col;
ID NUM TXT
---------- ---------- --------------------
1 42 forty-two
2 99 ninety-nine
However, invisible columns can be explicitly specified in the select statement which shows their value:
select
num,
txt,
ins_dt
from
tq84_tab_with_hidden_col;
NUM TXT INS_DT
---------- -------------------- -------------------
42 forty-two 2022-02-12 08:43:34
99 ninety-nine 2022-02-12 08:43:35
A column can be hidden after the table was created:
alter table tq84_tab_with_hidden_col modify txt invisible;
Cleaning up:
drop table tq84_tab_with_hidden_col;
Insert statements
When inserting values into a hidden column, the column name must be stated. The following example throws
ORA-00913: too many values:
create table tq84_ora_00913 (
vis integer,
hid varchar2(20) invisible
);
insert into tq84_ora_00913 values (42, 'hello world');
drop table tq84_ora_00913;
At least one column must be visible
Views
It is not possible to hide columns of a
view - but that wouldn't make sense either.