invisible

How can I see my invisible columns

Connor McDonald , ,

A cool new feature in 12c is the ability to make a column invisible.  The concept has existed since 8i to handle things like “set unused” and function based indexes, but now it is available to developers directly.


SQL> create table T ( c1 int, c2 int );

Table created.

SQL> desc T
 Name                          Null?    Type
 ----------------------------- -------- -----------------
 C1                                     NUMBER(38)
 C2                                     NUMBER(38)

SQL> alter table T modify c2 invisible;

Table altered.

SQL> desc T
 Name                          Null?    Type
 ----------------------------- -------- -----------------
 C1                                     NUMBER(38)

So at this point… how I can tell in SQL Plus that I even have an invisible column, without querying the data dictionary.

It’s easy, we have a new setting – COLINVISIBLE



SQL> set colinvisible on
SQL> desc T
 Name                          Null?    Type
 ----------------------------- -------- -----------------
 C1                                     NUMBER(38)
 C2 (INVISIBLE)                         NUMBER(38)

Of course, if you want to play a practical joke on your work colleagues, you could do this:


SQL> desc t
 Name                          Null?    Type
 ----------------------------- -------- -------------
 C1                                     NUMBER(38)
 C2 (INVISIBLE)                         DATE
 C2 (INVISIBLE)                         NUMBER(38)

Smile

12c invisible columns

Connor McDonald , ,

As several bloggers have already pointed out, column order changes when you start playing with invisible columns, for example


SQL> create table T ( c1 int, c2 int, c3 int );

Table created.

SQL> desc T
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 C1                                                 NUMBER(38)
 C2                                                 NUMBER(38)
 C3                                                 NUMBER(38)

SQL> alter table T modify c1 invisible;

Table altered.

SQL> alter table T modify c1 visible;

Table altered.

SQL> desc T
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 C2                                                 NUMBER(38)
 C3                                                 NUMBER(38)
 C1                                                 NUMBER(38)

So just for a laugh, here’s a little routine to put the column order back to what you want 🙂


SQL> create or replace
  2  procedure fix_cols(p_tname varchar2, p_col_list varchar2) is
  3    l_col_list varchar2(1000) := p_col_list||',';
  4    type clist is table of varchar2(30)
  5      index by pls_integer;
  6   c clist;
  7
  8   this_col varchar2(30);
  9   l_id int;
 10  begin
 11    while instr(l_col_list,',') > 1 loop
 12      c(c.count+1) := substr(l_col_list,1,instr(l_col_list,',')-1);
 13      l_col_list := substr(l_col_list,instr(l_col_list,',')+1);
 14      dbms_output.put_line(c(c.count));
 15    end loop;
 16
 17    for i in 1 .. c.count loop
 18      loop
 19         select column_name
 20         into   this_col
 21         from   user_tab_columns
 22         where  table_name = p_tname
 23         and    column_id = i;
 24
 25         exit when this_col = c(i);
 26
 27         execute immediate 'alter table '||p_tname||' modify '||this_col||' invisible';
 28         execute immediate 'alter table '||p_tname||' modify '||this_col||' visible';
 29      end loop;
 30    end loop;
 31  end;
 32  /

Procedure created.

SQL> exec fix_cols('T','C1,C2,C3');

PL/SQL procedure successfully completed.

SQL> desc T
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 NUMBER(38)
 C2                                                 NUMBER(38)
 C3                                                 NUMBER(38)