Using Invisible Columns in Oracle Database 12c

By Yuli Vasiliev

Oracle Database 12c Release 1 (12.1) introduced the Invisible Columns feature that provides a convenient way to change a table’s structure without having to edit existing applications using that table. Invisible columns will not be seen in the SELECT list nor will be considered for insertion in an INSERT statement unless explicitly specified, thus not breaking SELECTs and INSERTs in existing code.

According to the documentation (https://docs.oracle.com/database/121/SQLRF/statements_7002.htm#SQLRF01402), you can use clause VISIBLE | INVISIBLE when defining a table column. The default is VISIBLE. This clause can be used in both the CREATE TABLE and ALTER TABLE statements. Once defined invisible, a column can be then converted to visible and vice versa, with the help of an ALTER TABLE statement, which is usually used when it comes to modifying existing column definitions.

The rest part of this article walks you through an example that illustrates this new feature in action. You’ll need access to an Oracle database 12c to follow the instructions below.

Let’s first create a new schema in the database, and grand it necessary priveleges. So, launch SQL Developer, SQLPLUS, or another tool you use to connect to the database, and connect to it using an account with DBA privileges.

Then, issue the following commands:

CREATE USER usr1 IDENTIFIED BY pswd;

GRANT CONNECT, RESOURCE TO usr1;

GRANT UNLIMITED TABLESPACE TO usr1;

Next, connect to the newly created schema and create a table in it, as follows:

CREATE TABLE emps(

ename VARCHAR2(100),

deptno VARCHAR2(3)

);

INSERT INTO emps VALUES(‘Maya Silver’, ‘310’);

INSERT INTO emps VALUES(‘John Polonsky’, ‘310’);

INSERT INTO emps VALUES(‘Jeff Jemison’, ‘320’);

Now that you have a table populated with data, what if you need to change its structure – for example, you need to add a new column — so that it does not disrupt the applications that already use the table. This is where using the Invisible Columns feature can come in very handy. You simply need to add the column as invisible with the ALTER TABLE statement, as follows:

ALTER TABLE emps ADD (bonus NUMBER(8,2) INVISIBLE);

From now on, the statements you write against the emps table may refer to this new column as if it were a regular column:

INSERT INTO emps(ename, deptno, bonus) VALUES(‘Tom Akopyan’, ‘320’, 1000.00);

On the other hand, the old code is still relevant. Thus, you can issue the following INSERT statement:

INSERT INTO emps VALUES(‘Tony Jones’, ‘320’);

You can even update early inserted rows, referring to the invisible column:

UPDATE emps SET bonus = 1500.00 WHERE deptno = ‘320’;

It is interesting to note however that any generic access to the table will not show the invisible column:

SELECT * FROM emps;

Maya Silver 310

John Polonsky 310

Jeff Jemison 320

Tom Akopyan 320

Tony Jones 320

The DESC command will not show it either:

Name Null Type

—— —- ————-

ENAME VARCHAR2(100)

DEPTNO VARCHAR2(3)

So, if your application does not know about its existence, there is no this column for it. To refer to it, you need to explicitly specify it in the SELECT list:

SELECT ename, deptno, bonus FROM emps;

Maya Silver 310

John Polonsky 310

Jeff Jemison 320 1500

Tom Akopyan 320 1500

Tony Jones 320 1500

Once you make sure your old code has been adjusted to work with the new column, you can make it visible with the ALTER TABLE statement, as follows:

ALTER TABLE emps MODIFY bonus VISIBLE;

From now on, the bonus column will be considered like any other regular column:

Maya Silver 310

John Polonsky 310

Jeff Jemison 320 1500

Tom Akopyan 320 1500

Tony Jones 320 1500

Conclusion

As you learned in this article, Oracle Database 12c feature Invisible Columns can be very helpful when you need to change the structure of tables that are already in use.

About the Author

Yuli Vasiliev is a software developer, freelance author, and consultant currently specializing in open source development, Java technologies, business intelligence (BI), databases, and service-oriented architecture (SOA). He is the author of Oracle Business Intelligence: The Condensed Guide to Analysis and Reporting (Packt, 2010) and PHP Oracle Web Development (Packt, 2007) as well as a series of other books on the Oracle technology.