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.