Oracle 12c New Features For Developers (Part 2)

Having covered top-n queries in Oracle 12c New Features part 1, let's now look at another new feature - invisible columns. At this point, you might be thinking that invisible columns already existed in Oracle 11g, however you would be mistaken and most likely thinking of either invisible indexes or virtual columns both of which were new features in 11g.

Invisible Columns

To ensure there's no confusion, let's look at the difference between virtual columns and invisible columns.

A virtual column is effectively a function-based column - its value is derived from the values of other columns in the table and the value is not stored in the Oracle database.

The values of invisible columns on the other hand are stored in Oracle (unless they're virtual invisible columns) but the columns are not displayed unless explicitly named in a query and also have to be refrerred to explicitly in insert statements.

Let's look at a few examples.

Example 1 - Creating a table with an invisible column

We can create an ordinary invisible column with a create table statement like so:

SQL> create table emp
  2  (emp_id number
  3  ,emp_name varchar2(50)
  4  ,salary number INVISIBLE);

When we describe the table the invisible column isn't shown.

sql> desc emp
Name     Null Type
-------- ---- ------------
EMP_ID        NUMBER
EMP_NAME      VARCHAR2(50)

You can change this default behaviour in SQL*Plus by typing

SQL> set colinvisble on

For more details on this, see the SQL*Plus User's Guide and Reference

Example 2 - Querying a table with an invisible column

When we query the table, unless the invisible columns are named in the query they are not displayed.

SQL> select * from emp;

EMP_ID EMP_NAME
------ --------

No data is displayed as the table is empty.

When we specify the invisible column in the query it is displayed

SQL> select emp_id,emp_name,salary from emp;

EMP_ID EMP_NAME
------ --------

Again, no data is displayed as the table is empty.

Example 3 - Inserting into a table with an invisible column

When we insert data into the table we must either explicitly include the invisible column in the insert statement or not provide a value for it.

SQL> insert into emp values (1,'Joe Smith') --THIS IS VALID
SQL> /

1 row created

In the above insert statement we didn't specify the columns and only provided values for the twovisible columns.

SQL> insert into emp (emp_id, emp_name, salary)
  2  values (3,'Tom Tucker',29000) -- THIS IS ALSO VALID
SQL> /

1 row created

In the above insert statement we specified the columns and provided values for the two visible columns and the invisible column.

SQL> insert into emp values (2,'Jack Jones', 20000)--INVALID
SQL> /
insert into emp values (2,'Jack Jones', 20000);

               *
ERROR at line 1:
ORA-00913: too many values

In the above insert statement we didn't specify the columns but provided values for the two visible columns and the invisible column, resulting in an error.


Oracle Tips & Tricks to SKYROCKET Your Career!

If you're not already a subscriber to Oracle Tips and Tricks, you're  missing out on a myriad of tips and techniques to help you become a better, faster, smarter developer. Subscribe now and ignite your career.


Querying data dictionary views

Even though the invisible columns are not displayed by default when we describe the table, they are shown when we query data dictionary views such as all/dba/user_tab_columns.

SQL> select column_name from user_tab_columns<br>
   2 where table_name='EMP';

COLUMN_NAME
-----------
EMP_ID
EMP_NAME
SALARY

Changing the visibility of columns

Like other column properties, the visible/invisible property can be changed by using the alter table command:

SQL> desc emp

Name     Null      Type

-------- ---- ------------
EMP_ID        NUMBER
EMP_NAME      VARCHAR2(50)

SQL> alter table emp modify salary visible;

Table altered.

SQL> desc emp

Name     Null      Type
-------- ---- ------------
EMP_ID          NUMBER
EMP_NAME        VARCHAR2(50)
SALARY          NUMBER


An article like this will only take you so far - the quickest way to learn Oracle is to take a formal training course. We partner with Gogo training to provide high quality, online, self-paced training courses taught by industry experts to help you gain the kowledge and skills you need to advance your career.

Go to GogoTraining to see the online training course available and enter "ASKTHEORACLE" as the coupon code when you check out to get a 5% discount.

Also check out the Oracle, SQL and PL/SQL tutorials on our site. If you can't find what you're looking for try the search facility and if you still can't find the answer to your question, ask us.

Return to part 1 of Oracle 12c new features for developers