Oracle: Query for comments on an invisible column-CodePudding

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)

CodePudding user response：

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