Invisible Columns in Oracle 12c | Tech News

Chemicloud Web Hosting

Web Hosting

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

 

You might also like More from author

Comments are closed.