DBA Hub

📋Steps in this guide1/3

Invisible Columns in Oracle Database 12c Release 1 (12.1)

Oracle database 12c allows columns to be created or modified to make them invisible.

oracle 12cconfigurationintermediate
by OracleDba
17 views
1

Invisible Columns

Making a column invisible means it is no longer seen by , SQL*Plus or OCI describes and attributes. Invisible columns are still available for all actions, provided they are named explicitly. Some miscellaneous facts about invisible columns include the following. - Virtual columns can be made invisible. - A table can be partitioned by an invisible column, either during or after table creation. - External, cluster and temporary tables can not have invisible columns. - User-defined types can not contain invisible attributes. - You can not make system generated hidden columns visible. We can display invisible columns using the command by setting the option.

Code/Command (click line numbers to comment):

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
drop table tab1 purge;

create table tab1 (
  id          number,
  description varchar2(50) invisible
);

desc tab1;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID						    NUMBER

SQL>

insert into tab1 values (1);
commit;

select * from tab1;

	ID
----------
	 1

SQL>

insert into tab1 (id, description) values (2, 'two');
commit;

select id, description
from   tab1;

	ID DESCRIPTION
---------- --------------------------------------------------
	 1
	 2 TWO

SQL>

SQL> desc tab1
 Name						       Null?	Type
 ----------------------------------------------------- -------- ------------------------------------
 ID								NUMBER

SQL> set colinvisible on
SQL> desc tab1
 Name						       Null?	Type
 ----------------------------------------------------- -------- ------------------------------------
 ID								NUMBER
 DESCRIPTION (INVISIBLE)					VARCHAR2(50)

SQL>
2

Invisible Columns and Column Ordering

Invisible columns are not assigned a column order, so if an invisible column is made visible it is listed as the last column of the table.

Code/Command (click line numbers to comment):

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
drop table tab1 purge;

create table tab1 (
  a number,
  b number,
  c number invisible
);

column column_name format a15

select column_id,
       column_name,
       hidden_column
from   user_tab_cols
where  table_name = 'TAB1'
order by column_id;

 COLUMN_ID COLUMN_NAME	   HID
---------- --------------- ---
	 1 A		   NO
	 2 B		   NO
	   C		   YES

SQL> 

alter table tab1 modify b invisible;
alter table tab1 modify c visible;

select column_id,
       column_name,
       hidden_column
from   user_tab_cols
where  table_name = 'TAB1'
order by column_id;

 COLUMN_ID COLUMN_NAME	   HID
---------- --------------- ---
	 1 A		   NO
	 2 C		   NO
	   B		   YES

SQL>

alter table tab1 modify b visible;

select column_id,
       column_name,
       hidden_column
from   user_tab_cols
where  table_name = 'TAB1'
order by column_id;

 COLUMN_ID COLUMN_NAME	   HID
---------- --------------- ---
	 1 A		   NO
	 2 C		   NO
	 3 B		   NO

SQL>
3

Mandatory Invisible Columns

Making a column invisible does not affect its mandatory/optional status, as shown in the example below. For more information see: - Understand Invisible Columns - Invisible Columns in Oracle 12.1 Onward Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

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
drop table tab1 purge;

create table tab1 (
  id          number not null,
  description varchar2(50) not null,
  created_date date invisible not null
);

column column_name format a20

select column_id,
       column_name,
       nullable,
       hidden_column
from   user_tab_cols
where  table_name = 'TAB1'
order by column_id;

 COLUMN_ID COLUMN_NAME		N HID
---------- -------------------- - ---
	 1 ID			N NO
	 2 DESCRIPTION		N NO
	   CREATED_DATE 	N YES

SQL>

insert into tab1 values (1, 'one');
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("TEST"."TAB1"."CREATED_DATE")

SQL>

alter table tab1 modify created_date null;
insert into tab1 values (1, 'one');

1 row created.

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!