• Home
  • About
  •  

    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 0×400000020.

    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 comments on “Oracle 12cR1 Invisible Columns

    1. Jonathan Lewis on said:

      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.

    2. Alvaro Fdez on said:

      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.

    3. tom mize on said:

      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.

    4. Vijay Dodla on said:

      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

      • Julian Dyke on said:

        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

    Your email address will not be published. Required fields are marked *

    *

    HTML tags are not allowed.