Creating and Managing Schema Annotations

Schema annotations are free-form text fields that contain extended or custom properties of database objects such as tables, views, columns, indexes, and data use case domains. Annotations are a lightweight declarative facility to centrally register usage properties for database schema objects. They can be thought of as lightweight standardized markup for database metadata, for use by applications to register and process extended and custom usage properties.

See Also: Oracle AI Database Concepts for additional general information about annotations

Creating Annotations

Annotations are properties of schema objects, and can be specified in the CREATE statement for the object.

The following statement creates a new table with annotations for the table itself and the underlying columns.

CREATE TABLE employees
(
    id NUMBER(5) PRIMARY KEY ANNOTATIONS (Identity, Display 'Employee Name'),

    ename VARCHAR2(50) ANNOTATIONS(Display 'Employee Name'),

    salary NUMBER ANNOTATION(Display 'Employee Name', Confidential)
) ANNOTATIONS (Display 'Employee Table');

The following statement includes an annotation in the creation of the dept_codes application usage domain in the hr schema.

CREATE DOMAIN dept_codes AS NUMBER(3)
  CONSTRAINT dept_chk CHECK (dept_codes > 99 AND dept_codes != 200)
  ANNOTATIONS (Title 'Domain Annotation);

See Also: Oracle AI Database Development Guide for information about DDL statements for annotations

Listing Annotations

You can use dictionary views to get the list of annotations that are used for specific objects.

The following dictionary views are defined for annotations and annotation usage.

ALL_ANNOTATION_VALUES, DBA_ANNOTATION_VALUES, USER_ANNOTATION_VALUES
ALL_ANNOTATIONS, DBA_ANNOTATIONS, USER_ANNOTATIONS
ALL_ANNOTATIONS_USAGE, DBA_ANNOTATIONS_USAGE, USER_ANNOTATIONS_USAGE

The ALL_* views include all annotations for objects owned by the user, all annotations for objects owned by other users where the user has the ALTER privilege, and all annotations for objects where the user has system privileges for that object type. The DBA_* views include all annotations for all objects in the database. The USER_* views include all annotations for objects owned by the user.

The following statement gets the table-level annotations for the EMPLOYEE table:

SELECT * from USER_ANNOTATIONS_USAGE WHERE Object_Name = 'EMPLOYEE' AND
Object_Type = 'TABLE' AND Column_Name IS NULL;

The following statement gets the column-level annotations for the EMPLOYEE table:

SELECT * from USER_ANNOTATIONS_USAGE WHERE Object_Name = 'EMPLOYEE' AND
Object_Type = 'TABLE' AND Column_Name IS NOT NULL;

The following statement gets the column-level annotations for the EMPLOYEE table as 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 = 'EMPLOYEE' AND Object_Type = 'TABLE' AND
Column_Name IS NOT NULL GROUP BY Column_Name;

See Also: Oracle AI Database Reference for information about the dictionary views for annotations.

Modifying Annotations

To modify an annotation, use the ALTER statement for the annotated schema object.

The following statement adds an additional annotation Department with the value HR to the employees table.
```
ALTER TABLE employees ANNOTATIONS (ADD Department 'HR');
```
The following statement drops the annotation Title and adds a new annotation Name with the value Domain to the dept_code domain.
```
ALTER DOMAIN dept_codes ANNOTATIONS(DROP Title, ADD Name 'Domain');
```

See Also: Oracle AI Database Development Guide for information about DDL statements for annotations