Pakistan's First Oracle Blog

SQL injection and credential misuse remain serious threats to database security. Oracle SQL Firewall offers a practical way to defend against these risks by allowing only approved SQL statements and trusted connection paths for each database user.

Instead of relying solely on application-level controls, SQL Firewall operates directly inside the database kernel. This makes it difficult to bypass and gives you fine-grained control over what each user can do.

How SQL Firewall Works

SQL Firewall uses an allow-list approach. You first let it observe normal activity for a user, then create a policy that defines exactly which SQL statements and connection details are permitted. Anything outside that policy triggers a violation that can be logged or blocked in real time.

It evaluates both the SQL statement itself and the context in which it runs, including the client IP address, operating system user, and program name. This helps protect against stolen credentials and unexpected access paths.

Key Benefits in Practice

• Inspects every SQL statement, including those generated inside PL/SQL
• Works whether connections are local or remote, encrypted or not
• Gives you the choice to log violations only or actively block them
• Applies per database user, making it easy to protect application accounts or individual users
• Integrates well with other Oracle security features like Database Vault and auditing

Setting Up SQL Firewall - A Practical Approach

Here is a straightforward way to implement SQL Firewall for an application service account.

Step 1: Enable SQL Firewall

EXEC DBMS_SQL_FIREWALL.ENABLE;

Step 2: Start Capturing Normal Activity

Begin recording what the target user typically does. This example captures activity for an application user named APP:

BEGIN
  DBMS_SQL_FIREWALL.CREATE_CAPTURE(
    username       => 'APP',
    top_level_only => TRUE,
    start_capture  => TRUE
  );
END;
/

Let the application run normally for a sufficient period so the firewall learns the expected SQL patterns and connection details.

Step 3: Review What Was Captured

Check the captured data to confirm it covers the expected workload:

SELECT SQL_TEXT 
FROM DBA_SQL_FIREWALL_CAPTURE_LOGS 
WHERE USERNAME = 'APP';

Step 4: Generate the Allow-List Policy

Once you are satisfied with the captured data, create the policy:

EXEC DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST('APP');

You can review the allowed SQL statements and connection contexts using the DBA_SQL_FIREWALL_ALLOWED_* views.

Step 5: Enable Enforcement

Activate protection for the user. This example enforces allowed SQL statements and blocks violations:

BEGIN
  DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST(
    username => 'APP',
    enforce  => DBMS_SQL_FIREWALL.ENFORCE_SQL,
    block    => TRUE
  );
END;
/

You can choose to enforce SQL statements, connection context, or both. You can also decide whether to block or only log violations.

Monitoring and Ongoing Management

Review violations regularly using this query:

SELECT SQL_TEXT, FIREWALL_ACTION, IP_ADDRESS, CAUSE, OCCURRED_AT
FROM DBA_SQL_FIREWALL_VIOLATIONS 
WHERE USERNAME = 'APP'
ORDER BY OCCURRED_AT DESC;

Periodically clean up old violation records and consider exporting policies using Data Pump for backup or movement between environments.

Important Considerations

• SQL Firewall only captures statements that execute successfully
• It normalizes SQL by replacing literal values before storing signatures
• Connection context is checked at session creation time
• You can add new allowed entries later from either the capture log or violation log
• Existing sessions are not terminated when you first enable a policy

When to Use SQL Firewall

This feature works especially well for:

• Protecting application service accounts that run a known set of SQL statements
• Adding an extra layer of defense for sensitive users such as reporting accounts or DBAs
• Quickly restricting direct database access to known IP addresses or programs
• Detecting and responding to potential SQL injection attempts in real time

Final Thoughts

Oracle SQL Firewall provides a practical, database-native way to enforce least-privilege access at the SQL level. By combining allow-listing of both statements and connection contexts, it helps reduce the attack surface without requiring major changes to your applications.

Start with a focused rollout on your most critical application accounts, review the captured activity carefully, and gradually expand protection across your environment.

Organizations running Oracle workloads in AWS now have a powerful new option. Oracle Autonomous AI Database Serverless is generally available on Oracle AI Database@AWS. This allows teams to deploy a completely self-managing Oracle database directly through the AWS Console while using their existing AWS spending commitments.

The service handles patching, performance tuning, scaling, and security updates automatically, so teams spend less time on routine maintenance and more time on strategic initiatives.

The Shift Toward Hands-Off Database Management

Many companies want the benefits of automation without moving away from AWS. This release delivers exactly that. You get Oracle’s most advanced autonomous database technology running inside AWS data centers, with the same tools and purchasing experience your teams already use.

Independent research has shown strong returns for organizations adopting this approach, including major gains in team productivity and sharp reductions in unexpected outages. Those advantages are now available without leaving the AWS environment.

Reduced Day-to-Day Database Work

The service takes care of many tasks that traditionally require manual effort:

• Patches and upgrades are applied automatically with no downtime
• Indexing and query optimization happen continuously in the background
• Compute and storage scale independently based on actual demand
• Security updates are managed without scheduling windows

This automation helps database administrators and infrastructure teams become significantly more productive. They can shift focus toward architecture, data strategy, and innovation instead of ongoing maintenance.

Support for Different Workload Needs in One Service

Rather than running separate systems for different requirements, a single instance can handle multiple patterns:

• High-volume transactional applications and mixed workloads
• Analytics, data warehousing, and lakehouse scenarios with support for open table formats
• Document-centric applications using familiar JSON interfaces
• Rapid application development through low-code tools with built-in AI features

Reliable Protection for Important Systems

Mission-critical applications need strong uptime guarantees. The service includes:

• A 99.995 percent availability commitment
• Disaster recovery options across AWS regions
• Local high availability with automatic failover between availability zones
• Fully managed backups stored in Amazon S3

Deep Integration with AWS Services

Because the database runs on Oracle infrastructure inside AWS data centers, connectivity to other AWS services is fast and straightforward. Key integrations include:

• Automated backups landing in Amazon S3 with options for immutability
• Encryption using customer-managed keys through AWS KMS
• Zero-ETL data movement into Amazon Redshift for analytics
• Native metrics and events flowing into CloudWatch and EventBridge
• Provisioning and management through the AWS Console, APIs, and CloudFormation templates

Best Fit Scenarios

This offering works especially well when teams want to:

• Bring existing Oracle applications into AWS with limited changes
• Lower the operational load on database and infrastructure teams
• Build AI applications that need vector search and natural language capabilities
• Consolidate different workload types onto a single managed platform

Availability and Next Steps

The service is available now in the US East (N. Virginia) and US West (Oregon) regions. Both Oracle AI Database 26ai and 19c are supported, along with flexible licensing models.

Teams already comfortable with AWS tools can start exploring the service through familiar interfaces while taking advantage of Oracle’s automation and resilience features.

Creating a reliable retrieval-augmented generation (RAG) system usually involves a lot of boilerplate. With the langchain-oracledb integration, you can build a complete pipeline; document loading, chunking, vector storage, hybrid search, semantic caching, and conversation memory; in a clean and compact way.

This guide walks you through a practical, end-to-end implementation using Oracle AI Database as the single source of truth for vectors, text, cache, and history.

