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

    – Advertisement –
  • at the time of table creation using CREATE TABLE command
                                SQL> create table inviscol_test

                                2  (compno number,

%MINIFYHTML3143f7e11335d5c9b0dcb4319fd7253811%
                                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

 

Prosyscom Tech News publishes relevant guest contributions from the community. Share your honest opinions and expert knowledge by submitting your content here.

You might also like More from author