You can now make columns within a table invisible and general access will only display visible tables. The following queries will not display invisible columns:
- Select * from
- Describe command
- %ROWTYPE declaration in PL/SQL
- Describe on OCI
Invisible columns will be displayed if they are explicitly called by column name and you can insert data by explicitly naming the column, but if insert does not list column names you cannot insert into invisible columns. Columns can be made invisible during table creation, adding columns, or modifying them. Virtual columns can be invisible also. You can use an invisible column as a partition key.
Invisible columns have the following restrictions.
- External, cluster, and temporary tables cannot have invisible columns.
- Attributes of user-defined types cannot be invisible.
Example of Column invisibility using SCOTT.EMP table.
- Logon to SQLPLUS as the user SCOTT
[oracle@linux2 ~]$ sqlplus scott/password
SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 29 14:14:06 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Thu Dec 29 2016 13:47:09 -05:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
SQL>
- select table_name, column_name, hidden_column from USER_TAB_COLS.
SQL> column table_name format a10
SQL> column column_name format a10
SQL> select table_name, column_name, hidden_column from user_tab_cols where table_name=’EMP’;
TABLE_NAME COLUMN_NAM HID
———- ———- —
EMP EMPNO NO
EMP ENAME NO
EMP JOB NO
EMP MGR NO
EMP HIREDATE NO
EMP SAL NO
EMP COMM NO
EMP DEPTNO NO
8 rows selected.
SQL>
NOTE: None of the columns are hidden.
- Now select the first three rows from table SCOTT.EMP.
SQL> set linesize 200
SQL> select * from scott.emp where rownum<4;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
SQL>
- Now alter the table SCOTT.EMP and make column HIRDATE, SAL, COMM invisible.
alter table scott.emp modify hiredate invisible;
alter table scott.emp modify sal invisible;
alter table scott.emp modify comm invisible;
SQL> alter table scott.emp modify hiredate invisible;
alter table scott.emp modify sal invisible;
alter table scott.emp modify comm invisible;
Table altered.
SQL>
Table altered.
SQL>
Table altered.
SQL>
- Select the first 3 rows of table SCOTT.EMP.
SQL> select * from scott.emp where rownum<4;
EMPNO ENAME JOB MGR DEPTNO
———- ———- ——— ———- ———-
7369 SMITH CLERK 7902 20
7499 ALLEN SALESMAN 7698 30
7521 WARD SALESMAN 7698 30
SQL>
NOTE: You no longer see the columns HIREDATE, SAL, or COMM
- Now desc the table SCOTT.EMP.
SQL> desc scott.emp;
Name Null? Type
———— ——– ——————————————-
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
DEPTNO NUMBER(2)
SQL>
NOTE: You no longer see the columns HIREDATE, SAL, or COMM
- Now select the first 3 values but explicitly request the columns, NOTE they are still there but need to be explicitly queried.
SQL> select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO from scott.emp where rownum<4;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
SQL>
- Additionally the hidden_columns value in user_tab_cols will also be updated accordingly.
SQL> column table_name format a10
column column_name format a10
select table_name, column_name, hidden_column from user_tab_cols where table_name=’EMP’;
SQL> SQL>
TABLE_NAME COLUMN_NAM HID
———- ———- —
EMP EMPNO NO
EMP ENAME NO
EMP JOB NO
EMP MGR NO
EMP HIREDATE YES
EMP SAL YES
EMP COMM YES
EMP DEPTNO NO
8 rows selected.
SQL>
- This completes using invisible columns in Oracle 12c.
Larry Catt
OCP