DBA Hub

📋Steps in this guide1/3

Annotations in Oracle Database 23ai/26ai

This article describes the use of annotations to document our database objects in Oracle database 23ai/26ai.

oracle 23configurationintermediate
by OracleDba
76 views
1

What's the Point?

We can think of annotations as an extension of database comments. With comments we were able to add free text to objects such as tables and columns, allowing us to describe their purpose and usage. Annotations take this a step further, allowing us to associate name-value pairs to most database objects, which can be used to describe or classify them. The names and values are free text, so we can pick anything that is meaningful to us. You may see annotations described as "application usage annotations". This simply emphasizes that annotations are mean to help the documentation of your application. More recently, Oracle have been discussing annotations in the context of AI. Annotations can be used to provide additional metadata to help AI understand the data, assuming you are making the data available to AI agents.
2

Using Annotations

If an object supports annotations, the annotations clause typically follows this format. Here is a simple example of adding annotations to a table. Notice we don't use the optional keyword while adding these annotations. Let's look at each in turn. - SurrogateKey : This annotation tells us the column is a surrogate key. Notice this annotation has no value, as the name is sufficient to provide meaning. - UI_Display : This annotation gives the preferred display name in any UI. - Classificaton : This annotation allows us to classify the type of information present. In this case we classify it as "Fruit Info". Remember, all these annotations are just free text we have made up, with the annotation values having a maximum limit of 4000 characters. They have no meaning to the database or any tool other than the meaning we associate with them. If we need spaces in the key names we must double-quote them. This is true of reserved words also. We can modify the annotations by adding or dropping annotations. In these examples we add a new annotation to the table without using the optional keyword, we drop the annotation using the keyword, and we add it again, this time using the optional keyword. We can do a similar thing with the column level annotations.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
ANNOTATIONS ( {ADD|DROP} annotation_name {'annotation_value'} {,} )

create table fruit (
  id           number        annotations (SurrogateKey, UI_Display 'Fruit ID', Classification 'Fruit Info'),
  name         varchar2(50)  annotations (UI_Display 'Fruit Name', Classification 'Fruit Info'), 
  description  varchar2(50)  annotations (UI_Display 'Description', Classification 'Fruit Info')
)
annotations (UI_Display 'Fruit Table', Classification 'Fruit Info');

alter table fruit annotations (Visibility 'Everyone');

alter table fruit annotations (drop Visibility);

alter table fruit annotations (add Visibility 'Everyone');

alter table fruit modify (id annotations (Visibility 'Hidden'));

alter table fruit modify (id annotations (drop Visibility));

alter table fruit modify (id annotations (add Visibility 'Hidden'));
3

Views

There are two main views associated with annotations. They are the and views, but you will probably only use the view. For more information see: - Application Usage Annotations - CREATE TABLE Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
set linesize 150
column object_name format a12
column object_type format a12
column column_name format a12
column domain_name format a12
column domain_owner format a12
column annotation_name format a14
column annotation_value format a20

select object_name,
       object_type,
       column_name,
       domain_name,
       domain_owner,
       annotation_name,
       annotation_value
from   user_annotations_usage
order by annotation_name, annotation_value;

OBJECT_NAME  OBJECT_TYPE  COLUMN_NAME  DOMAIN_NAME  DOMAIN_OWNER ANNOTATION_NAM ANNOTATION_VALUE
------------ ------------ ------------ ------------ ------------ -------------- --------------------
FRUIT        TABLE        ID                                     CLASSIFICATION Fruit Info
FRUIT        TABLE        DESCRIPTION                            CLASSIFICATION Fruit Info
FRUIT        TABLE        NAME                                   CLASSIFICATION Fruit Info
FRUIT        TABLE                                               CLASSIFICATION Fruit Info
FRUIT        TABLE        ID                                     SURROGATEKEY
FRUIT        TABLE        DESCRIPTION                            UI_DISPLAY     Description
FRUIT        TABLE        ID                                     UI_DISPLAY     Fruit ID
FRUIT        TABLE        NAME                                   UI_DISPLAY     Fruit Name
FRUIT        TABLE                                               UI_DISPLAY     Fruit Table
FRUIT        TABLE                                               VISIBILITY     Everyone
FRUIT        TABLE        ID                                     VISIBILITY     Hidden

11 rows selected.

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!