Techblogs

Annotations

Terug naar overzicht

24 januari 2024

In previous releases of Oracle Database, there was always the possibility of adding comments to tables and columns, among other elements. As a developer, I have always appreciated finding such comments from those who worked on the code before me. They provide valuable insights into the meaning of each table and column, especially in older versions where object names were limited to 30 bytes.

In version 12.2 and beyond, this limit has increased to 128 bytes. Occasionally, these comments were utilized to define how a column should be displayed, such as in an APEX application.

Oracle Database 23c introduces a more flexible way of commenting on tables, views, materialized views, columns, and indexes: Annotations. These annotations, which are name-value pairs, can be added directly during the creation of the object. Post-creation, you can drop, add, or replace annotations.

Annotations follow the normal naming rules for objects in Oracle Database, using only ASCII letters, numbers, and underscores. They must begin with a letter. Similar to object names, annotation names are case-insensitive, unless enclosed in double quotes (") for case-sensitive names, unsupported characters, or reserved keywords. The value must be a character string literal, always enclosed in single quotes (').
Annotation names have a maximum length of 1024 characters, and annotation values can have a maximum length of 4000 characters. As annotations are stored in separate tables, there is no limit to the number of annotations.

Let’s create a table with some annotations:

create table if not exists drivers
(
driverid number generated by default on null
as identity
annotations ( primarykey
, display 'false')
, driver_number number ( 2 ) annotations ( "Permanent driver number"
, caption 'Driver number'
, display 'true'
, optional )
, code varchar2( 3 ) annotations ( "Driver code e.g. 'VER'"
, caption 'Driver code'
, display 'true'
, non_unique )
, driver_name varchar2( 32 ) not null annotations ( "Driver full name"
, caption 'Driver full name'
, display 'true'
, mandatory )
, dob date not null annotations ( "Driver date of birth"
, caption 'Driver date of birth'
, display 'true'
, mandatory )
, nationality varchar2( 16 ) annotations ( "Driver nationality"
, caption 'Driver nationality'
, display 'true' )
)
annotations ( "access" 'Public' )
/

After creating the object, you can drop, add, or replace annotations. To prevent script breakage, annotations also support the `if [not] exists` construction:
alter table drivers
modify nationality annotations ( add if not exists display 'true' )
/

This follows the same rules as any other `if not exists` statement. If the annotation name already exists, nothing happens. The `add or replace` syntax is also an option. This also always completes successfully but the value is (re)placed in the annotations.

alter table drivers
annotations ( add or replace "access" 'Restricted' )
/

Dictionary Views
Oracle Database 23c introduces several new dictionary views to track all annotations and their usage across objects in a schema. You can query `USER_ANNOTATIONS` to find all unique annotations used in your schema:

select annotation_name
from user_annotations
/

ANNOTATION_NAME
--------------------------
DISPLAY
PRIMARYKEY
CAPTION
OPTIONAL
Permanent driver number
Driver code e.g. 'VER'
NON_UNIQUE
Driver full name
MANDATORY
Driver date of birth
Driver nationality
access

12 rows selected.

To find all unique name-value pairs, query `USER_ANNOTATION_VALUES`:

select annotation_name
, annotation_value
from user_annotation_values
/
ANNOTATION_NAME ANNOTATION_VALUE
------------------------- -------------------------
access Restricted
PRIMARYKEY
DISPLAY false
Permanent driver number
CAPTION Driver number
DISPLAY true
OPTIONAL
Driver code e.g. 'VER'
CAPTION Driver code
NON_UNIQUE
Driver full name
CAPTION Driver full name
MANDATORY
Driver date of birth
CAPTION Driver date of birth
Driver nationality
CAPTION Driver nationality

17 rows selected.

Another view, `USER_ANNOTATION_USAGE`, shows which annotation name-value pairs are used for which objects:

SQL> select object_name
2 , object_type
3 , column_name
4 , annotation_name
5 , annotation_value
6 from user_annotations_usage
7 /

OBJECT_NAME OBJECT_TYPE COLUMN_NAME ANNOTATION_NAME ANNOTATION_VALUE
----------- ----------- ------------- ----------------------- --------------------
DRIVERS TABLE access Restricted
DRIVERS TABLE DRIVERID PRIMARYKEY
DRIVERS TABLE DRIVERID DISPLAY false
DRIVERS TABLE DRIVER_NUMBER Permanent driver number
DRIVERS TABLE DRIVER_NUMBER CAPTION Driver number
DRIVERS TABLE DRIVER_NUMBER DISPLAY true
DRIVERS TABLE DRIVER_NUMBER OPTIONAL
DRIVERS TABLE CODE Driver code e.g. 'VER'
DRIVERS TABLE CODE CAPTION Driver code
DRIVERS TABLE CODE DISPLAY true
DRIVERS TABLE CODE NON_UNIQUE
DRIVERS TABLE DRIVER_NAME Driver full name
DRIVERS TABLE DRIVER_NAME CAPTION Driver full name
DRIVERS TABLE DRIVER_NAME DISPLAY true
DRIVERS TABLE DRIVER_NAME MANDATORY
DRIVERS TABLE DOB Driver date of birth
DRIVERS TABLE DOB CAPTION Driver date of birth
DRIVERS TABLE DOB DISPLAY true
DRIVERS TABLE DOB MANDATORY
DRIVERS TABLE NATIONALITY Driver nationality
DRIVERS TABLE NATIONALITY CAPTION Driver nationality
DRIVERS TABLE NATIONALITY DISPLAY false

22 rows selected.

For all these views, the `ALL_` and `DBA_` views are also available. The `ALL_%` views include an extra column with the schema name where the annotation is defined. The `DBA_%` view is only available with sufficient access rights.

Querying these views can potentially help generate parts of your application code or even influence the behavior of your APEX application. For example, retrieving all annotations for a specific column in JSON format:

select object_type
, object_name
, column_name
, json_serialize(
json_arrayagg(
json_object(
annotation_name
, annotation_value
)
)
pretty ) in_jsonformat
from user_annotations_usage
where column_name = 'DRIVER_NUMBER'
group by object_type
, object_name
, column_name
/

OBJECT_TYPE OBJECT_NAME COLUMN_NAME IN_JSONFORMAT
----------- ----------- ------------- ---------------------------------------------------
TABLE DRIVERS DRIVER_NUMBER [
{
"ANNOTATION_NAME" : "Permanent driver number",
"ANNOTATION_VALUE" : null
},
{
"ANNOTATION_NAME" : "OPTIONAL",
"ANNOTATION_VALUE" : null
},
{
"ANNOTATION_NAME" : "DISPLAY",
"ANNOTATION_VALUE" : "true"
},
{
"ANNOTATION_NAME" : "CAPTION",
"ANNOTATION_VALUE" : "Driver number"
}
]

Adapting to these changes might take some getting used to, but by consistently using annotations in all projects, future developers will undoubtedly appreciate the clarity and insight they provide.