Are Invisible Columns Really Invisible?

Today’s question: How are invisible columns treated with DESC, INFO and the GUI equivalent?

Super quick, an INVISIBLE column is one that the user doesn’t see. They won’t be able to access it unless they know about it.

Security by obscurity? No. Tom Kyte shows a few examples of the features usefulness, especially when adding columns to a table and not breaking any SELECT * FROMs in your code. Of course, you don’t HAVE any SELECT * FROMs in your code, because you’re smart and read blogs and stuff…

Oracle-Base has a nice summary of this topic, a new feature in Oracle Database 12c…

Or you can read the Oracle Docs…

VISIBLE | INVISIBLE
Use this clause to specify whether column is VISIBLE or INVISIBLE. The default is VISIBLE.

INVISIBLE columns are user-specified hidden columns. To display or assign a value to an INVISIBLE column, you must specify its name explicitly. For example:

The SELECT * syntax will not display an INVISIBLE column. However, if you include an INVISIBLE column in the select list of a SELECT statement, then the column will be displayed.

You cannot implicitly specify a value for an INVISIBLE column in the VALUES clause of an INSERT statement. You must specify the INVISIBLE column in the column list.

You must explicitly specify an INVISIBLE column in Oracle Call Interface (OCI) describes and PL/SQL %ROWTYPE attributes.

You can configure SQL*Plus to allow INVISIBLE column information to be viewed with the DESCRIBE command. Refer to SQL*Plus User’s Guide and Reference for more information.

That last bit is interesting.

Tom talks about that here.

SQL Developer and SQLcl and Invisible Columnsn

[text]
SQL> set colinvisible ON
SP2-0158: unknown SET option "colinvisible"
SQL>
[/text]

Looks like I have a bug to file for SQLcl 🙂

One more thing…

Those of you freaking out that 11gR2’s normal support lifecycle is coming to a close, did you notice some of these 12c posts I’ve referenced are going on two years old? 12c isn’t the new shiny thing in the store anymore. I mean, it’s still shiny, it’s just been out for awhile, and you need to get used to it. Or something. It’s a Saturday and my marketing-fu is a bit rusty.

One more, one more thing

I started answering this question before I even saw Kent put it out there…

So, can we specify a colum as Invisible in in Data Modeler? Does it then generate the correct DDL?

In a word, Yes.

One last, one more thing

Hiding the invisible columns in your data model…interesting idea. You want to describe and display your data model. But…maybe for some reason you don’t want to share the fact that you’re hiding something. Ok.

Simply right-click on your diagram and select ‘show/hide elements.’