Invisible Not Null Column

Yesterday I attended John King (@royaltwit) session on Oracle 12c for developers. He gave an overview of all the goodies that are available to us as developers. The whole plugging and unplugging of database, though very cool and exiting, is most likely not very relevant to most developers.
When he discussed the possibility to have invisible columns in a table, it triggered the question: What happens when a NOT NULL column is made invisible? To see what would happen, I set up a small test case on my Oracle 12c database.

SQL> select banner
  2    from v$version
  3  /

BANNER
--------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE 12.1.0.1.0 Production
TNS for Linux: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

First create a table, two columns - both NOT NULL, and some data.

SQL> create table t
  2  (id   number not null
  3  ,text varchar2(10) not null
  4  )
  5  /

Table created.

SQL> insert into t values (1, 'Hello')
  2  /

1 row created.

SQL> insert into t values (2, 'World')
  2  /

1 row created.

SQL> commit;

Commit complete.

Next to change the TEXT column to be invisible.

SQL> alter table t modify (text invisible)
  2  /

Table altered.

SQL> desc t
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID        NOT NULL NUMBER

SQL> select * from t
  2  /

 ID
----------
  1
  2

The data that we entered is still there, or at least the ID values are still there.
So the column is now invisible, what if we try to add new data into it?

SQL> insert into t values (3)
  2  /
insert into t values (3)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."T"."TEXT")

Even though you can't see the column, the NOT NULL constraint is still enforced.
When you name the column explicitly, you can still do DML on it.

SQL> update t 
  2  set text = upper(text)
  3  /

2 rows updated.

But not like this

SQL> insert into t values (3, 'Goodbye');
insert into t values (3, 'Goodbye')
            *
ERROR at line 1:
ORA-00913: too many values

This following is still valid, and again shows that naming the columns in an INSERT statement is a good practice.

SQL> insert into t (id, text) values (3, 'Goodbye');

1 row created.