I like it! Have you ever been in a situation you should add a column to a table but you cannot because the current application would stop working? I have and many times. Now we can do it immediately and give some time for the developers to fix the application.

This feature, invisible column, was introduced in Oracle Database 12c. An invisible column is a table column that cannot be seen using for instance SELECT * FROM or DESCRIBE command. It cannot be seen with %ROWTYPE attribute in PL/SQL variable declarations either. To be able to see it you must explicitly specify it in queries.

Let’s create EMP table with column ENAME defined as invisible.

 

CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10) INVISIBLE,
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
/

 

When inserting rows to a table that has invisible columns, and also to that column, make sure to define the column names. If you do not do that you will get an ORA-00913: too many values error.

If you do not want to insert a value to the invisible column, inserts without column lists can be done. If the invisible column has been defined obligatory there is no problem but if it has been defined mandatory make sure to define the default for it using DEFAULT clause.

Another fun thing with invisible columns is that when you define the column invisible and then visible, the column moves to the last column in the table (logically of course, no physical moves).

ALTER TABLE EMP
MODIFY ENAME VISIBLE;

 

DESC EMP

Name     Null     Type        

——– ——– ————

EMPNO   NOT NULL NUMBER(4)  

JOB               VARCHAR2(9)

MGR               NUMBER(4)  

HIREDATE         DATE        

SAL               NUMBER(7,2)

COMM             NUMBER(7,2)

DEPTNO           NUMBER(2)  

ENAME             VARCHAR2(10)

Sometimes this might be useful for you…

 

Cheers

Heli

Advertisements