Why Oracle AI Database + LangChain?

Instead of juggling multiple systems (vector database + cache + message store), everything lives inside Oracle AI Database. This simplifies architecture, improves consistency, and gives you enterprise-grade features like transactions and security out of the box.

High-Level Architecture

Here’s how the pieces connect:

• Ingestion: Load → Split → Embed → Store in OracleVS
• Retrieval: Hybrid search combining semantic similarity and keyword search
• Intelligence Layer: Semantic cache + persistent chat history
• Answer Generation: Use retrieved context to generate responses

Step 1: Document Ingestion Pipeline

We’ll read runbook content from a database table, split it intelligently, and store the chunks with embeddings.

from langchain_oracledb.document_loaders import OracleDocLoader, OracleTextSplitter
from langchain_oracledb.vectorstores import OracleVS, DistanceStrategy
from langchain_community.embeddings import HuggingFaceEmbeddings

def ingest_documents(conn):
    # Load documents with metadata
    loader = OracleDocLoader(
        conn=conn,
        params={
            "owner": conn.username,
            "tablename": "RUNBOOK_SOURCE",
            "colname": "CONTENT",
            "mdata_cols": ["ID", "TITLE", "CATEGORY"]
        }
    )
    docs = loader.load()

    # Create vector store
    embeddings = HuggingFaceEmbeddings(model_name="all-MiniLM-L6-v2")
    
    vs = OracleVS(
        conn,
        embeddings,
        table_name="DOCUMENT_VECTORS",
        distance_strategy=DistanceStrategy.COSINE
    )

    # Split and store
    splitter = OracleTextSplitter(
        conn=conn,
        params={"by": "words", "max": 250, "split": "sentence"}
    )

    vs.add_documents(docs, text_splitter=splitter)
    return vs

Step 2: Hybrid Retrieval

Combine semantic search (vector similarity) with keyword search for better results.

from langchain_oracledb.retrievers import OracleTextSearchRetriever

def hybrid_retrieve(vector_store, query, k=5):
    # Semantic search
    semantic_docs = vector_store.similarity_search(query, k=k)
    
    # Keyword search
    keyword_retriever = OracleTextSearchRetriever(
        vector_store=vector_store,
        k=k
    )
    keyword_docs = keyword_retriever.invoke(query)
    
    # Simple fusion logic (you can improve this with RRF)
    combined = semantic_docs + keyword_docs
    # Deduplicate and rank (basic version shown)
    seen = set()
    unique_docs = []
    for doc in combined:
        doc_id = doc.metadata.get("ID")
        if doc_id not in seen:
            seen.add(doc_id)
            unique_docs.append(doc)
    
    return unique_docs[:k]

Step 3: Add Semantic Caching

Reuse answers for similar questions to reduce cost and latency.

from langchain_oracledb.cache import OracleSemanticCache
from langchain.schema import Generation

def get_cached_or_generate(conn, embeddings, query, context):
    cache = OracleSemanticCache(
        conn,
        embeddings,
        table_name="SEMANTIC_CACHE",
        score_threshold=0.85
    )
    
    cached = cache.lookup(query, "answer_cache")
    
    if cached:
        return cached[0].text, True  # cache hit
    
    # Generate new answer (call your LLM here)
    answer = generate_response(query, context)
    
    cache.update(query, "answer_cache", [Generation(text=answer)])
    return answer, False

Step 4: Persist Conversation History

Keep full chat context across sessions.

from langchain_oracledb.chat_message_histories import OracleChatMessageHistory
from langchain.schema import HumanMessage, AIMessage

def save_to_history(conn, session_id, question, answer):
    history = OracleChatMessageHistory(
        session_id,
        client=conn,
        table_name="CONVERSATION_HISTORY"
    )
    
    history.add_messages([
        HumanMessage(content=question),
        AIMessage(content=answer)
    ])
    
    return len(history.messages)

Complete End-to-End Function

Here’s how everything works together in a single function:

def ask_question(conn, vector_store, question, session_id="default"):
    embeddings = HuggingFaceEmbeddings(model_name="all-MiniLM-L6-v2")
    
    # 1. Hybrid retrieval
    relevant_docs = hybrid_retrieve(vector_store, question)
    context = "\n\n".join([doc.page_content for doc in relevant_docs])
    
    # 2. Check cache or generate
    answer, was_cached = get_cached_or_generate(conn, embeddings, question, context)
    
    # 3. Save to history
    history_length = save_to_history(conn, session_id, question, answer)
    
    return {
        "answer": answer,
        "cached": was_cached,
        "sources": len(relevant_docs),
        "history_messages": history_length
    }

How to Run This Locally

1. Install dependencies with Poetry
2. Run the sample script — it automatically spins up an Oracle AI Database Free container using Testcontainers
3. Ask questions and observe hybrid retrieval, caching behavior, and growing chat history

Benefits of This Approach

• Single database for vectors, documents, cache, and history
• Hybrid search improves answer quality
• Semantic caching reduces expensive LLM calls
• Conversation state is durable and queryable
• Very little custom code required thanks to langchain-oracledb

Final Thoughts

Building a solid RAG system doesn’t have to be complex. By leveraging Oracle AI Database through the official LangChain integration, you get a clean, maintainable pipeline that handles ingestion, retrieval, caching, and memory in one place.

This pattern scales well and keeps your architecture simple while delivering strong retrieval performance.

Start experimenting with the sample, you’ll be surprised how quickly you can get a capable system running.

 If you have been wiring local AI agents to MCP servers, you already know the uneasy feeling. You connect a model, give it some tools, send it off on a task, and then you just hope it behaves. You have no real view into what it is calling, and no easy way to stop it when it does something it should not.

I spent some hands-on time with Archestra, an open source AI platform that fixes exactly this, and ran the whole thing locally on my own hardware.

What Archestra Is

Archestra is an all-in-one, open source platform for running AI agents safely. It pulls together the pieces you would normally wire up yourself: a chat interface, a no-code agent builder, an LLM gateway, an MCP gateway, a private MCP registry, deterministic guardrails, and full observability. One Docker command brings the whole stack up. The team behind it previously worked on Grafana OnCall, so the production thinking shows.

Running It Locally

I drove everything with a local model, Qwen3.6 27B served through Ollama, on my own GPU. No cloud dependency for inference. Archestra is provider agnostic, so pointing it at a local Ollama endpoint took a single configuration step.

The Part That Matters

I built a simple agent and gave it one tool: a website reader pulled from Archestra's MCP registry. What stood out is that every MCP server runs as its own isolated pod inside a Kubernetes cluster that Archestra spins up automatically. That is real isolation, not just a local process.

I ran a task and watched the agent make its tool call live. Every step it took was visible. Then came the payoff. I set a deterministic guardrail to block that tool, re-ran the task, and the agent could no longer touch it. The block is enforced at the platform level, so a prompt cannot talk its way around it.

That is the whole point. Seeing what your agents do is good. Being able to stop them, deterministically, is what makes agentic AI safe to run.

Try It Yourself

Archestra is open source and self-hostable. Grab it from GitHub, run the Docker quickstart, and try the same flow on your own machine.

Watch the full hands-on walkthrough in the video below.

