Invisible columns were introduced in Oracle 12.1.0.1
The basic idea of invisible columns is that they allow columns to be hidden from applications. In this regard they work very well. Columns can be made invisible in the CREATE TABLE statement or subsequently using an ALTER TABLE statement. By default columns are visible. Invisible columns can be made visible again using an ALTER TABLE statement.
The following example creates a table with an invisible column:
CREATE TABLE t1 ( c1 NUMBER, c2 NUMBER, c3 NUMBER INVISIBLE, c4 NUMBER );
Invisible columns do not appear in the output of the DESCRIBE command. For example:
SQL> DESCRIBE t1 Name Null? Type ----------------------------------------- -------- ---------------------- C1 NUMBER C2 NUMBER C4 NUMBER
SQL*Plus can optionally show hidden columns by setting the COLINVISIBLE option
SQL> SET COLINVISIBLE ON SQL> DESCRIBE t1 Name Null? Type ----------------------------------------- -------- ---------------------- C1 NUMBER C2 NUMBER C4 NUMBER C3 (INVISIBLE) NUMBER
Even though the column is invisible values, in that column can still be displayed and modified. For example:
SQL> INSERT INTO t1 (c1,c2,c3,c4) VALUES (11,12,13,14); 1 row created.
SQL> SELECT c1,c2,c3,c4 FROM t1;
C1 C2 C3 C4
---------- ---------- ---------- ----------
11 12 13 14
Note in the above example that all column names are listed in both the INSERT and SELECT statements
What happens if we omit the column names for the INSERT statement?
INSERT INTO t1 VALUES (21,22,23,24)
*
ERROR at line 1:
ORA-00913: too many values
The INSERT statement fails because we are attempting to insert into an invisible column (C3).
If we try again, omitting the value for C3 the INSERT statement will succeed:
SQL> INSERT INTO t1 VALUES (21,22,24);
1 row created.
SQL> SELECT c1,c2,c3,c4 FROM t1;
C1 C2 C3 C4
---------- ---------- ---------- ----------
11 12 13 14
21 22 24
Note that the value for C3 is NULL for the row inserted.
SELECT * does not return invisible columns. For example:
SQL> SELECT * FROM t1;
C1 C2 C4
---------- ---------- ----------
11 12 14
21 22 24
An invisible column can be made visible again. For example:
ALTER TABLE t1 MODIFY c3 VISIBLE;
A visible column can be made invisible. For example:
ALTER TABLE t1 MODIFY c3 INVISIBLE;
When you make a column invisible, it sets the COL# value for that column to zero in the data dictionary COL$ table. Consider the following example:
CREATE TABLE t2 ( c1 NUMBER, c2 NUMBER, c3 NUMBER, c4 NUMBER );
The data dictionary COL$ table contains one row for each table column. Each row has three column numbers:
In the data dictionary COL$ table, the values for COL#, INTCOL# and SEGCOL# are identical.
SQL> SELECT name,col#,intcol#,segcol#,TO_CHAR (property,'XXXXXXXXXXXX') FROM sys.col$ WHERE obj# = ( SELECT obj# FROM sys.obj$ WHERE name = 'T2' ); NAME COL# INTCOL# SEGCOL# PROPERTY ---------- ---------- ---------- ---------- ------------- C1 1 1 1 0 C2 2 2 2 0 C3 3 3 3 0 C4 4 4 4 0
The following statement makes column C3 invisible
ALTER TABLE t2 MODIFY c3 INVISIBLE; Table altered.
Column C3 is no longer reported by the DESCRIBE command:
SQL> DESCRIBE t2 Name Null? Type ----------------------------------------- -------- ------------------------ C1 NUMBER C2 NUMBER C4 NUMBER
In the data dictionary COL$ table the COL# value for column C3 has been changed to 0. The PROPERTY column now has a value of 0x400000020.
The COL# value for column C4 has been changed from 4 to 3. Effectively all subsequent columns are shifted up one place when a column is made invisible.
The values for INTCOL# and SEGCOL# are unchanged for all columns in the table.
SQL> SELECT name,col#,intcol#,segcol#,TO_CHAR (property,'XXXXXXXXXXXX') FROM sys.col$ WHERE obj# = ( SELECT obj# FROM sys.obj$ WHERE name = 'T2' ); NAME COL# INTCOL# SEGCOL# TO_CHAR(PROPE ---------- ---------- ---------- ---------- ------------- C1 1 1 1 0 C2 2 2 2 0 C3 0 3 3 400000020 C4 3 4 4 0
The following statement makes column C3 visible again
SQL> ALTER TABLE t2 MODIFY c3 VISIBLE; Table altered.
The C3 column is now reported by the DESCRIBE command:
SQL> DESCRIBE t2 Name Null? Type ----------------------------------------- -------- ----------------------- C1 NUMBER C2 NUMBER C4 NUMBER C3 NUMBER
In the data dictionary COL$ table the COL# value for column C3 has been changed to 4. The PROPERTY column now has a value of 0.
The COL# value for column C4 is unchanged. Effectively the newly visible column becomes the last column in the table.
The values for INTCOL# and SEGCOL# are unchanged for all columns in the table.
SQL> SELECT name,col#,intcol#,segcol#,TO_CHAR (property,'XXXXXXXXXXXX') FROM sys.col$ WHERE obj# = ( SELECT obj# FROM sys.obj$ WHERE name = 'T2' ); NAME COL# INTCOL# SEGCOL# TO_CHAR(PROPE ---------- ---------- ---------- ---------- ------------- C1 1 1 1 0 C2 2 2 2 0 C3 4 3 3 0 C4 3 4 4 0
The danger is that a column is inadvertently made invisible and then made visible again causing an application to fail if it executes a SELECT * or INSERT statement that does not specify a column list. Worse, it is possible that incorrect data is inserted into the database resulting in a corruption that may be difficult to detect initially.
The Oracle position is that this is expected behaviour. For years they have been recommending that explicit column lists are used in both SELECT and INSERT statements. Therefore any issues arising from invisible columns are the fault of the user and the user alone.
My problem with this feature is that VISIBLE is not an exact inverse of INVISIBLE. If you make a column invisible and then visible again you do not end up back where you started. Inadvertent use of this feature, through a GUI development tool or just curiosity could cause older applications to fail. I still see lots of applications which do not specify column lists for SELECT or INSERT statements. It is difficult to check an application for this type of statement during testing. It would be possible to search through V$SQL at regular intervals but who has time to do that?
If Oracle were serious about outlawing SELECT * statement and INSERT statements without column lists, they could have introduced a hidden parameter to disable them. Alternatively they could have introduced a hidden parameter to disable invisible columns. Perhaps they have, but I cannot find them in X$KSPPI.
In my opinion invisible columns are a nice, though arguably unnecessary, feature which have the potential to cause a really nasty issue for someone. Problems are likely to be few and far between, but there is still an increased probability of hitting one than would have existed in Oracle 11gR2. A database corruption caused by this feature could potentially go undetected for months or even years by which time recovery may be impossible. I would expect that users will try the feature initially in a development or test environment rather than production, but it still has the potential to impact or delay projects.
My friends in the Oak Table have divided opinions on this issue. The optimists agree with the Oracle approach; if you have been coding your applications correctly then you will not have any problems. The pessimists, including myself, think that this feature could cause significant damage to some legacy applications and have less faith in the quality of some application code.
A possible work-around for this issue has been proposed by Mark Farnham. He suggests renaming the original table and creating a view with a view name the same as the original table name to protect legacy applications. I think this would definitely solve the problem, though I suspect it might invalidate support for some packages.