While updating my slides covering “what’s new in Oracle AI Database 26ai” I noticed that examples how to annotate views are currently absent from the SQL Language Reference. While this is getting fixed, here’s a short post how to use annotations with Oracle views.
Schema Annotations
Schema Annotations are a pretty nice feature, especially in the age of AI-assisted development, which is why they are so important. They were first introduced in Oracle Database 23ai, and later backported to 19c (19.28 to be precise). Their purpose is documented in the Database Development Guide as follows
For many applications, it is important to maintain additional property metadata for database objects such as tables, views, table columns, indexes, and domains. Annotations enable your applications to store and retrieve additional user-specific metadata about database objects and table columns. Applications can use such metadata to help render effective user interfaces and customize application logic.
They are very much straight-forward to use with tables; let’s have a look at how to annotate a view.
Annotations on Views
Let’s start with an example:
SQL> create table t as
2 select
3 object_id,
4 data_object_id,
5 object_name,
6 object_type
7 from
8* all_objects;
Table T created.
SQL> create or replace view v as
2 select
3 object_id,
4 data_object_id,
5 object_name,
6 object_type
7 from
8* t;
View V created.
At this stage, the view isn’t annotated at all:
SQL> select
2 object_type,
3 column_name,
4 annotation_name,
5 annotation_value
6 from
7 user_annotations_usage
8 where
9* object_name = 'V';
no rows selected
Let’s add some annotations to the view:
SQL> alter view v annotations (
2 purpose 'just a demo view',
3 content 'an excerpt from all_objects, provided in table t'
4* );
View V altered.
Seems to have worked! Now you can check the various dictionary views to see them. For example:
SQL> select
2 object_type,
3 column_name,
4 annotation_name,
5 annotation_value
6 from
7 user_annotations_usage
8 where
9* object_name = 'V';
Which produces the following output
OBJECT_TYPE COLUMN_NAME ANNOTATION_NAME ANNOTATION_VALUE
______________ ______________ __________________ ___________________________________________________
VIEW PURPOSE just a demo view
VIEW CONTENT an excerpt from all_objects, provided in table t
So far so good, but what about column annotations? You can create them just like you do with comments, without having to replace the view. Here you can see the annotations added, based on the description provided in the Database Reference:
alter view v modify (
object_id annotations (
add or replace content 'Dictionary object number of the object'
),
data_object_id annotations (
add or replace content 'Dictionary object number of the segment that contains the object',
add or replace note 'OBJECT_ID and DATA_OBJECT_ID display data dictionary metadata. Do not confuse these numbers with the unique 16-byte object identifier (object ID) that Oracle AI Database assigns to row objects in object tables in the system.'
),
object_name annotations (
add or replace content 'Name of the object'
),
object_type annotations (
add or replace content 'Type of the object (such as TABLE, INDEX)'
)
);
View altered.
Let’s check again if they are present:
SQL> select
2 object_type,
3 column_name,
4 annotation_name,
5 annotation_value
6 from
7 user_annotations_usage
8 where
9* object_name = 'V';
Well?
OBJECT_TYPE COLUMN_NAME ANNOTATION_NAME ANNOTATION_VALUE
______________ _________________ __________________ ___________________________________________________________________________________________________________________________________________________________________________________________________________________________________
VIEW PURPOSE just a demo view
VIEW CONTENT an excerpt from all_objects, provided in table t
VIEW OBJECT_ID CONTENT Dictionary object number of the object
VIEW DATA_OBJECT_ID CONTENT Dictionary object number of the segment that contains the object
VIEW DATA_OBJECT_ID NOTE OBJECT_ID and DATA_OBJECT_ID display data dictionary metadata. Do not confuse these numbers with the unique 16-byte object identifier (object ID) that Oracle AI Database assigns to row objects in object tables in the system.
VIEW OBJECT_NAME CONTENT Name of the object
VIEW OBJECT_TYPE CONTENT Type of the object (such as TABLE, INDEX)
7 rows selected.
Hurray, that’s that problem solved, and by the way, if you can see them, so does you MCP Server and LLM 😀
Summary
Annotations are pretty cool, especially when it comes to AI-assisted development. Using annotations your MCP server and the LLM have a much better understanding of your data. The latest SQLDeveloper Extension for VSCode even supports this natively – have a look at Jeff Smith’s post for all the details.
That’s it, Happy annotating!
Martins Blog 