Oracle 12c – Invisible Columns!

Oracle 12c – Invisible Columns

Beginning with Oracle 12c columns may be marked “INVISIBLE” in CREATE and ALTER TABLE statements.

#ff0000;">What the?!?!?!?!

Suppose you have code that uses “SELECT *” (no, really this sometimes occurs) and you need to add a column or columns? Oops! Code using “SELECT *” now breaks as does code using INSERT statements without a column list. (I know, ALL of your system’s code uses explicit column name lists in SELECT and INSERT ; this does not apply to your shop… (please excuse the sarcasm))

Here’s how it works:

  • Invisible columns do not appear in SQL*Plus DESCRIBE
  • Using SQL*Plus SET COLINVISIBLE ON will cause invisible columns to appear when the table is DESCribed
  • Invisible columns do not appear in SQL Developer column display
    (at least, it does show in SQL Developer table column list)
  • Invisible columns do not appear in SELECT * queries
  • Invisible columns are not included in PL/SQL %ROWTYPE
  • Invisible columns that are null-capable may be omitted from INSERT statements
  • Invisible columns may be inserted into INSERT statements by name
  • When made visible, formerly-invisible columns appear to move to the end of table
    (hmmm, why?)
    The answer is best described by knowing what happens when a column is marked invisible
  • The database changes the column number to 0; if you have access (probably only if your are a DBA); you can see this with the following query (must be SYS or be GRANTed access):
SELECT c.name,c.type#,c.col#,c.intcol#, c.segcol#,
         TO_CHAR (c.property,'XXXXXXXXXXXX') AS property
FROM sys.col$ c, sys.obj$ o, sys.user$ u
WHERE c.obj# = o.obj#AND o.owner# = u.user#
  AND u.name = ‘MYUSER’
  AND o.name = ‘MYTABLE’;
  • Col# is set to 0
  • Property is set to x’40000020′

Once a column is made visible again, it takes the next available column number. So, you could intentionally “reorganize” a table (or at least the output from SELECT *) with potentially disastrous results! (let your imagination roam…)

Sample Statements

Here’s some code illustrating how invisible columns work.

First the table is created with invisible column

Invisible Column Creation

drop table invisible_test;
create table invisible_test 
 (id number,
  col1 varchar2(10),
  col2 varchar2(10) invisible,
  col3 varchar2(10));
desc invisible_test;

Finding Invisible Columns in the Catalog

The following select will display column names and hidden indicator using the Oracle catalog.

select owner,table_name,column_name,
  hidden_column,column_id
from all_tab_cols
where owner = user
  and table_name = 'INVISIBLE_TEST';

Inserting Rows With Invisible Columns

The following statements INSERT rows into the table with invisible columns:

  • Invisible columns may be omitted from column lists
  • Invisible columns may be named in column lists
  • If invisible columns are included in values; they MUST be named in column list
SQL> insert into invisible_test (id,col1,col3) values (1,'a','a');
1 row inserted.
SQL> insert into invisible_test values (2,'b','b');
1 row inserted.
SQL> insert into invisible_test values (3,'c','c','c');
Error starting at line : 12 in command -
insert into invisible_test values (3,'c','c','c')
Error at Command Line : 12 Column : 13
Error report -
SQL Error: ORA-00913: too many values
SQL> -- fail
SQL> insert into invisible_test (id,col1,col2,col3) values (3,'c','c','c');
1 row inserted.
SQL> insert into invisible_test (col1,col3) values (4,'d');
1 row inserted.
SQL> insert into invisible_test values (5,'e');
Error starting at line : 15 in command -
insert into invisible_test values (5,'e')
Error at Command Line : 15 Column : 13
Error report -
SQL Error: ORA-00947: not enough values
SQL> insert into invisible_test values (5,'e','e');
1 row inserted.
  • Inserts without column specifications act as if the column is truly invisible
  • Inserts with column specifications may include invisible column

Queries and Invisible Columns

  • select * does not “see” the invisible column
  • select may name invisible columns or use invisible columns
SQL> select * from invisible_test;
ID         COL1       COL3
---------- ---------- ----------
1          a          a
2          b          b
3          c          c
4          d
5          e          e
SQL> select id,col1,col2,col3 from invisible_test;
ID         COL1       COL2       COL3
---------- ---------- ---------- ----------
1           a                    a
2           b                    b
3           c          c         c
4           d
5           e                     e

Making Columns Visible

  • Columns may be made visible or invisible using ALTER TABLE
  • Columns made visible appear at the bottom of the list due to column number reassignment
SQL> alter table invisible_test modify col2 visible;  
Table INVISIBLE_TEST altered.
SQL> desc invisible_test;
Name Null Type
---- ---- ------------
ID       NUMBER
COL1     VARCHAR2(10)
COL3     VARCHAR2(10)
COL2     VARCHAR2(10)
SQL> select * from invisible_test;
ID         COL1       COL3       COL2
---------- ---------- ---------- ----------
1          a          a
2          b          b
3          c          c          c
4          d
5          e                     e    

– Visible column shows up at end of describe and select *

Invisible Columns – Use and Maintenance

Invisible columns allow you to add columns to a table without “breaking” any code that has the misfortune to include “SELECT *”.

Making different columns invisible and then visible may cause code using SELECT * to behave erratically or fail.

An invisible column’s NOT NULL or NULL status is unchanged.

Invisible columns might be useful as a (pretty weak) kind of security.

Virtual columns can be made invisible and vice-versa.

Tables may be partitioned by an invisible column, either during or after table creation.

External, cluster and temporary tables may not have invisible columns.

User-defined types may not contain invisible attributes.

System-generated invisible columns may not be made visible.

Conclusion

Invisible columns allow us to add columns to tables without fear of breaking code that makes the unfortunate use of “SELECT *” or “INSERT” without column lists. This is particularly valuable when your code base includes purchased software that may not be modified for contractual or other reasons.

In some cases, Invisible columns will provide an excellent option for working with existing systems. Use of “invisible/visible” to reorder columns in output is probably a bad idea; you should probably recreate the tables in the desired column sequence.