Dear Readers,
In this article, we will see the Invisible columns in Oracle 12c.
Oracle 12C has introduced the concept of invisible columns. The idea of invisible columns is to hide the some columns in a table.
It can provide the security for crucial information maintained columns in a table.
It is similar to the view in prior versions of Oracle. Before Oracle version 12c we used views to hide some columns in a table while displaying data to the end users.
User can defined invisible columns during creation of table with the CREATE TABLE command.
User can modify the existing table columns to invisible with the ALTER command. By default table columns are always visible.
Invisible columns are still available for indexing and can be used for cost based optimizer.
If column is modified to invisible and later it changing it to visible the column order changes.
When you change an invisible column to visible then the COL# assigned value will be change to the highest (make it the end column of the table).
The order of the columns is changing in this case so we can’t go for explicit method wile inserting the data. We try to use the implicit method.
If the application uses ” SELECT * ” or ” INSERT ” without column names it might stop the task.
Once a table’s column is set to invisible, the following key statements will not work for the invisible column.
- SQL Statement SELECT * FROM
- SQL*Plus Command DESCRIBE
- PL/SQL variable declaration %ROWTYPE
The Following demo will help u to understand the invisible columns concept.
Creation of Normal Table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 |
TOM>>create table INVTAB(sno number,name varchar2(10)); Table created. TOM>>desc INVTAB; Name Null? Type --------------------------------------- -------- ------------------------ SNO NUMBER NAME VARCHAR2(10) TOM>>insert into INVTAB values(1,'a'); 1 row created. TOM>>insert into INVTAB values(2,'b'); 1 row created. TOM>>commit; Commit complete. TOM>>select * from INVTAB; SNO NAME ---------- ------------------ 1 a 2 b Invisible Column Creation TOM>>alter table INVTAB add(sal number invisible); Table altered. TOM>>desc INVTAB; Name Null? Type ----------------------------------------- -------- ---------------------- SNO NUMBER NAME VARCHAR2(10) set colinvisible on will display what columns are invisible in a table. TOM>>set colinvisible on; TOM>>desc INVTAB; Name Null? Type ----------------------------------------- -------- ---------------------- SNO NUMBER NAME VARCHAR2(10) SAL (INVISIBLE) NUMBER TOM>>select * from INVTAB; SNO NAME ---------- ---------------------------------------- 1 a 2 b TOM>>insert into INVTAB values(3,'c'); 1 row created. TOM>>select * from INVTAB; SNO NAME ---------- ---------------------------------------- 1 a 2 b 3 c TOM>>insert into INVTAB values(4,'d',100); insert into INVTAB values(4,'d',100) * ERROR at line 1: ORA-00913: too many values TOM>>desc INVTAB; Name Null? Type ----------------------------------------- -------- -------------------- SNO NUMBER NAME VARCHAR2(10) SAL (INVISIBLE) NUMBER TOM>>alter table INVTAB modify sal visible; Table altered. TOM>>desc INVTAB; Name Null? Type ----------------------------------------- -------- -------------------- SNO NUMBER NAME VARCHAR2(10) SAL NUMBER TOM>>select * from INVTAB; SNO NAME SAL ---------- ------------------- ------------------- 1 a 2 b 3 c TOM>>insert into INVTAB values (4,'d',100); 1 row created. TOM>>commit; Commit complete. |
ADDING COLUMNS IN B/W EXISTING COLUMNS BY USING INVISIBLE UTILITY
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
TOM>>create table INVTAB1 (col1 number,col2 number,col3 number,col4 number); Table created. TOM>>desc INVTAB1; Name Null? Type ---------------------------------- -------- ----------------------- COL1 NUMBER COL2 NUMBER COL3 NUMBER COL4 NUMBER TOM>>alter table INVTAB1 modify(col3 invisible,col4 invisible); Table altered. TOM>>desc INVTAB1; Name Null? Type ----------------------------------- -------- ---------------------- COL1 NUMBER COL2 NUMBER TOM>>set colinvisible on; TOM>>desc INVTAB1; Name Null? Type -------------------------------- -------- ----------------- COL1 NUMBER COL2 NUMBER COL3 (INVISIBLE) NUMBER COL4 (INVISIBLE) NUMBER TOM>>alter table INVTAB1 add col5 number; Table altered. TOM>>desc INVTAB1; Name Null? Type ------------------------------------ -------- -------------------- COL1 NUMBER COL2 NUMBER COL5 NUMBER COL3 (INVISIBLE) NUMBER COL4 (INVISIBLE) NUMBER TOM>>alter table INVTAB1 modify(col4 visible); Table altered. TOM>>desc INVTAB1; Name Null? Type ---------------------------------- -------- ---------------------------- COL1 NUMBER COL2 NUMBER COL5 NUMBER COL4 NUMBER COL3 (INVISIBLE) NUMBER TOM>>alter table INVTAB1 modify col3 visible; Table altered. TOM>>desc INVTAB1; Name Null? Type ------------------------------------ -------- -------------------- COL1 NUMBER COL2 NUMBER COL5 NUMBER COL4 NUMBER COL3 NUMBER |
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform
Loading...
Manikanta
Nice
Sri Kanth
Nice Iformation
Nikitha
Good article
sai
Nice info