Oracle SQL - Table Invisible Columns

  1. HOME
  2. Oracle SQL
  3. Table
  4. CREATE TABLE Command

Introduction

The following code shows how to add an invisible column: 

create table t (x number, y number);
alter table t add (newcol number INVISIBLE);

insert into t (x,y,newcol) values (1,2,3);

When addressing the column explicitly you of course see it. 

select x, y, newcol from t;

With SELECT * you don't see the column. 

select * from t;

You can define a view where the column is visible.

Columns in views are visible regardless of their visibility in the base table. 

create or replace view see_all as select x,y,newcol from t;

If we had specified SELECT * while creating the view, the invisible column from table t would never have made it to the view.

The CREATE VIEW command hardcodes the columns it finds and explicitly lists them in the saved view text.

Since we explicitly selected the newcol, it is part of the view see_all: 

select * from see_all;

You can make the column INVISIBLE even in the view. 

create view see_all
(x, y, newcol INVISIBLE)
as select x, y, newcol from t;

Related Topic