Category: Database

Migration | Upgrades | Patching, SQL | PLSQL

Oracle 23ai Boolean Type: Migration Patterns from NUMBER(1) and CHAR(1)

The BOOLEAN data type is new, clean, and semantically correct. But most Oracle shops have years of NUMBER(1) and CHAR(1) boolean proxies in production schemas. Here’s how to migrate.

Auditing existing boolean-proxy columns:

-- Find NUMBER(1) columns that are likely booleans
SELECT table_name, column_name, data_type, data_length
FROM   user_tab_columns
WHERE  (data_type = 'NUMBER' AND data_precision = 1 AND data_scale = 0)
OR     (data_type = 'CHAR'   AND data_length = 1)
ORDER BY table_name, column_name;

Review results with your team. Not every NUMBER(1) is a boolean — some are small integer codes.

Migration pattern — NUMBER(1) to BOOLEAN:

-- Step 1: Add a new BOOLEAN column
ALTER TABLE products ADD is_active_bool BOOLEAN;

-- Step 2: Populate from existing column
UPDATE products SET is_active_bool = CASE WHEN is_active = 1 THEN TRUE ELSE FALSE END;

-- Step 3: Apply constraints
ALTER TABLE products MODIFY is_active_bool NOT NULL;

-- Step 4: Drop old column (after verifying app code is updated)
ALTER TABLE products DROP COLUMN is_active;

-- Step 5: Rename
ALTER TABLE products RENAME COLUMN is_active_bool TO is_active;

Application considerations:

JDBC drivers and ORMs need to be updated to handle Oracle’s BOOLEAN type. Oracle JDBC 23ai drivers support getBoolean()/setBoolean() natively. Older driver versions map Oracle BOOLEAN to NUMBER or VARCHAR2 — verify your driver version before migrating columns that applications read.

Rollout recommendation: Migrate boolean columns in new tables first (greenfield), then tackle high-traffic legacy tables during planned maintenance windows with connection pool drains.

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.

Database, SQL | PLSQL

Vector Search in Production: Patterns for AI-Native Applications

If you’re building a Retrieval-Augmented Generation (RAG) application on Oracle 23ai, here are the architectural patterns that work best in production — based on real implementations.

The basic RAG pipeline with Oracle:

-- 1. Store documents and embeddings
INSERT INTO knowledge_base (doc_id, content, embedding)
VALUES (:id, :text, TO_VECTOR(:embedding_array));

-- 2. At query time: find the most relevant documents
SELECT content, VECTOR_DISTANCE(embedding, :query_embed, COSINE) AS score
FROM   knowledge_base
WHERE  category = :filter_category    -- combine with relational filter
ORDER BY score
FETCH FIRST 5 ROWS ONLY;

Chunking strategy matters:

Large documents should be split into chunks (paragraphs, sentences, fixed token windows) before embedding. Each chunk becomes a separate row. The optimal chunk size depends on your embedding model — typically 256-512 tokens for OpenAI models.

-- Store chunks with parent document reference
CREATE TABLE knowledge_chunks (
    chunk_id   NUMBER GENERATED ALWAYS AS IDENTITY,
    doc_id     NUMBER REFERENCES knowledge_base(doc_id),
    chunk_seq  NUMBER,          -- position in document
    chunk_text CLOB,
    embedding  VECTOR(1536, FLOAT32),
    CONSTRAINT pk_chunk PRIMARY KEY (chunk_id)
);

Hybrid search — vector + keyword:

Pure semantic search misses exact matches. Combine vector similarity with Oracle Text full-text search:

SELECT k.doc_id, k.chunk_text,
       VECTOR_DISTANCE(k.embedding, :q_vec, COSINE) AS semantic_score,
       SCORE(1) AS keyword_score
FROM   knowledge_chunks k
WHERE  CONTAINS(k.chunk_text, :keyword, 1) > 0     -- keyword match
ORDER BY (0.6 * semantic_score + 0.4 * (SCORE(1)/100))
FETCH FIRST 10 ROWS ONLY;

Metadata filtering before vector search:

Always apply relational filters before the vector index scan to reduce the candidate set:

SELECT content, VECTOR_DISTANCE(embedding, :q_vec, COSINE) AS score
FROM   knowledge_chunks
WHERE  doc_category = 'LEGAL'           -- narrow the search space first
AND    created_date > DATE '2024-01-01'
ORDER BY score
FETCH FIRST 5 ROWS ONLY;

