Oracle 12cR1 Invisible Columns

Oracle 12cR1 – Invisible Columns

The first Oracle 12c New Feature that I decided to investigate was invisible columns. I chose this feature because it appeared to be simple and uncontroversial. It just goes to show how little I know.

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.

An Example

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;

How does it work?

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:

  • COL# – external column number
  • INTCOL# – internal column number
  • SEGCOL# – segment column number

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

So why is this controversial?

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.

End of post

5 responses to Oracle 12cR1 Invisible Columns

Julian,
I was off-net when the conversation was going on – but another little anomaly/effect to consider is what happens when you try to use dbms_metadata.get_ddl() to get the generating SQL. With your original table definition (c3 invisible), this is what you get (please edit if unreadable):

SQL> l
1* select dbms_metadata.get_ddl(‘TABLE’,’T1′, user) from dual
SQL> /

DBMS_METADATA.GET_DDL(‘TABLE’,’T1′,USER)
——————————————————————————–

CREATE TABLE “TEST_USER”.”T1″
( “C3″ NUMBER INVISIBLE,
“C1″ NUMBER,
“C2″ NUMBER,
“C4″ NUMBER
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE “TEST_8K”

1 row selected.

The c3 column is positioned (as you might expect) according to the col# value – so the implementation loses the original definition the moment it’s made.

It has also crossed my mind to wonder what would happen if you started mixing invisibility with some of those strange columns types (longs as the simplest, multi-column array types etc.) where the user-defined column position is re-arranged and system-generated invisible columns start to appear.

I for one second the pessimistic view of this. I work in a shop where a huge legacy codebase (Microsoft DAO/Jet technologies) we must still maintain, and a LOT of ODBC code would flaw using this feature. And (as checked from traces) it’s not only the “user” fault , for some joins sometimes the DAO/Jet driver inject calls which will try to get all columns via a wildcard. Admitely this is “our” problem, but there it is.

Seems like a useless feature that is not benign at all – this article has great inside on potential issues. Not sure why Oracle even added this feature, and they fail to realize that in the real world dbas cant prevent bad coding practices.

Julian,
Thanks for an insightful post.

But, I am still not clear how Mark’s workaround, as mentioned at the end of your blog, can come to rescue!
If you want to create a view with same name as original table’s, then why would you in first place make the column invisible!
Would really help if you can elaborate a bit.

Thanks
Vijay

Thinking about it again it does not make much sense the way I phrased it. The invisible column functionality could be achieved by renaming the table and then replacing it with a view that only selects the supported columns from the table. But I think Mark’s idea was smarter than that. I will ask him.

Leave a Reply

* Copy This Password *

* Type Or Paste Password Here *