https://youtu.be/9JiA6RYpEYo

Understanding how Linux handles network packets at the kernel level can feel overwhelming — until you see how the pieces fit together. Netfilter provides the foundation, while nftables gives us a modern, flexible way to define firewall rules, NAT, and packet mangling.

Whether you’re debugging connectivity issues, writing security tools, or optimizing performance, knowing these internals helps you work more effectively with Linux networking.

Netfilter: The Kernel’s Packet Processing Framework

Netfilter is the Linux kernel’s packet filtering and mangling infrastructure. It defines well-known hook points where packets can be inspected and modified as they flow through the system:

• PREROUTING — Right after a packet arrives, before routing decisions
• INPUT — Packets destined for the local system
• FORWARD — Packets being routed through the host
• OUTPUT — Locally generated packets
• POSTROUTING — After routing, before leaving the host

Chains attached to these hooks let you enforce security policies, perform NAT, or influence routing.

nftables: The Modern Replacement for iptables

nftables brings a cleaner, more consistent syntax and better performance compared to the older iptables framework. It organizes configuration into tables, chains, rules, sets, and expressions.

Core nftables Building Blocks Tables

Containers that group related chains, sets, and rules. Common families include ip (IPv4), ip6 (IPv6), and inet (both).

nft add table ip myfirewall

Chains

Sequences of rules. Base chains attach directly to Netfilter hooks and define behavior (filter, nat, route).

nft add chain ip myfirewall input { type filter hook input priority 0 \; }

Rules

Define matching conditions and actions (accept, drop, jump, etc.).

nft add rule ip myfirewall input tcp dport 22 accept

Sets

Efficient collections for matching (IP addresses, ports, etc.).

nft add set ip myfirewall trusted_ips { type ipv4_addr \; }
nft add element ip myfirewall trusted_ips { 192.168.1.10, 10.0.0.5 }

Practical Example: Simple Firewall

Here’s how to create a basic firewall that allows SSH from trusted IPs and drops everything else:

nft add table ip myfirewall
nft add chain ip myfirewall input { type filter hook input priority 0 \; }
nft add set ip myfirewall trusted_ips { type ipv4_addr \; }
nft add element ip myfirewall trusted_ips { 192.168.1.1, 192.168.1.2 }
nft add rule ip myfirewall input ip saddr @trusted_ips accept
nft add rule ip myfirewall input drop

Behind the Scenes: User Space to Kernel

Tools like nft use libmnl and libnftnl to communicate with the kernel via Netlink. This allows atomic batch operations — multiple changes applied together or not at all — ensuring consistent firewall state.

Best Practices for Production

• Use named sets for frequently updated lists (trusted IPs, blocked addresses)
• Keep base chains simple and explicit with a final drop rule
• Leverage priorities to control execution order
• Batch operations when making multiple changes
• Monitor and log dropped packets for visibility

Conclusion

Netfilter and nftables form a powerful, unified framework for packet processing in Linux. Understanding how tables, chains, rules, and sets work together helps you build more effective firewalls, troubleshoot network issues faster, and appreciate the elegance of the modern Linux networking stack.

Whether you’re securing servers, implementing complex NAT rules, or exploring kernel internals, nftables gives you the tools to control traffic with precision and clarity.

Most Oracle DBAs and developers copy-paste the same “recommended” TNS connection string for RAC and Data Guard without fully understanding how each parameter affects real-world behavior. That changes today.

This guide breaks down the critical parameters in a typical HA connect descriptor, shows measurable timing impacts, and gives clear guidance on when to tune what — so your applications stay resilient during switchovers, failovers, and maintenance.

The Standard HA Connect String

Here’s the common pattern you’ll see in MAA documentation:

(DESCRIPTION =
  (CONNECT_TIMEOUT=90)(RETRY_COUNT=100)(RETRY_DELAY=3)
  (TRANSPORT_CONNECT_TIMEOUT=1000ms)
  (ADDRESS_LIST = (LOAD_BALANCE=on) (ADDRESS = ...))
  (ADDRESS_LIST = (LOAD_BALANCE=on) (ADDRESS = ...))
  (CONNECT_DATA = (SERVICE_NAME = my_service))
)

Let’s explore what each setting actually does and how changing it impacts connection behavior.

1. FAILOVER = ON (Default)

Controls whether the client tries alternate addresses when one fails. Keep this ON unless you have a very specific reason to disable it. Turning it OFF makes connections order-dependent and can prevent reaching an available site during role transitions.

2. LOAD_BALANCE = ON

Randomizes the starting address in an ADDRESS_LIST. This prevents one SCAN IP from being hammered and helps spread load. Strongly recommended when you have multiple addresses, especially during partial outages or maintenance.

3. RETRY_COUNT & RETRY_DELAY

RETRY_COUNT defines how many additional rounds the client makes through the address list. RETRY_DELAY adds a pause between rounds so the service has time to become available after a switchover or failover.

Tip: Use RETRY_DELAY=3 (seconds) as a good starting point. Tight loops (RETRY_DELAY=0) create unnecessary load and should be avoided in production.

4. TRANSPORT_CONNECT_TIMEOUT

This is crucial when an IP or port is unreachable. It caps how long the client waits for a TCP connect before moving to the next address. Set it low enough to fail fast during outages, but high enough to handle normal network jitter (1000ms is a common balanced value).

5. CONNECT_TIMEOUT

Limits the total time for a single connection attempt, including server process creation. Set this higher than TRANSPORT_CONNECT_TIMEOUT (commonly 60–90 seconds) to allow normal connects under load while protecting against hanging attempts.

Practical Recommendations

• For frequent role changes (Fast-Start Failover): Use LOAD_BALANCE=on and moderate RETRY_COUNT
• For stable primary with rare switchovers: Prefer LOAD_BALANCE=off with clear site ordering
• Always set TRANSPORT_CONNECT_TIMEOUT explicitly — don’t rely on defaults
• Align your application connection pool timeouts with the worst-case client wait time
• Use the latest Oracle client (26ai recommended) for millisecond precision support

Final Advice

Don’t treat the HA connection string as magic copy-paste code. Understand what each parameter controls and tune it to your environment’s failover patterns and network characteristics. Small changes here can dramatically improve application resilience during planned maintenance and unplanned outages.

Test your connection strings under simulated failure scenarios (service down, network blocked) and measure real connect times. The better you understand your client behavior, the more predictable and reliable your high-availability applications will be.

Mission-critical applications demand minimal downtime and lightning-fast recovery. With Oracle AI Database 26ai, Oracle has dramatically improved Data Guard and Active Data Guard role transitions, making high-availability architectures faster and more reliable than ever.

These enhancements push Oracle Maximum Availability Architecture (MAA) Platinum tier capabilities across Oracle’s multicloud ecosystem, giving organizations consistent, enterprise-grade resilience no matter where they run.

Game-Changing Performance Improvements

Oracle’s testing shows impressive gains:

• Up to **5x faster failovers** — often completing in under 30 seconds
• Up to **3.4x faster switchovers**
• Consistent results across both small and large workloads
• No changes required to your applications

