Oracle Invisible Columns (12c new Features)

Hi Everyone,

In this article, I’ll discuss a new feature that has come with the Oracle 12c. I hope this will be a beneficial article in terms of awareness.

With the Oracle 12c, there has been a feature added to tables about the visibility of columns. Now we can easily make the desired columns in the table visible or invisible. In my opinion, this feature was necessary because it seems practical to allow everyone in the tables, which are generically reached, to see every column without creating “view”.

Now, let’s see how an invisible column is created.

CREATE TABLE invColExm
(
colA NUMBER,
colB NUMBER INVISIBLE,
colC VARCHAR2 (100)
);

As can be seen from the example above, after determining the data type the column can be made invisible by inserting the phrase “invisible” to associated column. Now, let’s see in which actions this column is invisible.

1 – SELECT * FROM invColExm; — colB is invisible.

2 -DESC invColExm; — colB column is invisible in the list.

3– /in the table referenced with* ROWTYPE ,
the invisible column cannot be reached,
therefore it’s invisible in here too*/

— This block does not throw an error.
DECLARE
v_tb invColExm%ROWTYPE;
BEGIN
v_tb.colA := 1;
v_tb.colC := ‘Deneme’;
END;

— This block does throw an error.
DECLARE
v_tb invColExm%ROWTYPE;
BEGIN
v_tb.colA := 1;
v_tb.colC := ‘Deneme’;
v_tb.colB := 12;
END;

— This block does throw an error.
DECLARE
v_tb invColExm%ROWTYPE;
BEGIN
v_tb.colA := 1;
v_tb.colC := ‘Deneme’;
v_tb.colB := 12;
END;

However, there are exceptions for the phrases of insert, delete and update. If I’m aware of the presence of the invisible column, I can run a DML on this column. If we give an example:

– This phrase perceives the colB column as invisible and writes the oncoming values to colA and colC respectively.
INSERT INTO invColExm VALUE
(1,’Deneme’);

–If the user is aware of the invisible column, s/he can write this column’s name and allow values to be written in this column too.
INSERT INTO invColExm (colA, colB, colC)
VALUES (12, 10, ‘Deneme’);

–If I’m aware of the existence of the invisible column, I can use it for the “update” phrase too.
UPDATE invColExm
SET colB = 20;

– If I’m aware of the existence of the invisible column, I can use it for the “delete” phrase too.
DELETE invColExm
WHERE colB = 20;

We can make the column that has been set invisible, visible with the phrase below. However, the column that becomes visible is not seen in the created order but is seen as the last column.

ALTER TABLE invColExm
MODIFY colB VISIBLE;

With the phrase above, the invisible column can become visible. The opposite of this action which is making visible column invisible is also possible. At this point, the queries running previously while this column was being coded (eg: select colA,ColB from invColExm) will continue running. However, with “*” character, this column will not be inserted. Thus while making a column invisible, the codes querying that column should not be ignored.

Also, it should not be forgotten that there is no invisible column support in the table types below.

External tables – Cluster tables -Temporary tables

I hope this has been a beneficial article in terms of awareness.

REFERENCES

http://www.oracle.com/technetwork/articles/database/invisible-columns-odb12c-2331522.html

 

Advertisements

About ... from Emrah METE

Bilgisayar Mühendisi
This entry was posted in Oracle, Root and tagged , , , , . Bookmark the permalink.

One Response to Oracle Invisible Columns (12c new Features)

  1. Pingback: OTN Appreciation Day : Adding Invisible Column | Emrah METE

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