I have a table with an invisible column, that has a comment attached. The fact it's invisible means the all_col_comments view appears not to acknowledge its existence - although I can quite happily query comments on other column comments in the same table. Either referring to the column explicitly or (ideally) not, is there any query that will retrieve the comment?
(This is Oracle 12c1)
Apparently starting with 19c, the issue is solved. The comments on the hidden column do appear in all_col_comments. The attribute HIDDEN in the view all_tab_cols show the column as invisible.
Versions older have the issue you have shown, and I believe is either a bug or a new feature.
In 19c works without any problem.
SQL> alter session set current_schema=test ;
Session altered.
SQL> create table t1 ( c1 number generated always as identity start with 1 increment by 1 ,
c2 varchar2(10) ,
c3 varchar2(10) invisible
) ; 2 3 4
Table created.
SQL> comment on column t1.c1 is 'The id' ;
comment on column t1.c2 is 'The string' ;
comment on column t1.c3 is 'The invisible item' ;
Comment created.
SQL>
Comment created.
SQL>
Comment created.
SQL> select a.table_name , a.column_name, a.comments, b.hidden_column from all_col_comments a inner join all_tab_cols b
2 on a.owner = b.owner and a.table_name=b.table_name and a.column_name = b.column_name
3* where a.owner = 'TEST' and a.table_name = 'T1'
/
TABLE_NAME COLUMN_NAME COMMENTS HID
------------------------------ ------------------------------ -------------------------------------------------- ---
T1 C1 The Id NO
T1 C2 The string NO
T1 C3 The invisible item YES
SQL> set lines 80
SQL> desc t1
Name Null? Type
----------------------------------------- -------- ----------------------------
C1 NOT NULL NUMBER
C2 VARCHAR2(10)
However in 12.2 and 18c does not
sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue Nov 2 12:01:17 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select banner from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE 12.2.0.1.0 Production
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production
SQL> create user test identified by "Test_123" default tablespace users ;
User created.
SQL> grant create table to test ;
Grant succeeded.
SQL> alter session set current_schema = test ;
Session altered.
create table t1 ( c1 number generated always as identity start with 1 increment by 1 ,
c2 varchar2(10) ,
c3 varchar2(10) invisible
4 ) ;
Table created.
SQL> comment on column t1.c1 is 'The id' ;
Comment created.
SQL> comment on column t1.c2 is 'The string' ;
Comment created.
SQL> comment on column t1.c3 is 'The invisible item' ;
Comment created.
SQL> select a.table_name , a.column_name, a.comments, b.hidden_column from all_col_comments a inner join all_tab_cols b
2 on a.owner = b.owner and a.table_name=b.table_name and a.column_name = b.column_name
3* where a.owner = 'TEST' and a.table_name = 'T1'
SQL> /
TABLE_NAME
--------------------------------------------------------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
COMMENTS
--------------------------------------------------------------------------------
HID
---
T1
C1
The id
NO
TABLE_NAME
--------------------------------------------------------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
COMMENTS
--------------------------------------------------------------------------------
HID
---
T1
C2
The string
NO