12c invisible columns

Posted by

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)
 

Update Aug 2021

If you are using the Virtual Private Database feature, then you probably do not want to be using the invisible/visible trick to re-order the columns in your database, because you might get some nasty surprises. Consider the following example where I have a DML row level security policy on the table


SQL> create table scott.t1 ( x varchar2(10), y date, z int);

Table created.

SQL> create or replace package scott.secpkg is
  2    function vpd_dml_policy (
  3        object_schema in varchar2,
  4        object_name   in varchar2
  5    ) return varchar2;
  6  end;
  7  /

Package created.

SQL> create or replace package body scott.secpkg is
  2
  3  function vpd_dml_policy (
  4      object_schema in varchar2,
  5      object_name   in varchar2
  6  ) return varchar2 is
  7  begin
  8    return '1=1';
  9  end vpd_dml_policy;
 10
 11  end;
 12  /

Package body created.

SQL> begin
  2              sys.dbms_rls.add_policy (
  3                  object_schema   => 'SCOTT',
  4                  object_name     => 'T1',
  5                  statement_types => 'insert,update,delete',
  6                  policy_name     => 'DML_POLICY',
  7                  policy_function => 'secpkg.vpd_dml_policy',
  8                  update_check    => true,
  9                  policy_type     => sys.dbms_rls.dynamic
 10              );
 11  end;
 12  /

PL/SQL procedure successfully completed.


A standard INSERT works fine (because my simple policy of ‘1=1’ allows all activity)


--
-- works fine
--
SQL> desc t1
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 X                                      VARCHAR2(10)
 Y                                      DATE
 Z                                      NUMBER(38)

SQL> insert into t1 values ('x',sysdate,10);
                           *
1 row created.

Now I re-order my columns in the table


SQL> alter table t1 modify y invisible;

Table altered.

SQL> alter table t1 modify y visible;

Table altered.

SQL> desc t1
 Name                                  Null?    Type
 ------------------------------------- -------- ---------------------
 X                                              VARCHAR2(10)
 Z                                              NUMBER(38)
 Y                                              DATE

And now run my INSERT statement (adjusted to take into account the altered column order)


SQL> insert into t1 values ('x',10,sysdate);
insert into t1 values ('x',10,sysdate)
                           *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

We all know that not specifying the columns in the INSERT is generally bad practice, but this also impacts things like PL/SQL row variable inserts:


SQL> declare
  2    l_row t1%rowtype;
  3  begin
  4    l_row.x := 'x';
  5    l_row.y := sysdate;
  6    l_row.z := 10;
  7    insert into t1 values l_row;
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER
ORA-06512: at line 7

Throwing a 10730 trace on the activity reveals possibly what is happening here


KZRT_RLS_TRC: User SQL : INSERT INTO T1 VALUES (:B1 ,:B2 ,:B3 ) 
KZRT_RLS_TRC: SQL#=0000000004E0CB63, XSC=0000011F2D14B6E0, CHD=00007FFBE58A7768
KZRT_RLS_TRC: Logon user     : SCOTT
KZRT_RLS_TRC: Table/View     : SCOTT.T1
KZRT_RLS_TRC: VPD Policy name    : DML_POLICY
KZRT_RLS_TRC: Policy function: SCOTT.SECPKG.VPD_DML_POLICY
KZRT_RLS_TRC: RLS view :
SELECT  "X","Y","Z" FROM "SCOTT"."T1"   "T1" WHERE (1=1)  WITH CHECK OPTION

I suspect our original INSERT statement which is presented as:


INSERT INTO T1 VALUES (:B1 ,:B2 ,:B3 ) 

is being re-mapped to ensure that the DML policy is valid as the following:


INSERT INTO 
  ( SELECT  "X","Y","Z" FROM "SCOTT"."T1"   "T1" WHERE (1=1)  WITH CHECK OPTION ) 
VALUES (:B1 ,:B2 ,:B3 )

which leads to the mismatch of columns and values. Having the code crash is probably a “best case” scenario, because if by chance the alter column datatypes end up still in alignment, you may get a silent data corruption


SQL> select * from t1;

         X          Y          Z
---------- ---------- ----------
         1         10        100

SQL> alter table t1 modify y invisible;

Table altered.

SQL> alter table t1 modify y visible;

Table altered.

SQL> select * from t1;

         X          Z          Y
---------- ---------- ----------
         1        100         10

SQL> insert into t1 values (1,10,100);

1 row created.

SQL> select * from t1;

         X          Z          Y
---------- ---------- ----------
         1        100         10
         1        100         10  <<< incorrect


A bug has been raised but until this is patched, please be careful of logically reordering your columns. It really should not be a thing you ever need to do anyway!

6 comments

  1. Can you please try fix_cols(‘T’,’C1,C2,C3′) and then fix_cols(‘T’,’C1,C3,C2′); ?
    I believe there’s a bug that will fail to reorder certain permutations.

    Otherwise, nice catch.

    Did you verify what happens with rows inside materialized view log?
    It used to have bit-coded column positions in change_vector$$ column. I wonder if just a single “invisible” breaks the data inside the log.

    Does that work with functional-based index generated columns?

    1. Seems to work as expected … did you have something else in mind ?

      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)

      SQL> exec fix_cols(‘T’,’C1,C3,C2′)

      PL/SQL procedure successfully completed.

      SQL> desc t
      Name Null? Type
      —————————————– ——– —————————-
      C1 NUMBER(38)
      C3 NUMBER(38)
      C2 NUMBER(38)

      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)

      Having said that, the procedure is hardly what you’d call robust 🙂

      In terms of change vectors, I would imagine* that the internal column id would be used (in the same way handling of an ‘unused’ column would be)

      SQL> select column_name, COLUMN_ID, INTERNAL_COLUMN_ID
      2 from user_tab_cols
      3 where table_name = ‘T’;

      COLUMN_NAME COLUMN_ID INTERNAL_COLUMN_ID
      —————————— ———- ——————
      C1 1 1
      C2 3 2
      C3 2 3

      * – of course, the term ‘imagine’ is not the same as ‘i am sure’ 🙂

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.