Invisible Columns in Oracle 12c | Tech News

Invisible column is a new feature in Oracle 12c.  Invisible columns provide a convenient way to change a table’s structure without having to edit the existing applications using that table.

Invisible columns can be created

• at the time of table creation using CREATE TABLE command

                                SQL> create table inviscol_test

                                2  (compno number,

                                3  compname varchar(20),

                                4  compcode varchar(10) invisible);

                                Table created.

• existing visible columns can be modified to invisible using ALTER TABLE command

                                SQL> alter table inviscol_test modify (compname invisible);
                                Table altered.

How does it work?

• The insert statement where we don’t specify the column names will fail.

                                SQL> insert into inviscol_test values (101,'ABCD','A101');
                                insert into inviscol_test values (101,'ABCD','A101')
                                *
                                ERROR at line 1:
                                ORA-00913: too many values

                                Explicit column names should be included in the insert statement.

                                SQL>  insert into inviscol_test(compno, compname, compcode) values
                                (101,'ABCD','A101');
                                1 row created.

• If invisible columns are not listed in the insert statement they are set to NULL

                                SQL>  insert into inviscol_test values (201);
                                1 row created.

                                SQL> select compno,compname,compcode  from inviscol_test;
                                COMPNO     COMPNAME             COMPCODE
                                ---------- -------------------- ----------
                                101        ABCD                 A101
                                201        NULL                 NULL

• Invisible columns do not appear in SQL *Plus DESCRIBE. SET COLINVISIBLE ON will make the invisible columns visible.

                                SQL> desc inviscol_test;
                                Name                                      Null?    Type
                                ----------------------------------------- -------- ----------------------------
                                COMPNO                                             NUMBER

                                SQL> set colinvisible on

                                SQL> desc inviscol_test;

                                Name                                      Null?    Type
                                ----------------------------------------- -------- ----------------------------
                                COMPNO                                             NUMBER
                                COMPNAME (INVISIBLE)                               VARCHAR2(20)
                                COMPCODE (INVISIBLE)                               VARCHAR2(10)

• Invisible columns do not appear in the SELECT * queries. Explicit column names should be included.

                                SQL> select * from inviscol_test;

                                COMPNO
                                ----------
                                101
                                201

                                SQL> select compno,compname,compcode  from inviscol_test;

                                COMPNO     COMPNAME             COMPCODE
                                ---------- -------------------- ----------
                                101        ABCD                 A101
                                201        NULL                 NULL

• Invisible columns do not appear in the SQL Developer column display. But they do display in the table column list.

• When the existing invisible column is made visible it appears to move to the end of the table.
• External, cluster and temporary tables cannot have invisible columns
• Invisible columns are indexable
• Partitioning can be made on INVISIBLE columns

Related