These optimizations span database recovery, checkpoint processing, service management, and multitenant operations — delivering real reductions in Recovery Time Objectives (RTO).

MAA Platinum Tier Now Available Across Multicloud

Organizations using Oracle Database@Azure, Oracle Database@AWS, or Oracle Database@Google Cloud can now standardize on the same Platinum MAA architecture:

• Local HA with RTO under 10 seconds
• Regional DR with RTO under 30 seconds
• Zero or near-zero Recovery Point Objective (RPO)

This consistency lets teams apply the same proven best practices, operational procedures, and resiliency strategies across all their multicloud deployments.

Why This Matters for Your Business

• Less Downtime — Faster planned maintenance and unplanned recovery
• Better User Experience — Minimal disruption during role transitions
• Simplified Operations — Standardize high-availability practices across environments
• Future-Proof Architecture — Built for the most demanding mission-critical workloads

Who Should Upgrade?

Existing Exadata and Exadata Database Service customers running Active Data Guard will see immediate benefits from moving to Oracle AI Database 26ai. The performance gains make Platinum MAA tier objectives much more achievable without major architectural overhauls.

Next Steps

If you’re running mission-critical databases, now is the perfect time to evaluate Oracle AI Database 26ai. The combination of Exadata performance, Active Data Guard, and these accelerated role transitions creates one of the strongest availability platforms available today.

Explore the updated MAA reference architectures and multicloud certification matrix to see how you can strengthen your high-availability strategy across Oracle Cloud and major hyperscalers.

One of the nicest things about OKafka is how familiar it feels if you’ve used Kafka before. You configure connections with Properties objects, just like kafka-clients. The big difference? You’re talking directly to Oracle Database Transactional Event Queues instead of a separate broker.

Here’s how to set up authentication cleanly for development and production.

Two Main Authentication Paths 1. PLAINTEXT (Great for Local Dev)

Simple username/password setup using an ojdbc.properties file:

Properties props = new Properties();
props.put("security.protocol", "PLAINTEXT");
props.put("bootstrap.servers", "your-host:port");
props.put("oracle.service.name", "your_service_name");
props.put("oracle.net.tns_admin", "/path/to/config/dir");

Your ojdbc.properties file should contain:

user=testuser
password=YourStrongPassword123

2. SSL / mTLS (Production Ready)

Use Oracle Wallet for secure connections. Point to your wallet directory and specify the TNS alias:

props.put("security.protocol", "SSL");
props.put("oracle.net.tns_admin", "/path/to/wallet");
props.put("tns.alias", "your_tns_alias");

Full Working Example

Here’s a complete snippet to create an AdminClient and make a topic:

try (Admin admin = AdminClient.create(props)) {
    NewTopic topic = new NewTopic("MY_EVENTS", 5, (short) 0);
    admin.createTopics(Collections.singletonList(topic)).all().get();
    System.out.println("Topic created successfully");
}

Pro Tips for Smooth Sailing

• Always use uppercase topic names with OKafka
• Store wallet files securely and never commit them to version control
• Test with Oracle Database Free + Testcontainers for quick iterations
• Move to mTLS early in your development cycle

Final Thoughts

OKafka makes connecting to Oracle feel just like connecting to any other Kafka cluster — but you get all the power, security, and transactional guarantees of the database built right in.

No extra infrastructure. No separate cluster to manage. Just reliable event streaming where your data already lives.

Want the familiar Kafka Java APIs without standing up and managing a separate Kafka cluster? Oracle’s **OKafka** (Kafka Java Client for Transactional Event Queues) lets you produce and consume events straight from your Oracle Database — with full transactional guarantees and exactly-once semantics.

Here’s everything you need to know to get started quickly and build reliable event-driven applications on Oracle AI Database.

Why OKafka?

• Use standard Kafka Java producer/consumer code
• Events are stored and processed inside the database
• Atomic transactions between database changes and event publishing
• No separate message broker to operate and scale
• Works with Oracle Database 23ai Free and above

Quick Start: Database Setup

First, create a database user with the required privileges:

CREATE USER okafka_user IDENTIFIED BY Oracle123;

GRANT AQ_USER_ROLE TO okafka_user;
GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE TO okafka_user;
GRANT EXECUTE ON DBMS_AQ TO okafka_user;
GRANT EXECUTE ON DBMS_AQADM TO okafka_user;
GRANT SELECT ON GV_$SESSION TO okafka_user;
-- ... (full list in documentation)

Then create your first topic:

BEGIN
    DBMS_AQADM.CREATE_DATABASE_KAFKA_TOPIC(
        topicname => 'MY_TOPIC',
        partition_num => 5,
        retentiontime => 7*24*3600
    );
END;

Connection Configuration Option 1: PLAINTEXT (Simple)

security.protocol=PLAINTEXT
bootstrap.servers=your-host:port
oracle.service.name=your_service
oracle.net.tns_admin=/path/to/ojdbc.properties

Option 2: SSL (Recommended for Production)

Use Oracle Wallet for secure mTLS connections.

Building Your First OKafka App

1. Clone the OKafka distribution
2. Build with Gradle: ./gradlew jar or ./gradlew fullJar
3. Add the resulting JAR to your project

Producer Example

Properties props = new Properties();
props.put("bootstrap.servers", "your-host:port");
props.put("key.serializer", "org.apache.kafka.common.serialization.StringSerializer");
props.put("value.serializer", "org.apache.kafka.common.serialization.StringSerializer");

KafkaProducer producer = new KafkaProducer<>(props);

ProducerRecord record = new ProducerRecord<>("MY_TOPIC", "key1", "Hello from OKafka!");
producer.send(record).get();

producer.close();

Consumer Example

KafkaConsumer consumer = new KafkaConsumer<>(props);
consumer.subscribe(Collections.singletonList("MY_TOPIC"));

while (true) {
    ConsumerRecords records = consumer.poll(Duration.ofMillis(100));
    for (ConsumerRecord record : records) {
        System.out.println("Received: " + record.value());
    }
}

Best Practices

• Use transactions for atomic database + event operations
• Always handle proper error paths and rollbacks
• Test with Testcontainers + Oracle Database Free
• Monitor queue depth and consumer lag
• Start with PLAINTEXT for development, move to SSL in production

Conclusion

OKafka brings the power and familiarity of Kafka directly into Oracle Database. You get enterprise-grade messaging with transactional integrity, high availability, and zero additional infrastructure to manage.

Whether you’re building microservices, event-driven architectures, or real-time analytics, OKafka lets you leverage your existing Oracle investment for reliable pub/sub messaging.

Building event-driven applications usually means standing up another system — Kafka, RabbitMQ, or similar. But what if your database could handle reliable messaging natively, with full transactional guarantees and zero extra infrastructure?

That’s exactly what **Oracle Database Transactional Event Queues (TxEventQ)** delivers.

What Is TxEventQ?

TxEventQ is a built-in, high-performance messaging system inside Oracle Database. It supports:

• Multiple producers and consumers
• Exactly-once delivery semantics
• Partitioned queues with ordering guarantees
• Full SQL access to events and metadata

Available since Oracle Database 21c (including the free edition), it’s ready to use today.

Why Teams Are Choosing TxEventQ