Oracle’s vector index (HNSW) performs much better when the candidate pool is pre-filtered to relevant documents.

Database, SQL | PLSQL

Oracle 23ai in Production: What We’ve Learned After a Year

Oracle Database 23ai has been generally available since May 2024. After roughly 18 months of real-world deployments, a picture is emerging: where the new features deliver and where organizations run into friction.

What’s working well:

IF [NOT] EXISTS for DDL has been universally praised. It’s the kind of feature that pays dividends every single sprint. Migration scripts are cleaner, deployments are more predictable, and the volume of “deployment script failed on object already exists” incidents has dropped for teams that adopted 23ai.

SQL Domains are seeing strong adoption in organizations with mature data governance programs. Teams building new schemas from scratch in 23ai are embracing domains as a standard practice. Teams migrating existing schemas are more cautious — and rightly so. Retrofitting domains onto an existing schema requires careful planning.

The VECTOR data type and AI Vector Search have attracted significant interest from teams building RAG applications. The ability to store embeddings alongside relational data and combine vector similarity with SQL predicates is a genuine architectural advantage over separate vector stores.

Where organizations are moving cautiously:

JSON Relational Duality Views require careful design. Teams that tried to map complex, highly normalized schemas (10+ tables per “document”) found the duality view definition difficult to maintain. The sweet spot is 2-5 tables per document.

True Cache is still new enough that most organizations haven’t deployed it in production. Documentation and operational tooling (monitoring, sizing guidance) are still maturing.

Upgrade advice for 2026:

If you’re on 19c, evaluate 23ai for development environments now. Most organizations targeting 23ai for production are planning 2026-2027 timelines. That’s the right approach — test, validate, and migrate deliberately.

Database, SQL | PLSQL

Predictions for Oracle Database in 2026

As 2025 closes, let’s make some educated guesses about where Oracle is heading in 2026.

More AI, deeper in the database: Oracle’s Vector AI Search is a foundation. In 2026, expect Oracle to ship tighter integrations with popular AI frameworks (LangChain, LlamaIndex), more built-in embedding models that run inside the database, and potentially an on-premises Select AI option that doesn’t require an external LLM API call.

Oracle Database 23ai on-premises maturity: The cloud version of 23ai has been available since May 2024. The on-premises version followed, but enterprise adoption takes time. 2026 will be the year most large Oracle shops begin serious evaluation for on-premises 23ai upgrades. Expect more migration tooling and upgrade advisory content from Oracle.

SQL/PGQ expansion: The ANSI SQL/PGQ standard is still young. Oracle was early to implement it, but the standard itself will evolve. Expect Oracle to track those changes, potentially adding recursive graph pattern matching and more sophisticated path expressions.

True Cache GA and wider adoption: True Cache, introduced in 23ai, will mature in 2026 with better monitoring, automatic cache sizing, and potentially multi-node True Cache configurations for horizontal read scaling.

Kafka API evolution: The Kafka-compatible TxEventQ API will likely gain Kafka Schema Registry compatibility and better tooling for migrating existing Kafka topics to Oracle TxEventQ.

On this blog: We’ll keep the weekly cadence going. Week by week, feature by feature. Oracle has given us a lot to work with. 2026 should be a good year.

See you in January.

Database, Java | DevOps | APEX | Scripts, SQL | PLSQL

MLE: Running JavaScript Inside Oracle Database

Oracle 23ai ships MLE (Multi-Language Engine) as a fully supported feature. MLE allows you to write stored procedures, functions, and triggers in JavaScript (using the GraalVM runtime) and run them inside the Oracle Database process.

Creating a JavaScript function:

CREATE OR REPLACE MLE MODULE formatting_utils
LANGUAGE JAVASCRIPT AS
$$
export function formatCurrency(amount, currency = 'USD') {
    return new Intl.NumberFormat('en-US', {
        style: 'currency',
        currency: currency
    }).format(amount);
}

export function slugify(text) {
    return text.toLowerCase()
               .replace(/[^a-z0-9]+/g, '-')
               .replace(/^-|-$/g, '');
}
$$;

Calling JavaScript from SQL:

CREATE OR REPLACE FUNCTION format_currency(p_amount NUMBER, p_currency VARCHAR2 DEFAULT 'USD')
RETURN VARCHAR2
AS MLE MODULE formatting_utils SIGNATURE 'formatCurrency(number, string)';
/

