Month: February 2026

Database, Migration | Upgrades | Patching, SQL | PLSQL

Building Idempotent Database Scripts with Oracle 23ai IF [NOT] EXISTS

Idempotent database migrations — scripts you can safely run multiple times — are a CI/CD best practice. Oracle 23ai’s IF [NOT] EXISTS syntax makes this achievable without PL/SQL wrappers. Here’s a complete migration script template.

Idempotent schema creation script:

-- ============================================================
-- Migration: v2.5.0 - Customer Preferences Schema
-- Safe to run multiple times
-- ============================================================

-- 1. Create table if it doesn't exist
CREATE TABLE customer_preferences (
    preference_id   NUMBER         GENERATED ALWAYS AS IDENTITY,
    customer_id     NUMBER         NOT NULL,
    preference_key  VARCHAR2(100)  NOT NULL,
    preference_val  VARCHAR2(4000),
    created_at      TIMESTAMP      DEFAULT SYSTIMESTAMP,
    CONSTRAINT pk_cust_pref PRIMARY KEY (preference_id),
    CONSTRAINT uq_cust_pref_key UNIQUE (customer_id, preference_key)
) IF NOT EXISTS;

-- 2. Add columns if they don't exist (new in 23ai)
ALTER TABLE customer_preferences
    ADD (updated_at TIMESTAMP) IF NOT EXISTS;

ALTER TABLE customer_preferences
    ADD (updated_by VARCHAR2(100)) IF NOT EXISTS;

-- 3. Create index if needed
CREATE INDEX IF NOT EXISTS idx_cust_pref_customer
    ON customer_preferences (customer_id);

-- 4. Create sequence if it doesn't exist (for legacy patterns)
CREATE SEQUENCE IF NOT EXISTS seq_pref_legacy
    START WITH 1 INCREMENT BY 1 NOCACHE;

-- 5. Create or replace views (always safe, no IF needed)
CREATE OR REPLACE VIEW active_preferences AS
SELECT * FROM customer_preferences
WHERE preference_val IS NOT NULL;

-- End of migration

Why this matters for DevOps:

With Flyway or Liquibase, each migration file should run exactly once. But in some environments (re-running failed migrations, cross-environment synchronization), idempotency provides a safety net. Oracle 23ai’s native IF [NOT] EXISTS removes the need for tool-specific workarounds and makes the intent clear in the script itself.

SQL | PLSQL

Property Graphs: Advanced Analytics Beyond Simple Traversals

Property graphs in Oracle 23ai aren’t just for simple hop-counting. With SQL/PGQ’s full feature set, you can run sophisticated graph analytics directly in SQL.

Centrality analysis — finding the most connected nodes:

-- Degree centrality: how many direct connections does each node have?
SELECT node_id, COUNT(*) AS degree_centrality
FROM GRAPH_TABLE (
    social_graph
    MATCH (n) -[IS follows]-> (m)
    COLUMNS (n.user_id AS node_id)
)
GROUP BY node_id
ORDER BY degree_centrality DESC
FETCH FIRST 10 ROWS ONLY;

Community detection via shared connections:

-- Find users who share more than 5 mutual followers (potential community)
SELECT a.user_id, b.user_id, COUNT(DISTINCT shared.user_id) AS mutual_count
FROM GRAPH_TABLE (
    social_graph
    MATCH (a IS users) -[IS follows]-> (shared IS users) <-[IS follows]- (b IS users)
    WHERE a.user_id != b.user_id
    COLUMNS (a.user_id, b.user_id, shared.user_id)
) g
GROUP BY g.user_id, g.user_id  -- note: use actual column refs in real query
HAVING COUNT(DISTINCT shared.user_id) > 5;

Fraud ring detection — layered hops:

-- Find all accounts reachable within 4 transactions from a known fraud seed
SELECT DISTINCT suspicious.account_id, suspicious.account_name
FROM GRAPH_TABLE (
    fraud_graph
    MATCH (seed IS accounts) -[IS transactions]->{1,4} (suspicious IS accounts)
    WHERE seed.flagged_for_fraud = 1
    AND   suspicious.flagged_for_fraud = 0
    COLUMNS (suspicious.account_id, suspicious.account_name)
)
WHERE account_id NOT IN (SELECT account_id FROM fraud_whitelist);