• Simplified Architecture — No separate message broker to manage, patch, or scale
• Transactional Integrity — Database changes and message publishing happen atomically
• Exactly-Once Semantics — Critical for financial, compliance, and mission-critical flows
• SQL-Native — Query, join, and analyze events using familiar SQL

It’s especially powerful when you need tight coupling between data changes and event publishing — no dual-write problems.

How to Get Started

The easiest path for Java developers is the **Kafka Java Client for Oracle TxEventQ (OKafka)**. It uses the familiar Kafka APIs you already know, but talks directly to the database.

Other options include:

• PL/SQL using DBMS_AQ
• REST via Oracle REST Data Services (ORDS)
• Python, Node.js, .NET, and other language drivers

Real-World Use Cases

• Event-driven microservices inside the database
• Change Data Capture (CDC) patterns
• Application integration and workflow orchestration
• Real-time analytics and notifications

Pro Tips from the Field

• Start with the Kafka Java API if you’re already familiar with Kafka
• Use triggers for automatic event publishing on DML operations
• Leverage partitioning for high-throughput scenarios
• Combine with Oracle AI Database features for intelligent event processing

Conclusion

TxEventQ lets you bring reliable pub/sub messaging directly into your Oracle Database, eliminating the need for yet another system to manage. It’s fast, transactional, and deeply integrated with everything else Oracle Database offers.

Whether you’re modernizing legacy systems, building new event-driven apps, or simplifying your architecture, TxEventQ is worth serious consideration.

 

Writing solid Kafka Java code for Oracle AI Database’s Transactional Event Queues (using OKafka) can be tricky. Agents often miss Oracle-specific patterns around authentication, transactions, serialization, and testing.

That’s why I created a focused agent skill: **okafka-java-code** — designed to generate high-quality, production-ready OKafka applications from the start.

Why This Skill Exists

Most AI coding assistants generate OKafka code that works... but not well. They miss key Oracle behaviors like using `getDBConnection()` for transactional consistency, proper topic administration, and realistic testing with Testcontainers.

This skill packages the hard-won patterns I use daily into something any agent can reuse.

What the Skill Includes

• OKafka administration (topic creation)
• Authentication and connection properties
• Transactional producer and consumer patterns
• OSON serialization best practices
• Integration testing with Testcontainers
• Troubleshooting and common pitfalls

See It in Action

Here’s the kind of clean, correct code the skill generates for a transactional workflow:

private void publish(BusinessEvent event, boolean failAfterDatabaseWrite) throws Exception {
    producer.beginTransaction();
    try {
        producer.send(new ProducerRecord<>(topic, event.id(), event.payload())).get();
        insertProducedEvent(producer.getDBConnection(), event);
        
        if (failAfterDatabaseWrite) {
            throw new IllegalStateException("Simulated failure");
        }
        
        producer.commitTransaction();
    } catch (Exception e) {
        abortAndRethrow(e);
    }
}

And the consumer side follows the same safe transactional pattern.

The Testing Story

The skill also generates full integration tests using Testcontainers + Oracle Database Free. It validates:

• Successful commit (data + Kafka record both visible)
• Producer abort (no data persisted)
• Consumer rollback (message available for retry)

How to Use It

1. Install the skill from the GitHub repo
2. Describe your use case to your agent
3. Review and run the generated code
4. Iterate with confidence

Final Thoughts

Good agent skills shift the conversation. Instead of fixing basic setup issues, you can focus on business logic, transaction correctness, and real application behavior.

By packaging proven OKafka patterns into a reusable skill, you raise the baseline quality of every generated application — saving hours of debugging and review time.

If you work with Oracle AI Database and Kafka-style messaging, give this skill a try. It’s one of the fastest ways to go from “it compiles” to “this is production-ready.”

Need lightning-fast, in-memory caching for your modern applications? Oracle Cloud Infrastructure (OCI) Cache delivers a fully managed Valkey and Redis-compatible service that removes the operational burden of running your own cache clusters.

Whether you're accelerating database queries, powering session stores, or supporting real-time features, OCI Cache gives you enterprise-grade performance with zero infrastructure management.

Why Choose OCI Cache?

• Fully managed — Oracle handles patching, scaling, monitoring, and high availability
• Compatible with Valkey 8.1 (recommended), Valkey 7.2, and Redis 7.0
• Supports both non-sharded and sharded cluster architectures
• Easy resizing of nodes, shards, and memory allocation
• Built-in monitoring, alarms, and automatic security updates

Cluster Types Explained Non-Sharded Clusters

Ideal for most general-purpose caching needs. Scale from 1 to 5 nodes (1 primary + up to 4 replicas) with automatic distribution across fault and availability domains for high availability.

Sharded Clusters

Designed for massive scale. Split your data across 3 to 99 shards, each with its own primary and up to 4 replicas. Perfect for large datasets and high-throughput workloads.

Key Capabilities

• Resizing — Dynamically adjust node count, shard count, or memory per node
• Automatic Patching — OCI handles security updates and engine upgrades with minimal disruption
• Monitoring & Alarms — Built-in metrics for health, capacity, and performance
• High Availability — Replica nodes and intelligent placement for resilience

Getting Started with OCI Cache

1. Navigate to OCI Cache in the Console
2. Create a new cluster and choose your engine version
3. Select non-sharded or sharded topology
4. Configure memory size and node count
5. Connect your applications using standard Valkey/Redis clients

Best Practices

• Start with Valkey 8.1 for the latest features and performance
• Use sharded clusters when your dataset or throughput exceeds single-node limits
• Monitor key metrics like cache hit ratio, evictions, and CPU utilization
• Plan for resizing early — horizontal and vertical scaling are both supported
• Leverage replicas for read-heavy workloads and high availability

Conclusion

OCI Cache takes the complexity out of running high-performance caching infrastructure. With managed Valkey and Redis clusters, automatic scaling, and deep integration with the rest of OCI, you can focus on building great applications instead of managing cache servers.

Whether you're accelerating APIs, reducing database load, or powering real-time features, OCI Cache delivers the speed and reliability your applications need — without the operational overhead.

Ready to add blazing-fast caching to your OCI workloads? Create your first OCI Cache cluster today in the Oracle Cloud Console.

OCI Cache | Valkey | Redis | Managed Caching | Oracle Cloud Infrastructure

Tags: 

Most AI agents start strong in demos but quietly fail in week two. They forget context, repeat questions, or worse — hallucinate answers because they lost the thread. The fix isn’t a bigger prompt. It’s a proper memory layer.

Here’s how to combine Claude’s conversational strengths with Oracle’s secure, durable memory capabilities using SQLcl MCP Server, Oracle AI Agent Memory, and LangChain — in a way that survives real workloads.

The Core Problem Most Teams Hit

Claude is excellent at understanding intent and generating responses. But its built-in memory is scoped to the assistant experience. Once you close the chat or switch sessions, continuity breaks. Meanwhile, your Oracle data stays locked away unless you give the agent broad (and risky) access.

The solution is a layered architecture: controlled execution + durable application memory.

The Recommended Stack 1. Execution Layer: Claude + SQLcl MCP Server

