Month: December 2025

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.

Java | DevOps | APEX | Scripts, SQL | PLSQL

Oracle 23ai Performance Tips for SQL Developers

As we close out the year, here’s a practical performance guide focused specifically on Oracle 23ai SQL features and how to get the most out of them.

GROUP BY ALL performance: GROUP BY ALL generates the same execution plan as an explicit GROUP BY list. There’s no performance penalty, the optimizer resolves the column list at parse time. Use it freely.

Vector index accuracy vs. speed tradeoff:

CREATE VECTOR INDEX docs_idx ON docs(embedding)
WITH TARGET ACCURACY 90;  -- faster, approximate results
-- vs.
WITH TARGET ACCURACY 99;  -- slower, more precise

For most RAG use cases, 90-95% accuracy is acceptable and significantly faster.

Schema privileges and parse overhead: Schema-level privileges (GRANT SELECT ANY TABLE ON SCHEMA) don’t affect query parse time. The privilege check at parse time is O(1) — same as object-level privileges.

Domains and constraint checking: Domain constraints are enforced at INSERT/UPDATE time, exactly like column-level CHECK constraints. There’s no additional overhead for using domains vs. inline constraints.

JSON Duality Views: Duality views over wide joins (5+ tables) can have higher write overhead because each JSON mutation must be decomposed into relational DML across multiple tables. Profile with realistic payloads before deploying to production.

MEMOPTIMIZE FOR WRITE throughput: To maximize Fast Ingest throughput, ensure the LARGE_POOL_SIZE is sufficient. Oracle recommends allocating at least 3x the expected peak buffer size:

ALTER SYSTEM SET LARGE_POOL_SIZE = 1G;

SQL Firewall learning period: Run the learning phase for at least 2 weeks to capture all application code paths (batch jobs, end-of-month processes, etc.) before enabling enforcement. An incomplete learning phase leads to false positives.

SQL | PLSQL

Oracle 23ai: The SQL Year in Review

What a year for Oracle SQL. Looking back at what we’ve covered in 2025, Oracle 23ai represents one of the most significant leaps in Oracle’s SQL language in a decade. Here’s the condensed highlight reel.

Developer ergonomics (finally addressed): IF [NOT] EXISTS for DDL, GROUP BY ALL, SELECT without FROM, EXCEPT/EXCEPT ALL, table aliases in DML, the ALIAS clause, and multirow VALUES syntax. These aren’t flashy features — they’re things developers have wanted for years, implemented at last.

Data model modernization: SQL Domains, the Boolean data type, Annotations, and enhanced JSON functions (JSON_TRANSFORM, JSON_MERGEPATCH, JSON Schema validation). Oracle’s data model is now closer to a modern standard than at any point in its history.

AI integration: The VECTOR data type and AI Vector Search are the most strategically significant additions. Combined with Select AI (natural language to SQL) and the integration with LLM providers via DBMS_CLOUD_AI, Oracle has made a credible bid to be the primary data store for AI workloads.

Graph and streaming: SQL/PGQ property graphs and GRAPH_TABLE bring graph analytics into the SQL layer. Kafka-compatible APIs for Transactional Event Queues connect Oracle to the streaming ecosystem.

Concurrency and performance: Lock-Free Reservations, Priority Transactions, True Cache, and Memoptimized Rowstore enhancements address real scalability pain points at the architecture level.

Security: SQL Firewall is a meaningful addition to Oracle’s security stack — kernel-level, transparent, and zero-code-change for applications.

If you’re on Oracle 19c or 21c and haven’t evaluated 23ai yet, 2026 is the year to start that assessment. The feature set justifies the upgrade conversation.

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.

SQL | PLSQL

JSON Duality Views in Production: Patterns and Pitfalls

JSON Relational Duality Views were introduced in April. Having had more time to work with them, let’s talk about what works well in production and where to be careful.

What works well:

Simple document APIs: If your documents map to a small number of normalized tables (2-4 tables), duality views are excellent. The REST API you get via ORDS is clean, consistent, and requires almost no backend code.

Write consistency: Because duality views write through to relational tables in a single transaction, you get ACID guarantees that no external MongoDB + Oracle sync can provide. Write to the duality view; both your JSON API and your SQL reports see the same data, immediately.

-- ORDS exposes this as a REST endpoint automatically
-- GET /api/orders/42 → returns the JSON document
-- PUT /api/orders/42 → updates through to relational tables

Where to be careful:

Complex joins: Duality views over many tables with complex join conditions become difficult to reason about. If your document model doesn’t align reasonably with your relational model, the duality view definition becomes a maintenance burden.

Aggregations: Duality views represent individual documents (rows/graphs), not aggregations. You cannot expose aggregated data (counts, sums) as a duality view — that’s still a job for regular views or materialized views.

Caching behavior: Applications that cache JSON responses aggressively may see stale data if they don’t respect Oracle’s ETags that duality views generate for optimistic locking.

Design rule of thumb: Use duality views when your document model has a clear primary entity (Order, Customer, Product) that owns related data. Avoid them for cross-cutting concerns or heavily aggregated responses.