Annotations in Oracle 23ai

Oracle Database23ai introduced new feature annotations, using this feature user can add additional comments to objects and allow storing database object metadata. It helps to share the metadata across applications.

You can add annotations to any supported schema objects with CREATE privilege and you can add or drop annotations using ALTER privilege, the object can have more than one annotation as well. The supported schema objects include tables, indexes, views, materialized views. An annotation will consist of a name and an optional value, which can be entered as freeform text fields.

You can drop the annotations, or it will be dropped when a schema object is dropped. You can query USER|ALL|DBA_ANNOTATIONS_USAGE to view annotations for an object.

Annotation has three clauses i.e ADD, DROP or REPLACE

ADD – create annotations for an existing object.
DROP – removes the annotation from an object.
REPLACE – change annotation_value. This clause used with ALTER statements.

In below example, we’re adding annotation both table and columns

CREATE TABLE emp (
emp_id number ANNOTATIONS (ColumnInfo ‘Employee ID’),
emp_name varchar2(30) ANNOTATIONS (ColumInfo ‘Employee Name’),
dept varchar2(20) )
ANNOTATIONS ( TableInfo ‘Employee Table’);

SQL> SELECT ANNOTATION_NAME, ANNOTATION_VALUE from USER_ANNOTATIONS_USAGE WHERE Object_Name ='EMP' AND Object_Type = 'TABLE' AND Column_Name IS NULL;

ANNOTATION_NAME ANNOTATION_VALUE
-------------------- --------------------
TABLEINFO Employee Table

You can drop annotation from an existing table using below example
SQL> ALTER TABLE emp ANNOTATIONS (DROP TableInfo);

You can add annotation to the table using below example, note that ADD keyword is optional.
SQL> ALTER TABLE emp ANNOTATIONS (ADD TableInfo ‘Employee Table’);

SQL> SELECT ANNOTATION_NAME, ANNOTATION_VALUE from USER_ANNOTATIONS_USAGE WHERE Object_Name ='EMP' AND Object_Type = 'TABLE' AND Column_Name IS NOT NULL;

ANNOTATION_NAME ANNOTATION_VALUE
-------------------- --------------------
EMP_ID Employee ID
EMP_NAME Employee Name

In below example you can add, replace and drop annotations at column level. Note that ADD keyword is optional.

SQL> ALTER TABLE EMP modify dept ANNOTATIONS (ADD ColumnInfo ‘Dept Name’);
SQL> ALTER TABLE EMP modify dept ANNOTATIONS (REPLACE ColumnInfo ‘Deptartment Name’);
SQL> ALTER TABLE EMP modify dept ANNOTATIONS (DROP ColumnInfo);

Thanks & Regards,

https://oracleracexpert.com