As of Oracle 12.1, you can now define invisible columns in relational tables. An invisible column is a user-defined hidden column, which means that if you want to display or assign a value to an invisible column, you must specify its name explicitly.
• You can make a column invisible during table creation or during column addition to a table.
• You can also alter a table to make a visible column invisible.
Why would you make a column invisible? There are not many reasons why you would suddenly make a column invisible, but one situation that comes to mind is where you might want to test the waters before dropping the column from table – to figure out if something breaks or someone yells. Oracle provides an option to mark a column as UNUSED before you DROP, and do ALTER TABLE … DROP UNUSED COLUMNS at a later time. Once you mark a column as UNUSED, there is no going back to undo the action. So, marking it INVISIBLE before drop is a good idea. Another use could be that you have a running application used by many teams – before you collaborate with everyone on a table change, you could test the changes in the table by creating the new column as invisible, do your basic tests, then talk to the other teams and make the column visible to all.
o Virtual columns can be made invisible.
o A table can be partitioned by an invisible column, either during or after table creation.
o External, cluster and temporary tables can not have invisible columns.
o User-defined types can not contain invisible attributes.
o You can not make system generated hidden columns (ROWID,ROWNUM) visible.
The database usually stores columns in the order in which they were listed in the CREATE TABLE statement. If you add a new column to a table, then the new column becomes the last column in the table’s column order.
When a table contains one or more invisible columns, the invisible columns are not included in the column order for the table. Column ordering is important when all of the columns in a table are accessed. For example, a SELECT * FROM statement displays columns in the table’s column order. Because invisible columns are not included in this type of generic access of a table, they are not included in the column order.
When you make an invisible column visible, the column is included in the table’s column order as the last column.
When you make a visible column invisible, the invisible column is not included in the column order, and the order of the visible columns in the table might be re-arranged.
SQL> CREATE TABLE mytable
2 (
3 a INT,
4 b INT INVISIBLE,
5 c INT
6 );
Table created.
Column Column Order
a 1
c 2
SQL> ALTER TABLE mytable MODIFY (b VISIBLE);
Table altered.
Column Column Order
a 1
c 2
b 3
SQL> CREATE TABLE mytable2
2 (
3 x INT,
4 y INT,
5 z INT
6 );
Table created.
Column Column Order
x 1
y 2
z 3
SQL> ALTER TABLE mytable2 MODIFY (y INVISIBLE);
Table altered.
Column Column Order
x 1
z 2
SQL> ALTER TABLE mytable2 MODIFY (y VISIBLE);
Table altered.
Column Column Order
x 1
z 2
y 3Invisible 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.
SQL> CREATE TABLE tab2
2 (
3 a NUMBER,
4 b NUMBER,
5 c NUMBER INVISIBLE
6 );
Table created.
SQL> COLUMN column_name FORMAT A15
SQL> SELECT column_id,
2 column_name,
3 hidden_column
4 FROM user_tab_cols
5 WHERE table_name = 'TAB2'
6 ORDER BY column_id;
COLUMN_ID COLUMN_NAME HID
---------- --------------- ---
1 A NO
2 B NO
C YES
SQL> ALTER TABLE tab2 MODIFY b INVISIBLE;
Table altered.
SQL> ALTER TABLE tab2 MODIFY c VISIBLE;
Table altered.
SQL> SELECT column_id,
2 column_name,
3 hidden_column
4 FROM user_tab_cols
5 WHERE table_name = 'TAB2'
6 ORDER BY column_id;
COLUMN_ID COLUMN_NAME HID
---------- --------------- ---
1 A NO
2 C NO
B YES
SQL> ALTER TABLE tab2 MODIFY b VISIBLE;
Table altered.
SQL> SELECT column_id,
2 column_name,
3 hidden_column
4 FROM user_tab_cols
5 WHERE table_name = 'TAB2'
6 ORDER BY column_id;
COLUMN_ID COLUMN_NAME HID
---------- --------------- ---
1 A NO
2 C NO
3 B NOMaking a column invisible does not affect its mandatory/optional status.
SQL> CREATE TABLE tab2
2 (
3 id NUMBER NOT NULL,
4 description VARCHAR2(50) NOT NULL,
5 created_date DATE INVISIBLE NOT NULL
6 );
Table created.
SQL> COLUMN column_name FORMAT A20
SQL> SELECT column_id,
2 column_name,
3 nullable,
4 hidden_column
5 FROM user_tab_cols
6 WHERE table_name = 'TAB2'
7 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 tab2 VALUES (1, 'ONE');
INSERT INTO tab2 VALUES (1, 'ONE')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("COURSE"."TAB2"."CREATED_DATE")
SQL> INSERT INTO tab2 VALUES (3, 'THREE',SYSDATE);
INSERT INTO tab2 VALUES (3, 'THREE',SYSDATE)
*
ERROR at line 1:
ORA-00913: too many values
SQL> INSERT INTO tab2(id,description,created_date) VALUES (1,'ONE',SYSDATE);
1 row created.
SQL> ALTER TABLE tab2 MODIFY created_date NULL;
Table altered.
SQL> INSERT INTO tab2 VALUES (2, 'TWO');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT id,description,created_date FROM tab2;
ID DESCRIPTION CREATED_D
---------- -------------------------------------------------- ---------
1 ONE 29-SEP-20
2 TWO
SQL> CREATE TABLE tab1
2 (
3 id NUMBER,
4 description VARCHAR2(50) INVISIBLE
5 );
Table created.
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> INSERT INTO tab1 VALUES (1,'TEST-1');
INSERT INTO tab1 VALUES (1,'TEST-1')
*
ERROR at line 1:
ORA-00913: too many values
SQL> INSERT INTO tab1 VALUES (2);
1 row created.
SQL> INSERT INTO tab1(id,description) VALUES (3,'TEST-2');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM tab1;
ID
----------
2
3
SQL> SELECT id,description FROM tab1;
ID DESCRIPTION
---------- --------------
2
3 TEST-2
SQL> DECLARE
2 CURSOR tab1_cur IS
3 SELECT * FROM tab1;
4
5 l_rec1 tab1_cur%ROWTYPE;
6 BEGIN
7 OPEN tab1_cur;
8
9 FETCH tab1_cur INTO l_rec1;
10
11 CLOSE tab1_cur;
12
13 DBMS_OUTPUT.PUT_LINE ('ID ::: '||l_rec1.id);
14 DBMS_OUTPUT.PUT_LINE ('DESCRIPTION ::: '||l_rec1.description);
15 END;
16 /
--OUTPUT :::
DBMS_OUTPUT.PUT_LINE ('DESCRIPTION ::: '||l_rec1.description);
*
ERROR at line 14:
ORA-06550: line 14, column 53:
PLS-00302: component 'DESCRIPTION' must be declared
ORA-06550: line 14, column 4:
PL/SQL: Statement ignored
SQL> DECLARE
2 CURSOR tab1_cur IS
3 SELECT id FROM tab1;
4
5 l_rec1 tab1_cur%ROWTYPE;
6 BEGIN
7 OPEN tab1_cur;
8 LOOP
9 FETCH tab1_cur INTO l_rec1;
10 EXIT WHEN tab1_cur%NOTFOUND;
11 DBMS_OUTPUT.PUT_LINE ('ID ::: '||l_rec1.id);
12 END LOOP;
13 CLOSE tab1_cur;
14 END;
15 /
--OUTPUT :::
ID ::: 2
ID ::: 3
SQL> DECLARE
2 l_rec1 tab1%ROWTYPE;
3 BEGIN
4 SELECT *
5 INTO l_rec1
6 FROM tab1;
7
8 DBMS_OUTPUT.PUT_LINE ('ID ::: '||l_rec1.id);
9 DBMS_OUTPUT.PUT_LINE ('DESCRIPTION ::: '||l_rec1.description);
10 END;
11 /
--OUTPUT :::
DBMS_OUTPUT.PUT_LINE ('DESCRIPTION ::: '||l_rec1.description);
*
ERROR at line 9:
ORA-06550: line 9, column 53:
PLS-00302: component 'L_REC1.DESCRIPTION' must be declared
ORA-06550: line 9, column 4:
PL/SQL: Statement ignored
SQL> DECLARE
2 CURSOR tab1_cur IS
3 SELECT id,description FROM tab1;
4
5 l_rec1 tab1_cur%ROWTYPE;
6 BEGIN
7 OPEN tab1_cur;
8 LOOP
9 FETCH tab1_cur INTO l_rec1;
10 EXIT WHEN tab1_cur%NOTFOUND;
11 DBMS_OUTPUT.PUT_LINE ('ID ::: '||l_rec1.id);
12 DBMS_OUTPUT.PUT_LINE ('DESCRIPTION ::: '||l_rec1.description);
13 END LOOP;
14 CLOSE tab1_cur;
15 END;
16 /
OUTPUT :::
ID ::: 2
DESCRIPTION :::
ID ::: 3
DESCRIPTION ::: TEST-2You can make an invisible column visible with an ALTER TABLE statement, as in :-
ALTER TABLE tab1 MODIFY description VISIBLE;
You can make an visible column invisible with an ALTER TABLE statement, as in :-
ALTER TABLE tab1 MODIFY id INVISIBLE;
You can use records that are based on %ROWTYPE declarations against the table to which the insert is made, or on an explicit record TYPE that is compatible with the structure of the table.
You can also use a record based on a programmer-defined record TYPE to perform the INSERT, but that record type must be 100% compatible with the table %ROWTYPE definition. You may not, in other words, INSERT using a record that covers only a subset of the table’s columns.
