Annotations

-- example 01: table-level annotation

CREATE TABLE customers (
    customer_id     INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
    email_address   VARCHAR2(255 CHAR) NOT NULL,
    full_name       VARCHAR2(255 CHAR) NOT NULL
)
ANNOTATIONS (
    sensitivity 'high',
    departments 'sales, delivery',
    frontOffice
)
/

-- example 02: adding a column-level annotation

ALTER TABLE customers MODIFY (
    email_address ANNOTATIONS ( sensitivity 'highest' )
)
/

-- example 03: table and column level annotations

CREATE TABLE employees
(
    id     NUMBER(5)
           ANNOTATIONS (
               identity,
               display_as 'Employee ID',
               group_name 'Emp_Info'),
    name   VARCHAR2(50)
           ANNOTATIONS (
               display_as 'Employee Name',
               group_name 'Emp_Info'),
    salary NUMBER
           ANNOTATIONS (
               display_as 'Employee Salary', UI_hidden)
)
ANNOTATIONS (
    display_as 'Employee Table'
)
/

-- example 04: query the dictionary for annotation usage

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

SQL> -- example 01: table-level annotation
SQL> CREATE TABLE customers (
  2      customer_id     INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY,
  3      email_address   VARCHAR2(255 CHAR) NOT NULL,
  4      full_name       VARCHAR2(255 CHAR) NOT NULL
  5  )
  6  ANNOTATIONS (
  7      sensitivity 'high',
  8      departments 'sales, delivery',
  9      frontOffice
 10  )
 11  /

Table CUSTOMERS created.

SQL> -- example 02: adding a column-level annotation
SQL> ALTER TABLE customers MODIFY (
  2      email_address ANNOTATIONS ( sensitivity 'highest' )
  3  )
  4  /

Table CUSTOMERS altered.

SQL> -- example 03: table and column level annotations
SQL> CREATE TABLE employees
  2  (
  3      id     NUMBER(5)
  4             ANNOTATIONS (
  5                 identity,
  6                 display_as 'Employee ID',
  7                 group_name 'Emp_Info'),
  8      name   VARCHAR2(50)
  9             ANNOTATIONS (
 10                 display_as 'Employee Name',
 11                 group_name 'Emp_Info'),
 12      salary NUMBER
 13             ANNOTATIONS (
 14                 display_as 'Employee Salary', UI_hidden)
 15  )
 16  ANNOTATIONS (
 17      display_as 'Employee Table'
 18  )
 19  /

Table EMPLOYEES created.

SQL> -- example 04: query the dictionary for annotation usage
SQL> SELECT
  2      object_name,
  4      column_name,
  5      annotation_name,
  6      annotation_value
  7  FROM
  8      user_annotations_usage
  9  ORDER BY
 10      object_name,
 11      column_name
 12  /

OBJECT_NAME    COLUMN_NAME      ANNOTATION_NAME    ANNOTATION_VALUE
______________ ________________ __________________ ___________________
CUSTOMERS      EMAIL_ADDRESS    SENSITIVITY        highest
CUSTOMERS                       DEPARTMENTS        sales, delivery
CUSTOMERS                       FRONTOFFICE
CUSTOMERS                       SENSITIVITY        high
EMPLOYEES      ID               IDENTITY
EMPLOYEES      ID               GROUP_NAME         Emp_Info
EMPLOYEES      ID               DISPLAY_AS         Employee ID
EMPLOYEES      NAME             DISPLAY_AS         Employee Name
EMPLOYEES      NAME             GROUP_NAME         Emp_Info
EMPLOYEES      SALARY           DISPLAY_AS         Employee Salary
EMPLOYEES      SALARY           UI_HIDDEN
EMPLOYEES                       DISPLAY_AS         Employee Table

12 rows selected.