Oracle with the release of 12c introduced new feature called invisible columns which basically allows developers to create new columns (as I would say) not visible to the world 🙂 . It enables end-users to continue accessing the application while developers enhancing the application. These kind of columns are not visible to the application because in order to access the column we have to explicitly specify the column name in the select statement. “SELECT * FROM …” will also not show the column. This is because COLUMN_ID column in _TAB_COLUMNS dictionary views which is used to determine the order of columns retrieved when you run ” SELECT * FROM …” has a NULL value.
How do you define invisible columns? It is very straightforward with the INVISIBLE keyword.
If I describe previously created table we won’t see the columns that are set invisible.
Of course there is a solution for that. We can set COLINVISIBLE command in sqlplus, lets try.
In order to check which columns are invisible we have to search NULL value for COLUMN_ID in *_USER_TAB_COLUMNS dictionary views.
When we’re going to make the column visible the system will generate new highest number for COLUMN_ID column.
We can also specify INVISIBLE on virtual columns.
Lets insert sample data and see the effect.
As you can see we didn’t got COL2 column because it’s set to INVISIBLE. Lets specify the column in the SELECT statement.
The restrictions for this feature is that you can’t use it on temp, external or cluster tables. You can’t also make system-generated invisible columns visible.
You can comment via Twitter.
Link for this post: comment