Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 26 January 2014

Invisible Column feature in Oracle 12c

Oracle 12c allows a coulnm to be invisible. Invisible coulnm are considered only when they are explicitly referred, otherwise they are ignored for queries and DML operations. Sometimes it requires to hide particualr column from developers thus they can not include these data in reports.
You can make a column visible or invisible whenever required using ALTER TABLE command:

CREATE TABLE INV_EMP (EMP# NUMBER(4), SALARY NUMBER(8,2) INVISIBLE)
INSERT INTO INV_EMP (EMP#,SALARY) VALUES (101, 6000);
INSERT INTO INV_EMP VALUES(102);
SQL> select * from INV_EMP;
      EMP#
-----------
      101
      102
SQL> select EMP#,SALARY from INV_EMP;
      EMP#       SALARY
----------       -------------
      101        6000
      102
SQL> ALTER TABLE INV_EMP MODIFY (SALARY  VISIBLE);
SQL> select * from INV_EMP;
      EMP#      SALARY
----------      -------------
      101       6000
      102
You can do same for view as you are doing for table column:
SQL> create or replace view INV_EMP_VIEW (EMP#, SALARY INVISIBLE) as select EMP#, SALARY from INV_EMP;

You can not make a table or its whole column invisible. For Example the above table having two column one hidden one visible.
If you try to invisible both then you will get the error:

SQL> alter table INV_EMP (salary invisible);
alter table inv_emp (salary invisible)
            *
ERROR at line 1:
ORA-54039: table must have at least one column that is not invisible.

Note: *. INVISIBLE columns are not supported in external tables, cluster tables, or temporary tables.
          *. You cannot make a system-generated hidden column visible.

0 comments:

Post a Comment