# Schema Annotations with Database 19c

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:

*   Oracle APEX 24.1 and higher
    
*   Oracle Database 19c and higher
    

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.

```sql
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`.

```sql
create or replace view flow_processes_v as
select ...
```

This means any annotation strategy needs to be:

*   Repeatable
    
*   Idempotent
    
*   Compatible across upgrades
    
*   Compatible across Oracle Database 19c through 26ai
    

## The Basic Syntax

The good news is that annotations themselves are easy to create.

For a table column:

```sql
alter table t1
modify (
    id annotations (
        add description 'Identifier'
    )
);
```

For a view column:

```sql
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:

```sql
alter table t1
modify (
    id annotations (
        add or replace description 'Identifier'
    )
);
```

The intention is obvious:

*   Create the annotation if it doesn't exist
    
*   Replace it if it does
    

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:

```sql
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:

```sql
annotations (
    add description 'Identifier'
)
```

and

```sql
annotations (
    replace description 'Identifier'
)
```

The problem is specifically the combined syntax:

```sql
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:

```text
ADD OR REPLACE
  annotation = description
  value      = Identifier
```

the parser effectively interprets:

```text
ADD
  annotation = OR
  value      = REPLACE
```

which explains the resulting errors.

The same behaviour occurs on both:

*   Tables
    
*   Views
    

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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

*   No annotations
    

For tables:

```sql
add if not exists
```

followed by

```sql
replace
```

### Oracle Database 23ai and Later

Use:

```sql
add or replace
```

which works correctly.