Use SQLcl in MCP mode (`sql -mcp`) so Claude interacts with Oracle through explicit, auditable tools instead of raw credentials. This gives you:

• Clear tool boundaries and approval gates
• Session identification in V$SESSION
• Activity logging in DBTOOLS$MCP_LOG

2. Memory Layer: Oracle AI Agent Memory + Oracle AI Database

Oracle AI Agent Memory is a Python package that sits on top of Oracle AI Database and gives you:

• Thread management
• Durable, scoped memory records (facts, preferences, episodes)
• Hybrid retrieval (exact + semantic)
• Context card assembly for prompts

3. Orchestration Layer: LangChain (When Needed)

Use LangChain + langchain-oracledb for structured retrieval pipelines, tool routing, and complex context assembly — but keep security and authorization in the database layer.

Practical Architecture Flow

1. User talks to Claude
2. Claude calls tools via SQLcl MCP Server
3. Oracle AI Agent Memory handles durable storage and retrieval
4. LangChain orchestrates complex retrieval when required
5. All access is governed by database roles and data grants

Quick Start Recommendations

1. Begin with read-only access and one approved connection
2. Validate MCP server connectivity and logging first
3. Add Oracle AI Agent Memory once you need cross-session continuity
4. Introduce LangChain only when you need advanced retrieval orchestration
5. Always enforce scope and least privilege at the database level

Final Thought

The winning pattern isn’t “use the biggest context window.” It’s building clear boundaries between execution, memory, and orchestration — with governance baked into the database layer.

Claude gives you great conversational intelligence. Oracle gives you secure, durable, governed memory. Together, they create agents that don’t just sound smart — they stay reliable over time.

Ready to move beyond fragile RAG demos? Start with SQLcl MCP + Oracle AI Agent Memory and build a memory layer your agents can actually trust.

Oracle Cloud Infrastructure Kubernetes Engine (OKE) just got even better for high-performance workloads. You can now launch **managed node pools** directly into OCI Compute Clusters with RDMA networking — delivering ultra-low latency communication between worker nodes without giving up the operational simplicity of managed nodes.

This is a major win for distributed AI training, fine-tuning, multi-node inference, and other HPC-style workloads on Kubernetes.

Why RDMA Matters for Kubernetes

• Ultra-low latency (single-digit microseconds) between nodes
• High-bandwidth, direct memory access between GPUs across hosts
• Significantly better scaling efficiency for multi-node AI/ML jobs
• Keeps expensive GPUs utilized instead of waiting on network transfers

Key Benefits of This Release

• Full managed node pool experience (auto-scaling, upgrades, node replacement, cordon/drain)
• No more need for self-managed nodes just to get RDMA
• OKE automatically enables the required HPC plugins for RDMA
• Perfect for large-scale distributed training and inference

How to Use RDMA with OKE Managed Node Pools Prerequisites

• Use an enhanced OKE cluster
• The Compute Cluster must exist and be in ACTIVE state
• Use an RDMA-capable bare metal shape
• Placement must be in the same availability domain as the Compute Cluster
• Do not specify fault domains (managed by Compute service)

Required IAM Policy

allow any-user to {COMPUTE_CLUSTER_LAUNCH_INSTANCE}
  in compartment <compartment_name>
  where request.principal.type = 'nodepool'
    and target.resource.id = '<compute_cluster_OCID>'

Creating a Managed Node Pool with RDMA (Console)

1. In the OCI Console, go to your enhanced OKE cluster
2. Create a new managed node pool
3. Under Advanced Options → Add a Compute Cluster:
   • Select the compartment
   • Select the Compute Cluster
4. Choose an RDMA-supported shape
5. Configure placement in the matching availability domain
6. Create the node pool

OKE will automatically launch instances into the Compute Cluster with RDMA enabled.

Best Practices

• Use enhanced clusters for all new workloads
• Start with smaller clusters to validate performance gains
• Monitor GPU utilization and inter-node communication metrics
• Combine with OKE autoscaling for dynamic workloads
• Plan for the fact that Compute Cluster cannot be changed after node pool creation

Conclusion

With RDMA support for managed node pools, OKE now delivers the best of both worlds: the operational simplicity and automation of managed Kubernetes nodes combined with the ultra-low latency networking required for large-scale AI and HPC workloads.

Whether you’re doing distributed training, multi-node inference, or any communication-intensive workload, you can now take full advantage of OCI’s high-performance Compute Clusters without sacrificing managed node benefits.

RAG (Retrieval-Augmented Generation) is great for looking things up. But it’s not memory. Real AI agents need continuity — they need to remember user preferences, past decisions, policies, and completed work across sessions. That’s where a proper memory system comes in.

This guide shows how to evolve basic RAG into a production-grade memory layer that gives your agents true statefulness, continuity, and governance.

Why Basic RAG Falls Short

• No multi-turn continuity — agents forget what was just discussed
• No resumability — close the tab and everything is lost
• No long-term recall of user preferences or policies
• Prompts grow uncontrollably, leading to higher costs and lost-in-the-middle problems

RAG is retrieval. Memory is a write path + retrieval + governance loop.

What a Real Memory System Looks Like

A memory system adds a durable write path and a manager that decides what to store, how to retrieve it, and how to rebuild the prompt on every turn. It turns one-time lookup into reusable, governed knowledge.

Core loop per turn:

1. Append user message to trace
2. Retrieve relevant typed memory (policy, preferences, facts, episodes)
3. Reassemble prompt from memory (never accumulate transcript)
4. Call the model
5. Extract and promote new artifacts through a gate

The Five Types of Memory

Don’t throw everything into one vector store. Separate concerns:

1. Policy Memory

Rules, guardrails, compliance constraints. Exact-match lookup, never similarity.

2. Preference Memory

User settings and personalization (“always return JSON”, “use DD/MM/YYYY”). Fast keyed lookup.

3. Fact Memory

Durable assertions with provenance (“Acme’s production DB is in us-east-1”). Hybrid lexical + semantic retrieval.

4. Episodic Memory

Summaries of completed tasks. Reusable patterns for similar future work.

5. Trace Memory

Raw execution log for replay, debugging, and audit. Append-only, high volume.

Storage Tradeoffs That Matter

• Short-term vs Long-term — Keep working set in RAM, durable state in the database
• Filesystem vs Database — Files are great for single-tenant prototypes; databases are required for multi-tenant production
• Typed tables vs single store — Separate tables per memory type give you the right indexes, retention, and access patterns

Two Retrieval Paths You Need

1. Known-scope lookup — Policy and preferences (exact match, runs every turn)
2. Semantic discovery — Facts and episodes (hybrid lexical + vector search)

Always filter by scope before ranking — never after.

How to Add Memory to Your Agent (Practical Steps)

1. Type your memory — label everything as policy, preference, fact, episodic, or trace
2. Scope every record (tenant_id, user_id, agent_id)
3. Build a promotion gate that decides what gets stored durably
4. Reassemble the prompt on every turn from memory (don’t accumulate transcript)
5. Instrument the entire loop for replay and audit

Conclusion

RAG gives you lookup. A memory system gives you continuity, personalization, and governed recall. The difference is the write path, typed storage, scoped retrieval, and a manager that reassembles context intelligently on every turn.

