Oracle 23c - SQL

Introducing Annotations: A New Frontier for Metadata

/ Oracle DB 23c New Features, SQL 23c / By

Oracle Database 23c introduces a powerful new feature that promises to transform metadata management: Annotations. These flexible metadata tags enable you to attach descriptive information directly to database objects, unlocking a wealth of possibilities for customization, collaboration, and optimization.

Key Attributes of Annotations:

  • Ubiquitous Applicability: Annotations can be applied to a broad range of database objects, encompassing tables, columns, views, procedures, functions, packages, and more.
  • Versatile Structure: Annotations can take the form of simple name-value pairs or standalone names, offering flexibility in capturing diverse metadata.
  • Seamless Integration: Stored centrally within the database, annotations ensure accessibility and consistency across applications and tools.

Common Use Cases:

  • Customizing Business Logic: Tailor application behavior based on specific annotations, such as concealing sensitive columns or applying conditional logic.
  • Enhancing User Interfaces: Present user-friendly display names and descriptions derived from annotations, fostering a more intuitive user experience.
  • Integrating with Metadata Repositories: Share rich metadata with external tools and platforms, promoting seamless collaboration and integration.

Code Examples:

If an object supports annotations, the annotations clause typically follows this format.

ANNOTATIONS ( {ADD|DROP} annotation_name {'annotation_value'} {,} )

Note that the annotation value is optional. Let’s explore some code examples.

Creating a Table with Annotations:

Consider the following example of creating annotations at the table and column levels:

CREATE TABLE employees (
  employee_id NUMBER(10)  ANNOTATIONS (SurrogateKey, display 'Employee ID', Classification 'INTERNAL'),
  first_name VARCHAR2(50) ANNOTATIONS (display 'First Name', Classification 'CONFIDENTIAL'),
  last_name VARCHAR2(50)  ANNOTATIONS (display 'Last Name' , Classification 'CONFIDENTIAL'),
  salary NUMBER           ANNOTATIONS (display 'Monthly Salary' , Classification 'CONFIDENTIAL')
) ANNOTATIONS (display 'Employee Information');

The ‘SurrogateKey‘ annotation signifies that this column is a surrogate key without providing a specific value, serving as a flag.

The ‘display‘ annotation assists the application in determining the content to showcase in UI labels when presenting column values.

The ‘Classification‘ annotation establishes the security classification for data within the column. This information can be utilized by the application to prevent unauthorized personnel from accessing data with a classification higher than their permissible data classification.

Adding and Dropping Annotations

To add or drop annotation on table or column levels:

ALTER TABLE employees ANNOTATIONS (ADD system 'HR');
ALTER TABLE employees ANNOTATIONS (DROP system);
ALTER TABLE employees MODIFY ( employee_id ANNOTATIONS ( DM_Code 'EM019'));
ALTER TABLE employees MODIFY ( employee_id ANNOTATIONS ( DROP DM_Code ));

You cannot change an annotation value. You can drop it and create it again with the new required value.

Retrieving Annotations

Utilize the USER_ANNOTATIONS_USAGE view to obtain information about annotations:

SET LINESIZE 150
COLUMN object_name FORMAT A12
COLUMN object_type FORMAT A12
COLUMN column_name FORMAT A12
COLUMN annotation_name FORMAT A14
COLUMN annotation_value FORMAT A20

SELECT object_name,
       object_type,
       column_name,
       annotation_name,
       annotation_value
FROM   user_annotations_usage
ORDER BY annotation_name, annotation_value;

OBJECT_NAME    OBJECT_TYPE    COLUMN_NAME    ANNOTATION_NAME    ANNOTATION_VALUE
______________ ______________ ______________ __________________ _______________________
EMPLOYEES      TABLE          FIRST_NAME     CLASSIFICATION     CONFIDENTIAL
EMPLOYEES      TABLE          SALARY         CLASSIFICATION     CONFIDENTIAL
EMPLOYEES      TABLE          LAST_NAME      CLASSIFICATION     CONFIDENTIAL
EMPLOYEES      TABLE          EMPLOYEE_ID    CLASSIFICATION     INTERNAL
EMPLOYEES      TABLE          EMPLOYEE_ID    DISPLAY            Employee ID
EMPLOYEES      TABLE                         DISPLAY            Employee Information
EMPLOYEES      TABLE          FIRST_NAME     DISPLAY            First Name
EMPLOYEES      TABLE          LAST_NAME      DISPLAY            Last Name
EMPLOYEES      TABLE          SALARY         DISPLAY            Monthly Salary
EMPLOYEES      TABLE          EMPLOYEE_ID    DM_CODE            EM019
EMPLOYEES      TABLE          EMPLOYEE_ID    SURROGATEKEY

11 rows selected.

Additional Considerations

  • Annotations vs. Comments: Annotations offer structured metadata distinct from traditional comments, with potential for programmatic interpretation.
  • Security: Implement appropriate access controls to safeguard sensitive annotations.
  • Governance: Establish clear guidelines for annotation usage to ensure consistency and maintainability.

Conclusion

Annotations in Oracle Database 23c mark a significant leap forward in metadata management. By harnessing the power of annotations, organizations can build more adaptive, user-centric, and integrated database solutions, perfectly poised to meet the evolving demands of modern data ecosystems.

Scroll to Top