Oracle 12C introduced invisible columns. SQL Language Reference manual (in CREATE TABLE section) states:
- The
COLUMN_ID column of the ALL_, DBA_, and USER_TAB_COLUMNS data dictionary views determines the order in which a SELECT * query returns columns for a table, view, or materialized view. The value of COLUMN_ID is NULL for INVISIBLE columns. When you make an invisible column visible, it will be assigned the next highest available COLUMN_ID value. When you make a visible column invisible, its COLUMN_ID value is set to NULL and COLUMN_ID is decremented by 1 for any columns with a higher COLUMN_ID.
And indeed:
SQL> create table tbl(
2 ename varchar2(10),
3 sal number,
4 comm number,
5 job varchar2(20),
6 hiredate date
7 )
8 /
Table created.
SQL> select column_name,
2 virtual_column,
3 hidden_column,
4 column_id,
5 internal_column_id,
6 user_generated
7 from user_tab_cols
8 where table_name = 'TBL'
9 order by internal_column_id
10 /
COLUMN_NAME VIR HID COLUMN_ID INTERNAL_COLUMN_ID USE
------------------------------ --- --- ---------- ------------------ ---
ENAME NO NO 1 1 YES
SAL NO NO 2 2 YES
COMM NO NO 3 3 YES
JOB NO NO 4 4 YES
HIREDATE NO NO 5 5 YES
SQL> alter table tbl
2 modify job invisible
3 /
Table altered.
SQL> select column_name,
2 virtual_column,
3 hidden_column,
4 column_id,
5 internal_column_id,
6 user_generated
7 from user_tab_cols
8 where table_name = 'TBL'
9 order by internal_column_id
10 /
COLUMN_NAME VIR HID COLUMN_ID INTERNAL_COLUMN_ID USE
------------------------------ --- --- ---------- ------------------ ---
ENAME NO NO 1 1 YES
SAL NO NO 2 2 YES
COMM NO NO 3 3 YES
JOB NO YES 4 YES
HIREDATE NO NO 4 5 YES
SQL>
Column HIREDATE id changed from 5 to 4. And, as it was stated in docs:
SQL> alter table tbl
2 modify job visible
3 /
Table altered.
SQL> select column_name,
2 virtual_column,
3 hidden_column,
4 column_id,
5 internal_column_id,
6 user_generated
7 from user_tab_cols
8 where table_name = 'TBL'
9 order by internal_column_id
10 /
COLUMN_NAME VIR HID COLUMN_ID INTERNAL_COLUMN_ID USE
------------------------------ --- --- ---------- ------------------ ---
ENAME NO NO 1 1 YES
SAL NO NO 2 2 YES
COMM NO NO 3 3 YES
JOB NO NO 5 4 YES
HIREDATE NO NO 4 5 YES
SQL>
Column job id is now 5. So all out existing scripts start failing:
SQL> insert
2 into tbl
3 values('Sam',5000,1000,'ANALYST',date '2010-11-15')
4 /
values('Sam',5000,1000,'ANALYST',date '2010-11-15')
*
ERROR at line 3:
ORA-01858: a non-numeric character was found where a numeric was expected
This behavior doesn't make much sense. I wonder who's brilliant idea was to implement it this way. I have no clue why making column visible couldn't be trigger column id renumbering based on internal column id order.
SY.
Message was edited by: SolomonYakobson