8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Annotations in Oracle Database 23ai
This article describes the use of annotations to document our database objects in Oracle database 23ai.
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.
Using Annotations
If an object supports annotations, the annotations clause typically follows this format.
ANNOTATIONS ( {ADD|DROP} annotation_name {'annotation_value'} {,} )
Here is a simple example of adding annotations to a table. Notice we don't use the optional ADD
keyword while adding these annotations.
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');
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. 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 ADD
keyword, we drop the annotation using the DROP
keyword, and we add it again, this time using the optional ADD
keyword.
alter table fruit annotations (Visibility 'Everyone'); alter table fruit annotations (drop Visibility); alter table fruit annotations (add Visibility 'Everyone');
We can do a similar thing with the column level annotations.
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'));
Views
There are two main views associated with annotations. They are the USER_ANNOTATIONS
and USER_ANNOTATIONS_USAGE
views, but you will probably only use the USER_ANNOTATIONS_USAGE
view.
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>
For more information see:
Hope this helps. Regards Tim...