How to Retrieve Comments from an Invisible Column in Oracle Databases

Discover how to access comments from an invisible column in Oracle databases, including solutions for versions 12c and 19c.
---
This video is based on the question https://stackoverflow.com/q/69808947/ asked by the user 'Julia Hayward' ( https://stackoverflow.com/u/512461/ ) and on the answer https://stackoverflow.com/a/69809798/ provided by the user 'Roberto Hernandez' ( https://stackoverflow.com/u/13755538/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Oracle: Query for comments on an invisible column

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/licensing
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Retrieving Comments from an Invisible Column in Oracle Databases

In Oracle databases, hidden or invisible columns serve a purpose, allowing developers to modify table structures without impacting existing applications. However, this convenience sometimes creates a challenge, especially when it comes to retrieving metadata, such as comments associated with such columns. If you're using Oracle 12c and have encountered the issue where comments on invisible columns do not appear in standard views, you're not alone. In this post, we will explore the solution in detail, as well as how this behavior differs in newer versions like Oracle 19c.

The Problem: Comments on Invisible Columns

When you create a table with an invisible column, any comments you attach to that column may not show up in views like all_col_comments. This can hinder your ability to document and understand your database schema effectively, especially if you're working with multiple versions of Oracle.

Invisible Column: A column that is not visible to standard queries and applications.

all_col_comments View: A view typically used for retrieving descriptions or comments associated with other columns in your tables.

In short, while you can comment on invisible columns, they might not be accessible through usual querying methods in certain Oracle versions.

The Solution in Oracle 19c

Starting with Oracle version 19c, this limitation was addressed. If you're on this version or later, retrieving comments from invisible columns is straightforward and works as follows:

Create a Table with an Invisible Column:

[[See Video to Reveal this Text or Code Snippet]]

Add Comments to Your Columns:

[[See Video to Reveal this Text or Code Snippet]]

Query for Comments Including Invisible Columns:

[[See Video to Reveal this Text or Code Snippet]]

This will now return the expected results, showing comments for all columns, including the invisible one.

What About Older Versions?

If you are still using Oracle 12c (like version 12.2) or 18c, you might find that this functionality is not available. Queries to all_col_comments will exclude comments for invisible columns, revealing only comments on visible columns. Here are key points when working in these versions:

Invisible column comments will not show up.

This inconsistency may stem from either a bug or design choice in those versions.

Example of Limitation in 12c/18c:

When you try to execute the same query for comments on invisible columns in Oracle 12.2 or 18c, the result will exclude comments for the invisible column c3. Here's what happens:

[[See Video to Reveal this Text or Code Snippet]]

This will result in only visible columns (c1, c2) being returned, with no mention of c3.

Conclusion

If you’re working with invisible columns in older versions of Oracle, understanding the limitations is critical to managing your database schema effectively. For those using Oracle 19c or later, retrieving comments from invisible columns has become much easier thanks to the enhancements in the database’s architecture. Always keep your database software updated to leverage new features and improvements!
If you have additional questions or require further clarification on this topic, feel free to reach out in the comments section below!

Видео How to Retrieve Comments from an Invisible Column in Oracle Databases канала vlogize