Oracle Database 12c: Invisible columns

Hi everyone,

Today I will talk a little about a new feature on Oracle Database 12c, there are invisible columns. For use this feature there are some restrictions in some types of tables like:
Invisible columns are not supported in external tables, cluster tables, or temporary tables.
Also there are some kinds of operation that will not see a column set to invisible:
A statement with wildcard character, such as select * from mytables.
The SQL*Plus DESC command (Below I will tell how to see invisible columns with DESC command)
A PL/SQL %rowtype declaration
An insert into select * statement

Invisible columns can be made invisible when using thecommands:
create table
create view
alter view
alter table

Lets create a table:

Below we setting the INVISIBLE column with CREATE TABLE command:

SQL> CREATE TABLE TBINVISIBLECL (NAME VARCHAR2(20), SALARY NUMBER(4) INVISIBLE);

Now we are make a invisible column with ALTER TABLE command:

SQL> ALTER TABLE CLIENTS MODIFY (NAME INVISIBLE);

Note:
Making a column invisible will alter the column order in the table.

How to see invible columns with DESC command:

Using our table TBINVISIBLECL created above:

SQL> desc TBINVISIBLECL

Name Null? Type
————————- ——– —————————-
NAME VARCHAR2(20)

Now using the the SET COLINVIBLE ON:

SQL> SET COLINVISIBLE ON
SQL> desc TBINVISIBLECL
Name Null? Type
———————— ——— ——– —————————-
NAME VARCHAR2(20)
SALARY (INVISIBLE) NUMBER(4)

I hope that you enjoy!

Thanks, Felipe!

2 thoughts on “Oracle Database 12c: Invisible columns

  1. theflaz 13 de October de 2015 / 07:24

    Interesting but why might you use them?

    Like

    • Felipe Leal 24 de November de 2015 / 00:55

      Hi theflaz,

      Thank you very much for your comment, it is very important for me!
      This type of feature could be used for security reasons, for hide some important columns in the table.

      Have a great day!

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s