Oracle Database

ORACLE-WIKI.NET

Introduction

In previous releases of Oracle we have had comments. These were free form text that we could add to an object, for example a view or a table column, that allowed us to describe the table or associate useful information for DBAs or Developers. In Oracle 23c, we now have the natural extension of this and they are called annotations.

An annotation is a key-pair value that can be associated with a column, table or view that can be useful for application development. For example, In an heterogeneous reporting system you you may want to annotate a table with reporting component it relates to. For example, "Application 'Accounts'"

Annotations are supported on the following object types

• Tables and table columns
• Views and view columns
• Materialized views and materialized view columns
• Indexes
• Domains and multi-column domain columns

In order to create or drop an annotation you need create or alter on the object. Notice you do not need drop privileges. This is because an annotation is not an object in its own right. Instead it is what the manual calls a "subordinate element" of the object. Also note, that an annotation are created on first use, not at the point they are defined.

How to Annotate your Objects

Below is an example of a view annotation. In this example, we have a view that shows bedroom furniture. The annotations are used to provide Display names on a website and also to indicate the part number range for bedroom furniture.

CREATE OR REPLACE VIEW BedroomFurniture
(
  Part_Number ANNOTATIONS(Identity, Display 'Product Code', Range 'Bedroom Furniture Partno starts with D1'),
  Product_Name ANNOTATIONS (Display 'Product name', Category 'Bedroom'),
  Brand_Name  ANNOTATIONS (Display 'Brand')  
)
ANNOTATIONS (Title 'Bedroom Furniture')
AS SELECT * FROM products WHERE product  like 'D1%' order by brand_name;

Dictionary Views Related to Annotations

{DBA|USER|ALL|CDB}_ANNOTATIONS
{DBA|USER|ALL|CDB}_ANNOTATIONS_USAGE
{DBA|USER|ALL|CDB}_ANNOTATIONS_VALUES

The following query is very useful for obtaining column-level annotations a single JSON collection per column:

SELECT  U.Column_Name, JSON_ARRAYAGG(JSON_OBJECT(U.Annotation_Name, U.Annotation_Value))
  FROM USER_ANNOTATIONS_USAGE U
  WHERE Object_Name = 'products' AND Object_Type = 'TABLE' AND Column_Name IS NOT NULL
  GROUP BY Column_Name;

Further information can be found here - Annotations

Published 12th October 2023