Once you have a real memory layer, your agents stop feeling stateless and generic. They start to feel like they actually know the user, remember past work, and follow the right rules — every single time.

Models are shared. Your memory system is what makes your AI product yours.

Start small, type your memory early, and build the promotion gate before you scale. The investment pays off the moment your users come back for a second conversation.

AI agents are powerful, but they introduce a serious new risk: they act as autonomous insiders with broad database access. Traditional application-layer security can’t keep up. Oracle Deep Data Security changes that by enforcing end-user authorization directly inside the database — even when AI agents, vibe-coded apps, or analytics tools query data on a user’s behalf.

Now available in Oracle AI Database 26ai, Deep Data Security brings true “security at the source” to the agentic era.

The Problem: Privileged Access in the AI Era

Most applications use highly privileged database connections. The app layer is supposed to filter data for each user. But AI agents don’t follow predefined queries — they generate their own. This creates massive risk of unauthorized data exposure, especially with prompt injection or unexpected agent behavior.

Even vibe-coded or rapidly evolving applications can’t be trusted to enforce security perfectly every time.

How Oracle Deep Data Security Solves It

Deep Data Security lets you propagate the real end-user identity and context (via OAuth tokens or direct authentication) into the database at runtime. Declarative data grants then enforce row-level, column-level, and cell-level access based on who the user is — not on what the application or agent asks for.

The database automatically rewrites every query to apply the correct authorization rules before any data is returned. This works consistently whether the request comes from a traditional app, an AI agent, or a direct SQL query.

Real-World Example: Human Capital Management (HCM)

Consider an HR table containing sensitive employee data (SSN, salary, phone number, etc.).

• Emma (employee) should only see her own record.
• Marvin (her manager) should see his own record plus his direct reports, but not SSN or home address.

With Deep Data Security, both users (or any AI agent acting on their behalf) can query the same table. The database automatically returns only the data each person is authorized to see — no application code required.

Key Capabilities

• End users (distinct from schema users) authenticate directly to the database
• Data roles + data grants define precise row/column access using predicates
• ORA_END_USER_CONTEXT.username resolves the current user’s identity at runtime
• Works for AI agents, traditional apps, analytics tools, and direct SQL
• Centralized policy management — no duplication of security logic across layers

Getting Started (FastLab Highlights)

Oracle provides a complete LiveLab to explore Deep Data Security in minutes. Here’s the core flow:

1. Create end users (Emma and Marvin)
2. Define data roles (HRAPP_EMPLOYEES and HRAPP_MANAGERS)
3. Create data grants with predicates like:

   WHERE upper(user_name) = upper(ORA_END_USER_CONTEXT.username)

   or manager lookup logic
4. Connect as each user and run queries — the database enforces boundaries automatically

Even if an AI agent generates unexpected SQL, the results are still correctly restricted.

Analyst Perspective

Leading analysts agree this is a critical shift for the agentic era:

“Oracle Deep Data Security introduces identity-aware, fine-grained access control enforced at the database layer… This is a big step up from application-layer controls that are hard to enforce consistently across rapidly evolving agentic workflows.” — Steve McDowell, NAND Research Conclusion

In the age of autonomous AI agents, security at the application layer is no longer enough. Oracle Deep Data Security moves enforcement to the database — where the data lives — giving you consistent, trustworthy, and auditable protection regardless of how data is accessed.

Modern applications and AI workloads demand global speed, strong security, and simple operations. Cloudflare@OCI delivers exactly that by combining Oracle Cloud Infrastructure’s powerful compute and AI platform with Cloudflare’s massive global edge network (330+ cities worldwide).

This strategic partnership lets you accelerate and protect your OCI workloads without managing multiple vendors or complex integrations.

Key Benefits of Cloudflare@OCI

• Significantly reduce latency for users worldwide with Cloudflare’s global CDN
• Protect against DDoS, bots, and API threats at the edge — before traffic reaches OCI
• Apply consistent security policies across hybrid, multicloud, and on-premises environments
• Simplify procurement and billing — everything is available directly in the OCI Console
• Lower data transfer costs through the Bandwidth Alliance (zero egress fees for OCI Object Storage in North America)

Four Service Bundles to Match Your Needs

Choose the right level of edge protection and performance:

• Cloudflare Business Services — Essential CDN, DDoS protection, WAF, and basic rate limiting
• Cloudflare Enterprise Entry — Advanced certificate management, load balancing, and logging
• Cloudflare Enterprise Essential — Optimized routing, enhanced DDoS protection, and accelerated DNS
• Cloudflare Enterprise Advanced — Full security suite including Bot Management, advanced rate limiting, content scanning, and client-side protection

Perfect for AI Workloads

AI applications require low latency, strong security, and reliable global delivery. Cloudflare@OCI helps you:

• Accelerate AI inference and model serving at the edge
• Secure AI APIs and vector search endpoints
• Protect against prompt injection and other emerging AI threats
• Deliver consistent performance for distributed AI systems across regions

How to Get Started

1. Log in to the OCI Console
2. Navigate to Identity & Security
3. Browse and select the Cloudflare@OCI package that fits your needs
4. Purchase and connect your OCI workloads to Cloudflare
5. Configure policies and deploy in minutes

Conclusion

Cloudflare@OCI gives you the best of both worlds: OCI’s high-performance cloud infrastructure paired with Cloudflare’s industry-leading edge security and global performance platform — all managed through a single, Oracle-led experience.

Whether you’re building traditional web apps, modern microservices, or production AI systems, this partnership helps you deliver faster, safer, and more reliable experiences to users everywhere.

Regular expressions (regex) are one of the most powerful tools for working with text in Oracle AI Database. Whether you need to validate phone numbers, extract email addresses, clean messy data, or enforce complex business rules, Oracle’s built-in regex support makes it fast, efficient, and easy to implement directly in SQL.

Why Use Regular Expressions in Oracle?

• Centralize complex pattern-matching logic in the database instead of the application layer
• Enforce data quality and business rules with CHECK constraints
• Search, replace, and transform text with simple SQL functions
• Greatly simplify data validation, extraction, and cleansing tasks

Oracle SQL Regex Functions

Oracle provides five powerful functions/conditions for regular expressions:

Function / ConditionDescription REGEXP_LIKEReturns TRUE if the string matches the pattern (perfect for WHERE clauses and CHECK constraints) REGEXP_COUNTCounts how many times the pattern appears REGEXP_INSTRReturns the position where the pattern starts REGEXP_SUBSTRExtracts the matching substring REGEXP_REPLACEReplaces matching text with new content Basic Syntax and Options

REGEXP_LIKE(source_string, pattern [, match_parameter])

Common match parameters:

• 'i' — case-insensitive
• 'c' — case-sensitive (default)
• 'n' — dot (.) matches newline
• 'm' — multiline mode (^ and $ match start/end of lines)
• 'x' — ignore whitespace in pattern

Practical Examples Example 1: Enforce Phone Number Format with a CHECK Constraint

CREATE TABLE contacts (
    last_name  VARCHAR2(30),
    phone      VARCHAR2(30)
    CONSTRAINT valid_phone 
    CHECK (REGEXP_LIKE(phone, '^\(\d{3}\) \d{3}-\d{4}$'))
);