Combining graph traversal with relational filters and aggregations is what sets Oracle’s SQL/PGQ apart from standalone graph databases — you get the full SQL toolkit alongside the graph engine.

SQL | PLSQL

Oracle Select AI in Production: Lessons from Real Deployments

Select AI (natural language to SQL) sounds magical in demos. In production, it requires careful setup to be useful. Here’s what the production experience looks like.

Schema naming is everything:

Select AI uses table and column names to understand your schema. Cryptic names like T_CUST_HDR or AMT_NET_USD_EQUIV confuse the LLM. Before deploying Select AI, audit your schema naming:

-- Bad: obscure naming that defeats NLP
SELECT C_ID, FNM, LNM FROM T_CUST WHERE STAT = 'A';

-- Good: self-documenting naming
SELECT customer_id, first_name, last_name FROM customers WHERE status = 'ACTIVE';

If you can’t rename legacy tables/columns, use synonym layers or annotate them:

-- Add annotations as hints for Select AI
ALTER TABLE T_CUST MODIFY (FNM ANNOTATIONS (ADD UILabel 'First Name'));
ALTER TABLE T_CUST ANNOTATIONS (ADD BusinessName 'Customers');

Profile refinement with examples:

Select AI profiles support example question-SQL pairs that guide the LLM:

BEGIN
    DBMS_CLOUD_AI.ADD_EXAMPLE(
        profile_name => 'hr_assistant',
        question     => 'How many employees are in each department?',
        sql_text     => 'SELECT d.department_name, COUNT(e.employee_id) AS headcount FROM departments d LEFT JOIN employees e ON e.department_id = d.department_id GROUP BY d.department_name ORDER BY headcount DESC'
    );
END;

Adding 10-20 well-chosen examples dramatically improves accuracy for domain-specific queries.

Monitor generated SQL:

Always log what Select AI generates before it executes in production:

SELECT AI SHOWSQL 'Top 5 products by revenue last quarter'
USING PROFILE sales_ai;

Review and approve generated SQL patterns before exposing Select AI to end users.

SQL | PLSQL

JSON Duality Views: Advanced Query Patterns

After a year working with JSON Relational Duality Views, the patterns that make them genuinely powerful in production have become clear. Here are the advanced techniques.

Filtering on nested JSON fields:

-- Find orders where any item's quantity exceeds 10
SELECT data
FROM   order_dv
WHERE  JSON_EXISTS(data, '$.items[*]?(@.qty > 10)');

Combining duality view queries with relational joins:

Duality views can be joined with regular tables in SQL:

SELECT o.data.customer, o.data.status, c.credit_limit
FROM   order_dv o
JOIN   customers c ON c.customer_id = o.data._id.customer_id
WHERE  o.data.status = 'PENDING'
AND    c.credit_limit < 1000;

Using ORDS to expose duality views as REST APIs:

Once a duality view is published via ORDS, you get:

  • GET /api/orders — paginated list of all orders as JSON documents
  • GET /api/orders/42 — single order document
  • PUT /api/orders/42 — full document replacement (with optimistic locking via ETag)
  • POST /api/orders — insert a new order (decomposed into relational tables)

No controller code required. Oracle handles the JSON ↔ relational mapping.

Optimistic locking with ETags:

-- Each duality view row includes a system-generated ETag
SELECT data, ETAG
FROM   order_dv
WHERE  data._id = 42;

-- Update only succeeds if the ETag matches (no concurrent modification)
UPDATE order_dv
SET    data = JSON_MERGEPATCH(data, '{"status": "SHIPPED"}')
WHERE  data._id = 42
AND    ETAG = :last_known_etag;

This implements optimistic concurrency control — concurrent modifications are rejected rather than silently overwriting each other. A valuable pattern for mobile and REST APIs.