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

Oracle makes sure that each table has at least one visible column (see also ORA-54039: table must have at least one column that is not invisible)

Views

It is not possible to hide columns of a view - but that wouldn't make sense either.

Oracle: Invisible columns

Insert statements

At least one column must be visible

Views

See also