Example 2: Count Occurrences (Case-Insensitive)

SELECT REGEXP_COUNT('Albert Einstein', 'e', 1, 'i') AS count_e
FROM dual;

Example 3: Extract Email Addresses

SELECT REGEXP_SUBSTR(email, '\w+@\w+(\.\w+)+') AS email_address
FROM employees;

Example 4: Reposition Characters with Back References

SELECT 
    names,
    REGEXP_REPLACE(names, '^(\S+)\s(\S+)\s(\S+)$', '\3, \1 \2') AS formatted_name
FROM famous_people;

POSIX and PERL-Influenced Operators

Oracle supports the full set of POSIX operators plus useful PERL-style shortcuts:

• \d — digit
• \w — word character (alphanumeric + underscore)
• \s — whitespace
• \A, \Z, \z — string anchors
• Nongreedy quantifiers (+?, *?, etc.)

Best Practices

• Use regex in CHECK constraints to enforce data quality at the database level
• Prefer regex over complex string functions when pattern matching is involved
• Test thoroughly — regex can be tricky with edge cases
• Use the 'x' flag to make complex patterns more readable
• Combine with other SQL features (e.g., REGEXP_REPLACE inside UPDATE statements)

Conclusion

Regular expressions in Oracle AI Database give you enterprise-grade text processing power directly in SQL. From simple validation to complex data transformation, regex lets you solve real-world text problems efficiently without leaving the database.

Whether you’re cleaning data, enforcing business rules, or building sophisticated search features, mastering Oracle regex will make you a much more effective developer.

Building applications on Oracle AI Database that scale, stay fast, and remain secure in production requires deliberate design choices. The Oracle Real-World Performance group has proven over years of testing that three simple practices make the biggest difference: **bind variables**, **instrumentation**, and **set-based processing**.

These techniques are even more critical in the AI era, where applications often combine transactional workloads with AI agents, vector search, and MCP Server interactions.

1. Always Use Bind Variables

Bind variables are one of the easiest ways to dramatically improve scalability and security.

Instead of concatenating strings into SQL (which causes hard parsing, latch contention, and SQL injection risks), use placeholders:

-- Bad: String concatenation
INSERT INTO test (x, y) VALUES (''' || REPLACE(x, '''', '''''') || ''', ''' || REPLACE(y, '''', '''''') || '''');

-- Good: Bind variables
INSERT INTO test (x, y) VALUES (:x, :y);

Benefits:

• Only one statement is parsed and cached in the shared pool
• Massive reduction in latches and CPU overhead
• Supports thousands of users without performance degradation
• Protects against SQL injection attacks

2. Add Instrumentation Everywhere

Instrumentation means adding debug/trace code that helps you understand exactly what your application is doing at runtime.

In Oracle, this is as simple as setting MODULE and ACTION in V$SESSION or enabling SQL Trace. When something goes wrong in a multi-tier or AI-augmented application, trace files quickly show you which tier is causing the issue.

Good practice in PL/SQL or application code:

DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'AI_AGENT_WORKFLOW', 
                                 action_name => 'PROCESS_CUSTOMER_DATA');

Instrumentation is essential when working with AI agents, MCP Server, or Private Agent Factory — it lets you trace exactly what the LLM is doing in the database.

3. Prefer Set-Based Processing Over Iterative (Row-by-Row)

For large data volumes, set-based SQL is orders of magnitude faster than row-by-row processing.

Row-by-Row (Slow)

DECLARE
  CURSOR c IS SELECT * FROM ext_scan_events;
BEGIN
  FOR r IN c LOOP
    INSERT INTO stage1_scan_events VALUES r;
    COMMIT;   -- Very expensive!
  END LOOP;
END;

Set-Based (Fast)

ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+ APPEND */ INTO stage1_scan_events
SELECT * FROM ext_scan_events;

COMMIT;

Why set-based wins:

• Eliminates network round-trips and repeated parsing
• Leverages Oracle’s parallel execution and direct-path loads
• Reduces commits dramatically
• Handles billions of rows efficiently

Array processing and manual parallelism are better than pure row-by-row, but set-based SQL remains the clear winner for performance.

Summary: Three Rules for Real-World Performance

1. Use bind variables everywhere — for security and scalability
2. Instrument your code — so you can debug and monitor AI-augmented workflows
3. Think in sets, not rows — let the database do the heavy lifting

Conclusion

In the AI era, applications are more complex than ever — mixing OLTP, vector search, agents, and analytics. Following these three real-world performance practices will keep your Oracle AI Database applications fast, scalable, and easy to maintain.

Small design decisions made early (bind variables, instrumentation, set-based SQL) deliver massive returns in production.

Need to calculate different aggregates based on conditions in a single query — without multiple subqueries or CASE statements? Oracle AI Database 26ai introduces **Aggregation Filters**, a clean and powerful new feature that makes conditional SUM, COUNT, AVG, and other aggregates much simpler.

What Are Aggregation Filters?

Aggregation filters let you apply a WHERE condition directly inside an aggregate function. Only rows that match the condition are included in that specific calculation.

Syntax

aggregate_function ( expression ) FILTER ( WHERE condition )

Works with any aggregate function: SUM, COUNT, AVG, MAX, MIN, etc.

Practical Examples Example 1: Count Only Active Records

SELECT COUNT(*) FILTER (WHERE status = 'ACTIVE') AS active_count
FROM employees;

Example 2: Sum Salaries for a Specific Department

SELECT SUM(salary) FILTER (WHERE department = 'SALES') AS sales_total
FROM employees;

Example 3: Multiple Conditional Counts in One Query

SELECT
    COUNT(*) FILTER (WHERE status = 'ACTIVE')   AS active_count,
    COUNT(*) FILTER (WHERE status = 'INACTIVE') AS inactive_count
FROM employees;

Example 4: Quarterly Sales Breakdown in One Pass

SELECT
    year,
    SUM(sales)                                   AS year_sales,
    SUM(sales) FILTER (WHERE qtr_num IN (1, 2)) AS q1q2_sales,
    SUM(sales) FILTER (WHERE qtr_num IN (3, 4)) AS q3q4_sales
FROM sales_fact f
LEFT OUTER JOIN time_dim t ON (f.time_id = t.month_id)
GROUP BY year
ORDER BY year;

Key Notes

• Aggregation filters are evaluated after the main WHERE clause of the query.
• They provide a much cleaner alternative to writing separate subqueries or complex CASE expressions.
• You can combine multiple filtered aggregates in the same SELECT list.

Best Practices

• Use aggregation filters whenever you need different conditional totals in the same result set
• They are especially powerful for reporting, dashboards, and analytics queries
• Combine with GROUP BY for even more flexible breakdowns
• Great for simplifying queries that previously required multiple CTEs or subqueries

Conclusion

Aggregation filters are a small but incredibly useful enhancement in Oracle AI Database 26ai. They make your SQL cleaner, more readable, and more performant by eliminating unnecessary subqueries and complex logic.

Whether you’re building reports, dashboards, or analytical applications, aggregation filters will quickly become one of your favorite new SQL features.