Building a Customer Management System with Oracle 23ai: Domains, Duality Views, and Annotations

I’ve been exploring the new features in Oracle Database 23ai, and I have to say, the combination of SQL Domains, JSON Relational Duality Views, and Annotations completely changes how I think about schema design. In this post, I’ll walk through building a small customer and order management system that uses all three features together. And the best part? You can run every single example right here on FreeSQL without installing anything.

The Problem

Let’s say we’re building a simple e-commerce backend. We need customer records with validated email addresses and credit card numbers, and we need order records tied to those customers. On the application side, our frontend team wants to consume the data as JSON documents. On the database side, we want clean, normalized relational tables with proper constraints.

In older Oracle versions, you would have to:

• Repeat CHECK constraints for email validation on every table that stores emails
• Build complex application-layer ORM logic to convert between relational rows and JSON objects
• Keep documentation about your schema in external wikis or README files that nobody updates

Oracle 23ai solves all three problems with native features. Let me show you how.

Setting Up the Foundation: SQL Domains

SQL Domains are reusable column-type definitions. Think of them as named templates that bundle a data type, constraints, display formatting, ordering behavior, and documentation into a single schema object. Once you create a domain, any column can reference it and automatically inherit everything.

Here’s what that looks like for email addresses and credit card numbers:

sql

PURGE RECYCLEBIN;
DROP DOMAIN IF EXISTS emails;
DROP DOMAIN IF EXISTS cc;
CREATE DOMAIN emails AS VARCHAR2(100)
  CONSTRAINT email_chk CHECK (
    REGEXP_LIKE(emails, '^(\S+)\@(\S+)\.(\S+)$')
  )
  DISPLAY  LOWER(emails)
  ORDER    LOWER(emails)
  ANNOTATIONS (
    Description 'An email address with a check constraint 
    for name @ domain dot (.) something'
  );
CREATE DOMAIN cc AS VARCHAR2(19)
  CONSTRAINT cc_chk CHECK (
    REGEXP_LIKE(cc, '^\d+(\d+)*$')
  )
  ANNOTATIONS (
    Description 'Credit card number with a check constraint 
    no dashes, no spaces!'
  );

Notice a few things here. The DISPLAY clause means that whenever someone queries an email column, it will automatically be shown in lowercase. The ORDER clause ensures sorting is also case-insensitive. And the ANNOTATIONS clause embeds documentation directly in the data dictionary. No external docs needed.

Try inserting an invalid email like not-an-email into any column using the emails domain, and the database will reject it automatically. The validation lives in the schema, not in your application code.

Creating the Tables

Now let’s create our customers and orders tables. Notice how the email column simply references the emails domain, and the credit_card column references the cc domain. No need to repeat the CHECK constraints.

sql

DROP TABLE IF EXISTS orders CASCADE CONSTRAINTS PURGE;
DROP TABLE IF EXISTS customers CASCADE CONSTRAINTS PURGE;
CREATE TABLE IF NOT EXISTS orders (
    id             NUMBER,
    product_id     NUMBER,
    order_date     TIMESTAMP,
    customer_id    NUMBER,
    total_value    NUMBER(6,2),
    order_shipped  BOOLEAN,
    warranty       INTERVAL YEAR TO MONTH
);
CREATE TABLE IF NOT EXISTS customers (
    id             NUMBER,
    first_name     VARCHAR2(100),
    last_name      VARCHAR2(100),
    dob            DATE,
    email          emails,
    address        VARCHAR2(200),
    zip            VARCHAR2(10),
    phone_number   VARCHAR2(20),
    credit_card    cc,
    joined_date    TIMESTAMP DEFAULT SYSTIMESTAMP,
    gold_customer  BOOLEAN DEFAULT FALSE,
    CONSTRAINT new_customers_pk PRIMARY KEY (id)
);
ALTER TABLE orders ADD (CONSTRAINT orders_pk PRIMARY KEY (id));
ALTER TABLE orders ADD (
  CONSTRAINT orders_fk FOREIGN KEY (customer_id) 
  REFERENCES customers (id)
);

Also worth noting: BOOLEAN is now a native SQL data type in 23ai. No more NUMBER(1) or CHAR(1) workarounds. And INTERVAL YEAR TO MONTH gives us clean warranty period tracking without date math.

Loading Sample Data

Let’s insert a handful of customers and a couple of orders:

sql

INSERT INTO customers 
  (id, first_name, last_name, dob, email, address, 
   zip, phone_number, credit_card)
VALUES  
  (1, 'Alice', 'Brown', DATE '1990-01-01', 
   'alice.brown@example.com', '123 Maple Street', 
   '12345', '555-1234', '4111111111110000'),
  (3, 'Bob', 'Brown', DATE '1990-01-01', 
   'email1@example.com', '333 Maple Street', 
   '12345', '555-5678', '4111111111111111'),
  (4, 'Clarice', 'Jones', DATE '1990-01-01', 
   'email8888@example.com', '222 Bourbon Street', 
   '12345', '555-7856', '4111111111111110'),
  (5, 'David', 'Smith', DATE '1990-01-01', 
   'email375@example.com', '111 Walnut Street', 
   '12345', '555-3221', '4111111111111112');