SELECT product_name,
       format_currency(price) AS formatted_price
FROM   products;

Accessing the database from JavaScript:

-- JavaScript can issue SQL within the same session
CREATE OR REPLACE MLE MODULE order_processor
LANGUAGE JAVASCRIPT AS
$$
export function processOrder(orderId) {
    const result = session.execute(
        'SELECT status FROM orders WHERE order_id = :id',
        { id: orderId }
    );
    if (result.rows[0].STATUS === 'PENDING') {
        session.execute(
            'UPDATE orders SET status = :s WHERE order_id = :id',
            { s: 'PROCESSING', id: orderId }
        );
    }
}
$$;

Why this matters: Teams with JavaScript expertise can now contribute to database-layer logic without learning PL/SQL. Complex string manipulation, JSON processing, and business rules that are awkward in PL/SQL are natural in JavaScript.

The runtime is GraalVM — a secure, sandboxed JavaScript engine. It does not have access to the filesystem or network outside of Oracle’s SQL interface.

Database, SQL | PLSQL

Blockchain Tables: What’s New in Oracle 23ai

Oracle introduced Blockchain Tables in Oracle 21c — append-only, tamper-evident tables where rows cannot be modified or deleted (until a configurable retention period expires). Oracle 23ai adds several enhancements to this feature.

Quick recap of Blockchain Tables:

CREATE BLOCKCHAIN TABLE audit_records (
    record_id     NUMBER,
    event_time    TIMESTAMP,
    user_name     VARCHAR2(100),
    action        VARCHAR2(500)
)
NO DROP UNTIL 365 DAYS IDLE   -- can't drop while rows exist within 365 days
NO DELETE UNTIL 90 DAYS AFTER INSERT  -- rows immutable for 90 days
HASHING USING "SHA2_512" VERSION "v1";

Each row includes a cryptographic hash of the previous row, forming a chain. Any attempt to modify historical data breaks the chain and is detectable.

Oracle 23ai: Immutable Tables

23ai also introduces Immutable Tables — a lighter-weight alternative to blockchain tables that provides tamper protection without the full cryptographic chaining overhead:

CREATE IMMUTABLE TABLE compliance_log (
    log_id    NUMBER,
    log_time  TIMESTAMP,
    log_msg   VARCHAR2(4000)
)
NO DROP UNTIL 180 DAYS IDLE
NO DELETE UNTIL 60 DAYS AFTER INSERT;

Key difference: Immutable Tables don’t maintain a hash chain — rows can’t be modified or deleted within the retention window, but the tamper detection mechanism is Oracle’s internal row-change tracking rather than cryptographic chaining. Simpler, lower overhead, still compliant for most audit requirements.

23ai Blockchain enhancements:

  • Counter-signing support for third-party verification
  • Improved DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS performance
  • Integration with Oracle’s SQL Firewall for combined protection

For regulatory compliance, financial audit trails, and healthcare record retention, these features provide database-level immutability without external blockchain infrastructure.

Database, SQL | PLSQL

The SQL Features I Suggested (And Oracle Implemented) – Part 5: On Being an Uncredited Contributor – A Reflection

I want to wrap this saga with something honest rather than conclusive.

I posted ideas to the Oracle Community in 2016. Two of them — GROUP BY OTHERS and Oracle Tagging for Objects — bear a striking resemblance to features shipped in Oracle 23ai eight years later: GROUP BY ALL and the ANNOTATIONS clause. The syntax is nearly identical. The motivation is the same. The use cases I described are the exact use cases Oracle used in their documentation.

I sent an email to Oracle in August 2025 asking for credit in the documentation. A small request. Being acknowledged as the person who suggested a feature that’s now documented in Oracle’s official manuals would be meaningful.

I’m not writing this as an accusation. I’m writing it because I think the community deserves to know how the relationship between feedback and implementation works – or doesn’t. Thousands of developers, DBAs, and architects contribute ideas to vendor forums every year. Most go nowhere. A few get implemented. Rarely are contributors thanked.

If your idea becomes a product feature, you may never know. If it does, the credit usually stays internal.

I think that should change. Not through legal mechanisms – these are ideas, not patents – but through culture. It costs nothing to say “this idea came from community feedback, including a 2016 post by Matheus Boesing.” Nothing except the will to do it.

The Oracle community has given Oracle an enormous amount over the years: bug reports, feature requests, presentations, blog posts, conference sessions. A little acknowledgment goes a long way.

