我有一個帶有不可見列的表格，上面附有評論。它不可見的事實意味著all_col_comments視圖似乎不承認它的存在 - 盡管我可以很高興地查詢同一個表中其他列注釋的注釋。顯式參考列或（理想情況下）不參考列，是否有任何查詢可以檢索評論？
（這是 Oracle 12c1）
uj5u.com熱心網友回復：
顯然從 19c 開始，問題就解決了。隱藏列上的評論確實出現在all_col_comments. HIDDEN視圖中的屬性all_tab_cols將列顯示為不可見。
較舊的版本存在您所顯示的問題，我認為是錯誤或新功能。
在 19c 中作業沒有任何問題。
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)
但是在 12.2 和 18c 中沒有
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/346913.html
標籤：甲骨文