Oracle's Schema Annotations feature is one of those additions that immediately makes sense once you see it.
As AI-powered development tools become increasingly database-aware, having structured metadata attached directly to database objects is becoming far more valuable than traditional comments. Annotations provide a standard way to describe tables, views, columns, APIs, and other schema objects in a way that can be consumed by both humans and tools.
So, inspired by Jeff Smith and Martin Bach's blogs on annotating your schema, I decided to add annotations to the next release of Flows for APEX across our database schema. I assumed it would be a fairly simple exercise.
It wasn't.
The Motivation
For those unfamiliar with the project, Flows for APEX is an open-source BPMN 2.0 workflow engine for Oracle APEX.
We currently support:
As part of our upcoming release we've been adding several features aimed at today's AI-powered developers, including:
Skills definitions
An XSD definition of our BPMN extensions
Scripts to create read-only schemas for MCP access
Database annotations on our schema objects
The goal was simple: provide richer metadata that can help developers, AI assistants, MCP servers, and schema analysis tools better understand the application.
Because we support both Oracle Database 19c and Oracle Database 26ai, our installation and upgrade scripts need to run successfully on both platforms.
That's where things became interesting.
How Flows for APEX Deploys Database Objects
Like many Oracle applications, we have two broad categories of database objects.
Tables
Tables are created on first installation and then migrated over time.
create table flow_processes (
...
);
Views, Packages and Other Code Objects
Views, packages, package bodies and similar objects are recreated during every upgrade using CREATE OR REPLACE.
create or replace view flow_processes_v as
select ...
This means any annotation strategy needs to be:
The Basic Syntax
The good news is that annotations themselves are easy to create.
For a table column:
alter table t1
modify (
id annotations (
add description 'Identifier'
)
);
For a view column:
alter view my_emp
modify (
empno annotations (
add description 'Employee Number'
)
);
So far, so good.
The Need for Idempotent Scripts
Installation scripts run once.
Upgrade scripts may run repeatedly during:
Development
Testing
CI/CD pipelines
Customer upgrades
Ideally, annotation scripts should be idempotent (they can be run multiple times without changing the result beyond the initial application).
Fortunately Oracle appears to provide exactly the syntax required:
alter table t1
modify (
id annotations (
add or replace description 'Identifier'
)
);
The intention is obvious:
Perfect.
Or so I thought....
The First Surprise
On Oracle Database 19c, 19.28 to at least 19.31, ADD OR REPLACE does not behave as expected.
Consider:
create table t1 (id number);
alter table t1
modify (
id annotations (
add description 'Identifier'
)
);
alter table t1
modify (
id annotations (
add or replace description 'Identifier2'
)
);
The second statement fails.
Initially I assumed there was a problem with the annotations implementation itself.
However, after discussions with Oracle, it turns out the issue is more specific.
The Real Problem: A Parser Issue
The annotations engine itself is not the problem.
Both of these work correctly:
annotations (
add description 'Identifier'
)
and
annotations (
replace description 'Identifier'
)
The problem is specifically the combined syntax:
annotations (
add or replace description 'Identifier'
)
Oracle confirmed that the parser in the 19c backport does not correctly interpret the ADD OR REPLACE syntax.
Instead of recognising:
ADD OR REPLACE
annotation = description
value = Identifier
the parser effectively interprets:
ADD
annotation = OR
value = REPLACE
which explains the resulting errors.
The same behaviour occurs on both:
Interestingly, the syntax works correctly on Oracle Database 23ai and 26ai, suggesting that the issue is specific to the 19c backport implementation.
The Next Experiment
At this point I wondered whether views might be simpler.
Since our views are recreated during every upgrade, perhaps recreating a view would remove any annotations attached to it?
For example:
create or replace view my_emp as
select * from emp;
alter view my_emp
modify (
empno annotations (
add description 'Employee Number'
)
);
create or replace view my_emp as
select * from emp;
If the annotation disappeared when the view was recreated, a subsequent ADD would succeed.
Unfortunately, that's not what happens.
The annotation survives the CREATE OR REPLACE VIEW.
The following statement fails because the annotation already exists:
alter view my_emp
modify (
empno annotations (
add description 'Employee Number'
)
);
This was actually a useful discovery.
While it complicates deployment scripts, it also means Oracle preserves annotation metadata across view recreation.
Oracle's Suggested Solution
After discussing the issue with Oracle, they suggested different approaches for views and tables.
Views
For views, annotations can be embedded directly into the view definition itself.
For example:
create table t (
c1 integer
);
create or replace view vw (
c1 annotations (
description 'Test Column'
)
)
as
select *
from t;
Because the annotation is part of the view definition, every CREATE OR REPLACE VIEW recreates the annotation automatically.
While this seems like a good workaround on simple views, implementing this on views at are UNIONs of several queries, which extract data from JSON, and other of our more complex views proved difficult and high risk - so we ended up deciding not to add annotations to views if the underlying database is < 23ai.
Tables
Tables cannot be recreated during upgrades, so a different approach is needed.
Oracle suggested adding a double statement:
alter table t
modify c1 annotations (
add if not exists description 'Identifier'
);
alter table t
modify c1 annotations (
replace description 'Identifier'
);
This effectively provides the same behaviour as ADD OR REPLACE.
The first statement ensures the annotation exists.
The second updates the value. It's slightly clunky, but it works.
Our Deployment Strategy
After all this experimentation, our annotation strategy now looks like this.
Oracle Database Earlier Than 19.28
No annotations.
The feature is not available.
Oracle Database 19c - 19.28+
For views:
For tables:
add if not exists
followed by
replace
Oracle Database 23ai and Later
Use:
add or replace
which works correctly.