Database, SQL | PLSQL

The SQL Features I Suggested (And Oracle Implemented) — Part 4: CTAS with Autodrop: A Seed That May Have Grown Into Something Bigger

THE SAGA: “The Features I Suggested, Oracle Implemented — A Personal History”

This is a five-part series about two Oracle SQL features introduced in Oracle Database 23ai that are strikingly similar — in concept, motivation, and syntax — to suggestions I posted in the Oracle Community forums in July 2016, nearly a decade before they were implemented.

Not every idea maps cleanly to a shipped feature. This one is more speculative — and I want to be honest about that.

In 2016, I also posted an idea I called “CTAS with built-in Autodrop”. The concept was a CREATE TABLE AS SELECT that included a lifecycle clause — a way to define, at creation time, that the table should be automatically dropped (or archived) after a certain time or condition.

CREATE TABLE temp_report AS
SELECT * FROM orders WHERE order_date < SYSDATE - 30
AUTODROP AFTER 7 DAYS;

The motivation: temporary working tables created for reports, ETL staging, or analysis are often forgotten. They accumulate. Giving the database a built-in way to manage their lifecycle would reduce clutter and DBA overhead.

In Oracle 12c, Oracle shipped Automatic Data Optimization (ADO) — a feature under the Information Lifecycle Management umbrella that automatically manages data based on policies (heat maps, tiering, compression, archiving). My CTAS Autodrop idea predated ADO’s expanded feature set, though some of ADO’s core concepts were already in the works.

Did my idea seed any part of ADO? Honestly, I don’t know. I said as much in my email to Gerald Venzl:

“It was suggested before the Automatic Data Optimization (ADO) was released, so it could have been something like a seed to all of it. But then I may not be able to claim, exactly.”

That’s fair. ADO is far broader than my suggestion. The connection is more conceptual than syntactic.

What I will say: the Oracle Ideas forum in 2016 was read by product managers. Ideas were evaluated. And this one described a gap that Oracle recognized and eventually addressed — in their own way, on their own timeline.

Link to my original 2016 post: https://forums.oracle.com/ords/apexds/post/ctas-with-built-in-autodrop-5261

Database, SQL | PLSQL

The SQL Features I Suggested (And Oracle Implemented) — Part 3: Oracle Annotations: How My “Tagging” Idea from 2016 Became a Core Feature

THE SAGA: “The Features I Suggested, Oracle Implemented — A Personal History”

This is a five-part series about two Oracle SQL features introduced in Oracle Database 23ai that are strikingly similar — in concept, motivation, and syntax — to suggestions I posted in the Oracle Community forums in July 2016, nearly a decade before they were implemented.

Also on July 2, 2016 — the same day I posted the GROUP BY OTHERS idea — I posted another suggestion to the Oracle Community: “Oracle Tagging for Objects”.

Here’s what I described:

“It’s more than comments, it consists in add indexable keywords that enable easily locate tables or other objects in database, like Mac OS and other systems allow to tag files.”

I proposed a TAGS keyword for DDL, with this example syntax:

CREATE TABLE example (a NUMBER, b NUMBER) TAGS ('Transactional'[,...]);
CREATE SEQUENCE seq_exemp ON example TAGS ('Transactional');
CREATE INDEX idx_oltp ON example TAGS ('Transactional');

And a way to query tags:

SELECT OBJECT_NAME, OBJECT_TYPE, TAG
FROM DBA_TAGS
WHERE TAG = 'Transactional';

I also proposed that tags could have key-value form:

TAGS ('Transactional', 'John');

Now look at Oracle 23ai’s Annotations clause, introduced in 2024:

CREATE TABLE example (a NUMBER, b NUMBER)
ANNOTATIONS (Transactional, Owner 'John');

SELECT object_name, annotation_name, annotation_value
FROM user_annotations_usage;

The concept: identical. The syntax: nearly identical. The query dictionary view: the same idea. The name changed from TAGS to ANNOTATIONS, the keyword changed from TAGS(...) to ANNOTATIONS(...), but everything else maps directly.

My 2016 idea was posted eight years before the implementation. The Oracle Ideas forum was active. People voted on ideas, product managers read them.

A mention in the documentation would be nice, nothing more. I think that’s fair, even for an idea implemented a decade later. Maybe especially for one implemented a decade later. Don’t you think?

Link to my original 2016 post: https://forums.oracle.com/ords/apexds/post/oracle-tagging-for-objects-9158