This blog post discusses invisible columns - a concept introduced in Oracle 12C. We provide some Q&A on this concept with working examples. Feel free to add your own experience on this concept as comments to this post.
Create an Invisible Column in a Table:
To create an invisible column in Oracle DB at the time of table creation, append the keyword
invisible against the column name as shown below.PDB1@ORCL> create table t1 (c1 number, c2 number invisible)
2 /
Table created.
If you want to add an invisible column to an existing table, you can use the
ALTER command with invisible suffix as provided below:PDB1@ORCL> alter table t1 add (c3 number invisible)
2 /
Table altered.
You cannot have a table with all columns as invisible. If you attempt to do this at the time of table creation, you get an error
When you attempt to
In order to insert data into invisible column in Oracle, you must specify the column name in the insert statement. For the table T1 we created earlier, if you attempt to insert without passing the column names, you get "too many values" error. This is shown below:
This is advantageous in situations where you want to keep a column in invisible mode till all supporting applications are updated - you can then make the column visible again.
On the same lines as of
The way columns are returned in SQL output, depends on the
I also found that SQL Loader Express Mode in Oracle 12C automatically ignores invisible columns in a table during load and loads them with a value of NULL. I was not able to load data into invisible columns in express mode.
However, in standard mode (with the help of a control file), I was able to load data into invisible columns using SQL Loader. All it requires is the mention of the column names in the control file. A sample control file is provided below for reference: (c2 and c3 are invisible columns)
ORA-54039 which tells you that you should have atleast one column in the table that is NOT invisible. This is shown below:PDB1@ORCL> create table t2 (c1 number invisible,c2 number invisible)
2 /
create table t2 (c1 number invisible,c2 number invisible)
*
ERROR at line 1:
ORA-54039: table must have at least one column that is not invisible
On the same lines, if you drop all the visible columns in a table trying to leave a table only with invisible columns, you get the error ORA 12983 - cannot drop all the columns in a table - even though you have some invisible columns. To simulate this, let us try to drop the column c1 in table t1 created earlier and observe the output:PDB1@ORCL> alter table t1 drop column c1
2 /
alter table t1 drop column c1
*
ERROR at line 1:
ORA-12983: cannot drop all columns in a table
On the same lines, you can make a visible column invisible and vice versa by using the commands as shown below: (you get same ORA 54039 if you try to make all the columns invisible)PDB1@ORCL> desc t1
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
C1 NUMBER
C2 (INVISIBLE) NUMBER
C3 (INVISIBLE) NUMBER
PDB1@ORCL> alter table t1 modify c2 visible
2 /
Table altered.
PDB1@ORCL> desc t1
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
C1 NUMBER
C2 NUMBER
C3 (INVISIBLE) NUMBER
PDB1@ORCL> alter table t1 modify c2 invisible
2 /
Table altered.
PDB1@ORCL> alter table t1 modify c1 invisible
2 /
alter table t1 modify c1 invisible
*
ERROR at line 1:
ORA-54039: table must have at least one column that is not invisible
Check if a Table has Invisible Columns:
When you attempt to
DESCribe a table in SQLPlus that has invisible columns, SQL Plus by Default do not show them in the output. This is controlled by a parameter COLINVISIBLE. When you set this to ON, you can see the invisible columns also in the output. This is explained below:PDB1@ORCL> desc t1
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
C1 NUMBER
PDB1@ORCL> show colinvisible
colinvisible OFF
PDB1@ORCL> set colinvisible on
PDB1@ORCL> desc t1
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
C1 NUMBER
C2 (INVISIBLE) NUMBER
C3 (INVISIBLE) NUMBER
PDB1@ORCL>
Inserting Data into Invisible Columns:
In order to insert data into invisible column in Oracle, you must specify the column name in the insert statement. For the table T1 we created earlier, if you attempt to insert without passing the column names, you get "too many values" error. This is shown below:
PDB1@ORCL> desc t1
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
C1 NUMBER
C2 (INVISIBLE) NUMBER
C3 (INVISIBLE) NUMBER
PDB1@ORCL> insert into t1 values (1,2,3)
2 /
insert into t1 values (1,2,3)
*
ERROR at line 1:
ORA-00913: too many values
To prevent this error and insert data into invisible column, provide the column name in the insert as provided below:PDB1@ORCL> desc t1
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
C1 NUMBER
C2 (INVISIBLE) NUMBER
C3 (INVISIBLE) NUMBER
PDB1@ORCL> insert into t1(c1,c2,c3) values (1,2,3)
2 /
1 row created.
This is advantageous in situations where you want to keep a column in invisible mode till all supporting applications are updated - you can then make the column visible again.
Display Data From Invisible Columns
On the same lines as of
INSERT, you have to specify the column name in your SELECT clause, to see the data in an invisible column. A "SELECT *" would not display columns that are invisible - as shown below:PDB1@ORCL> insert into t1(c1,c2,c3) values (1,2,3)
2 /
1 row created.
PDB1@ORCL> select * from t1
2 /
C1
----------
1
PDB1@ORCL> select c1,c2,c3 from t1
2 /
C1 C2 C3
---------- ---------- ----------
1 2 3
Column Ordering in SQL Query Results:
The way columns are returned in SQL output, depends on the
COLUMN_ID value in ALL_TAB_COLUMNS. To understand this, consider the SQL output below:PDB1@ORCL> select column_name,column_id from all_tab_columns where table_name='T1';
COLUMN_NAM COLUMN_ID
---------- ----------
C3 3
C2 2
C1 1
PDB1@ORCL> select * from t1;
C1 C2 C3
---------- ---------- ----------
1 2 3
The "SELECT" query returns C1, C2 and C3 in the order, because of the column_id values as shown in the previous output. Now, this COLUMN_ID has a value of NULL for invisible columns. When you make an invisible column visible, the column_id assigned would be the max of the column _id values +1 to the modified column. Let us make c1 invisible and then visible again, and observe the output.PDB1@ORCL> desc t1
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
C1 NUMBER
C2 NUMBER
C3 NUMBER
PDB1@ORCL> alter table t1 modify c1 invisible;
Table altered.
PDB1@ORCL> select column_id,column_name from all_tab_columns where table_name='T1';
COLUMN_ID COLUMN_NAM
---------- ----------
2 C3
1 C2
C1
PDB1@ORCL> alter table t1 modify c1 visible;
Table altered.
PDB1@ORCL> select column_id,column_name from all_tab_columns where table_name='T1';
COLUMN_ID COLUMN_NAM
---------- ----------
2 C3
1 C2
3 C1
PDB1@ORCL> select * from t1;
C2 C3 C1
---------- ---------- ----------
2 3 1
PDB1@ORCL>
As per above, when C1 was changed to "invisible" the column_id got set as NULL. When C1 was made visible again, the column ID assigned to C1 is now 3 instead of 1. Also, the "SELECT *" returned output in a different order as per above. You need to be careful about this when playing around with invisible columns.SQL Loader - Loading Data into Invisible Columns in Oracle 12C
I also found that SQL Loader Express Mode in Oracle 12C automatically ignores invisible columns in a table during load and loads them with a value of NULL. I was not able to load data into invisible columns in express mode.
However, in standard mode (with the help of a control file), I was able to load data into invisible columns using SQL Loader. All it requires is the mention of the column names in the control file. A sample control file is provided below for reference: (c2 and c3 are invisible columns)
load data
infile 'a1.dat'
into table t1
fields terminated by "," optionally enclosed by '"'
( c1,c2,c3 )
Output:
PDB1@ORCL> select c1,c2,c3 from t1
2 /
C1 C2 C3
---------- ---------- ----------
1 2 3
1 2 4
The results in express mode is provided below:[oracle@localhost sqlldrtest]$ more a.dat
1,2,3
2,3,4
5,6,7
/* Results after express mode load */
/* Invisible columns not loaded in express mode */
PDB1@ORCL> select c1,c2,c3 from t1
2 /
C1 C2 C3
---------- ---------- ----------
1 <null> <null>
2 <null> <null>
5 <null> <null>
No comments:
Post a Comment