INSERT INTO orders 
  (id, customer_id, product_id, order_date, 
   total_value, order_shipped, warranty)
VALUES
  (100, 1, 101, SYSTIMESTAMP, 300.00, NULL, NULL),
  (101, 4, 101, SYSTIMESTAMP - 30, 129.99, TRUE, 
   INTERVAL '5' YEAR);
COMMIT;

The Magic Part: JSON Relational Duality Views

Here’s where it gets really interesting. JSON Relational Duality Views let you expose your normalized relational tables as JSON documents. The data stays in the relational tables (normalized, efficient, properly constrained), but applications can read and write it as JSON. Both representations stay perfectly in sync, automatically.

First, a simple duality view for just the customers table:

sql

CREATE OR REPLACE FORCE JSON RELATIONAL DUALITY VIEW 
  customers_dv AS 
  customers @insert @update @delete
{
    _id          : id,
    FirstName    : first_name,
    LastName     : last_name,
    DateOfBirth  : dob,
    Email        : email,
    Address      : address,
    Zip          : zip,
    phoneNumber  : phone_number,
    creditCard   : credit_card,
    joinedDate   : joined_date,
    goldStatus   : gold_customer
};

Now you can insert data as JSON:

sql

INSERT INTO customers_dv VALUES (
  '{"_id": 2, "FirstName": "Jim", "LastName": "Brown", 
    "Email": "jim.brown@example.com", 
    "Address": "456 Maple Street", "Zip": 12345}'
);
COMMIT;

That JSON insert automatically populates the underlying relational customers table. The domain validation still applies, so if you try to insert a bad email through the JSON interface, Oracle will reject it.

Nested Duality Views: Customers with Their Orders

Now for the real power. Let’s create a duality view that nests orders inside customer documents:

sql

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW 
  customer_orders_dv
  ANNOTATIONS (
    Description 'JSON Relational Duality View 
    sourced from CUSTOMERS and ORDERS'
  )
AS SELECT JSON {
    '_id'        : c.ID,
    'FirstName'  : c.FIRST_NAME,
    'LastName'   : c.LAST_NAME,
    'Address'    : c.ADDRESS,
    'Zip'        : c.ZIP,
    'orders'     : 
      [ SELECT JSON {
          'OrderID'      : o.ID WITH NOUPDATE,
          'ProductID'    : o.PRODUCT_ID,
          'OrderDate'    : o.ORDER_DATE,
          'TotalValue'   : o.TOTAL_VALUE,
          'OrderShipped' : o.ORDER_SHIPPED
        }
        FROM ORDERS o WITH INSERT UPDATE DELETE
        WHERE o.CUSTOMER_ID = c.ID
      ]
  }
FROM CUSTOMERS c;

Query it, and you get clean JSON with nested orders:

sql

SELECT * FROM customer_orders_dv o 
WHERE o.data."_id" = 1;

You can even add a new order by updating the JSON document directly using JSON_TRANSFORM:

sql

UPDATE customer_orders_dv c
SET c.data = json_transform(
    data,
    APPEND '$.orders' = JSON {
      'OrderID': 123, 
      'ProductID': 202, 
      'OrderDate': SYSTIMESTAMP, 
      'TotalValue': 150.00
    }
)
WHERE c.data."_id" = 1;
COMMIT;
SELECT * FROM customer_orders_dv o 
WHERE o.data."_id" = 1;

That single JSON update automatically inserted a new row into the relational ORDERS table with the correct foreign key. No ORM. No application-layer mapping. The database handles the translation.

Try It Yourself on FreeSQL

The complete script is available to run on FreeSQL. Click the button below, and you’ll have everything set up: domains, tables, sample data, and both duality views. You can modify the queries, try inserting invalid emails to see domain validation in action, and experiment with the JSON interface.

What I Love About This Approach

Domains eliminate copy-paste constraints. In a real production schema, you might have emails in five different tables (customers, employees, vendors, contacts, users). With domains, the validation regex lives in one place. Change it once, and every column using that domain picks up the update.

Annotations are self-documenting schemas. You can query USER_ANNOTATIONS_USAGE to discover what every domain, table, and column does. No more hunting through Confluence pages or README files to understand what a column means.

Duality Views solve the ORM problem at the database level. Your frontend developers can work with clean JSON documents. Your DBAs can work with normalized relational tables. Both see the same data, and the database keeps them in sync. No impedance mismatch, no complex mapping layer, no stale caches.

The fact that you can now experience all of this directly in your browser through FreeSQL makes it incredibly easy to learn and prototype. Select the 23ai engine, and all these features are available immediately.

Regards
Osama