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!
Learning is not a spectator sport 
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?
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’ 🙂
Hi Connor,
Any idea if the bug is fixed ? Any bug # using which we can track the bug ?
Thanks,
A