DEV Community: Anya Summers

We just launched a new course on how to Build Adaptive AI Agents

Anya Summers — Fri, 28 Aug 2026 11:47:19 +0000

Our new short course with DeepLearning.AI, Building Adaptive AI Agents, is live today. It covers continual and adaptive learning: the different ways in which you can make an agent better over time, and how to do it on Oracle AI Database 26ai.

Key Takeaways

Agents forget because what one session works out rarely survives into the next: some setups carry a little project context, almost none carry the reasoning behind it.
The cheapest fixes live in the token space, turning yesterday's traces into reusable skills and restructuring memory, with zero training.
Structure beats keywords: we also explore Code Knowledge Graphs, a way to let agents find the correct context faster than simple regular expression matching.
Weight-space adaptation (retraining or finetuning) is the expensive tier you reach for only when context injection, processing and manipulation stops generalizing.
Best practice is an effort ladder: start cheap, and climb only when you must.

a map of the codebase, nothing is written down, Tuesday it builds the same
map again." width="799" height="291">A stateless AI agent rebuilds the same codebase map in a later session because it was never stored.

Why does your agent keep paying the same bill twice?

You ask your agent to add a field to the checkout flow. Before it writes a line, it greps for "checkout", opens six files to work out where the data layer actually lives, and reads through your ORM setup. Twelve tool calls to build a map of a codebase that has not moved since Friday.

It builds that map again on Tuesday. And on Wednesday.

How much of last month's token spend went on your agent rediscovering things it already knew?

The failure everyone braces for with coding agents is the wrong answer. That is not the one that costs the most. The one that costs the most is the right answer, worked out correctly, thrown away when the session closed, and then worked out again from scratch the next morning.

Watch it happen on something smaller

Your agent writes a query against your own schema and hits an error:

ORA-00904: "CREATED_AT": invalid identifier

So it reads the schema, figures out the column it actually needed is DATE_CREATED, fixes the query and moves on. Fine. That is the agent doing its job.

Then the next day it is queried again, and starts a fresh session with a blank context window. Nothing reminds the new conversation that CREATED_AT was wrong yesterday, so it figures it out all over again. Identical failure, identical token spend, and nothing learned, so you're essentially wasting tokens and time on unnecessary tool calls, thinking budget, context window buildup, etc.

There is usually an absence of a feedback loop. The agent learned something valuable yesterday and then threw it away with the context window after it was done. Continual learning is the discipline of making an agent better over time, and it runs all the way from reusing yesterday's traces up to retraining the model itself. This piece, and most of the available wins, sit at the cheap end of that range: feeding the traces back so the agent stops paying for the same work twice.

Nacho spent the past couple of months watching agents fail this way, and looking at how frontier labs fix it: finetuning models, building better agent harnesses, fresh training runs. All of it assumes compute most of us do not have, and all of it reaches for the most expensive item on the list to fix something that was rarely broken there in the first place.

So the question that started this course was a smaller one. "My agent is live and getting things wrong. What is the cheapest change that makes it better, and how do I do it?" Put another way: is there anything we can do with the data we already have?

Which kind of memory is your agent actually missing?

Every autonomous agent runs the same loop: perceive, decide, act, observe, repeat. Each turn through that loop leaves traces, and those traces land in one of four memory forms:

Memory form	What it stores	AI agent example
Working	What the agent is thinking about right now	Scratchpad, AI agent notes, TODOs, etc.
Episodic	Specific past events	Previous agent conversations, errors that happened, etc.
Semantic	Stable facts	Domain knowledge about the company, private data, private database schemas, etc.
Procedural	How to do things	Skills, skill chains, workflows, notes to self, etc.

The four forms decay at different speeds, and they have different degrees of permanence: working memory evaporates, episodic memory (usually) fades, semantic facts last years, and procedural skills are almost set in stone.

the turn, episodic fades, semantic lasts years, procedural is almost set in
stone." width="799" height="302">Four types of AI agent memory—working, episodic, semantic, and procedural—shown by how long they persist.

What your agent has at the end of Monday is episodic: the session where it grepped, opened six files and worked out where the data layer lives. What it needs on Tuesday is two other things. The map itself, which barely changes, is a semantic fact. How to find the next one is a procedural skill. Nothing in a default setup turns the episode into either, so it stays an episode and dies with the context window. Which form your agent is missing decides where in the course you should start.

We built Building Adaptive AI Agents with Andrew Ng and the team at DeepLearning.AI. Nacho Martinez and Casius Lee wrote it on the Oracle Developers side, and we are both genuinely happy about how this partnership came together. It goes through all four memory forms with the code: skill extraction from real agent traces, a Code Knowledge Graph built and queried, and LoRA adapters for the point where context injection stops generalizing.

It is live today. Your agent left the traces this morning and they are still sitting there.

Take the course on DeepLearning.AI

Frequently Asked Questions

What is continual learning?

Continual learning is the practice of feeding what an agent worked out in one session back into the next one, so it stops rediscovering things it already knew. It covers everything from turning yesterday's traces into reusable skills, through restructuring how memory is retrieved, up to retraining the model itself.

What is the token layer vs the weight layer?

The token layer is everything you can change without touching the model: what goes into the context window, which skills and memories get retrieved, and how they are structured. The weight layer means changing the model itself, through retraining or finetuning. Token-layer changes are cheap, take effect on the next run, and are where most of the available wins are. Weight-layer changes are expensive, and they are for the case where no amount of context injection produces the behavior reliably.

When do I need structure-aware retrieval?

When the right memory exists but retrieval misses it: especially multi-hop questions where the answer lives two edges away from the query. Build the graph, seed anchors, and let a PageRank walk find what keyword search can't.

When do I actually fine-tune the embedder?

When retrieval fails because the embedder doesn't understand your domain's semantics: an agent needs "the code it will use next," not "the code that looks similar." Fine-tune on graded retrieval traces.

When do I need to fine-tune the weights?

When the fix must generalize across a whole class of task and no context injection reliably produces it: removing a default behavior, installing a persona. In the course we explore the use of quantization and LoRA adapters, that can allow cheap finetuning.

What's the cheapest first step?

Analyze the traces you already have as you've been using your agents (Claude, Codex, etc.). We'll teach you how to make use of those traces your agent already leaves behind.

Resources

• Course: Building Adaptive AI Agents, a DeepLearning.AI short course built with Oracle, live 26 August 2026

• Agent memory: Oracle AI Agent Memory documentation and oracleagentmemory 26.6.0

• Database: Oracle AI Database 26ai: HNSW vector indexes, VECTOR_DISTANCE, and property-graph (PGQ) traversal

Build a Controlled Cursor MCP Workflow with Oracle AI Database

Anya Summers — Fri, 28 Aug 2026 11:44:49 +0000

Giving an AI assistant database access is easy. Making that access controlled, inspectable, and repeatable is the hard part. Here is the Cursor CLI and Oracle AI Database workflow that does it.

Companion notebook: Cursor MCP with Oracle AI Database Workflow

Key Takeaways

MCP turns AI-to-database access into an explicit tool contract instead of implicit system access.

Oracle SQLcl in MCP mode, sql -mcp, is a practical way to connect Cursor CLI to Oracle AI Database through a local MCP server.

Oracle AI Database provides the persistent storage and vector search layer for memory workloads, while Oracle AI Agent Memory gives teams a Python API for threads, durable memories, scoped retrieval, and context assembly on top of it.

LangChain can provide application-side wrappers and orchestration after the Oracle-backed memory and retrieval path is in place.

A strong default is hybrid: Cursor CLI plus MCP for interactive database work, Oracle AI Database plus Oracle AI Agent Memory for durable memory, and LangChain only when the application needs reusable retrieval orchestration.

Here is how those components connect in this pattern. The important visual point is that Cursor does not connect directly to Oracle AI Database. Cursor calls SQLcl MCP tools, SQLcl uses a saved Oracle connection, and Oracle AI Database remains the durable store for memory records, retrieval evidence, vectors, and tool traces. Oracle AI Agent Memory and LangChain sit in the application layer after that database-backed path is in place.

Production success depends less on clever prompting and more on boundaries, privileges, logging, scoped retrieval, and repeatable runbooks.

This guide is for developers who want Cursor CLI to work with Oracle AI Database through explicit tools, durable memory, and reviewable retrieval evidence.

The developer path through this guide is simple:

Start with one approved Oracle connection and a read-only validation query.

Put SQLcl MCP in front of that connection so Cursor sees tools, not raw database credentials.

Check the audit and activity trail before adding more tool access.

Add Oracle AI Agent Memory when the workflow needs durable thread context, scoped recall, or reusable context cards.

Add LangChain only when you need application-side retrieval orchestration beyond the MCP interaction loop.

Controlled Cursor MCP + Oracle AI Database Workflow

Why This Architecture Is Useful for Developers

Giving an AI assistant database access is easy. Making that access controlled, inspectable, and repeatable is the hard part.

Cursor is useful because it sits close to the developer's actual work: code, terminal commands, notebooks, configuration, and implementation details. A developer can move from "why is this failing?" to "inspect the database state" inside the same working loop. That closeness is powerful, but it also makes the database boundary more sensitive.

The question is not whether Cursor can produce SQL-shaped text. The question is whether the database path is approved, observable, and easy to debug later. A useful workflow needs to preserve the request, the tool call, the database identity, the retrieved context, and the reason a risky action was allowed or blocked.

By the end of this guide, you should know how to connect Cursor CLI to Oracle AI Database through a controlled MCP boundary, when Cursor context is enough and when Oracle-backed memory is needed, and how to build a retrieval path that can be queried, audited, and scaled.

The companion notebook is intentionally practical. It validates SQLcl and Java discovery, writes a sanitized Cursor MCP config preview, checks the saved SQLcl connection alias, creates memory tables, inserts simulated Cursor/MCP teaching traces, tests lexical, vector, and hybrid retrieval, initializes Oracle AI Agent Memory with the current 26.6 configuration shape, and finishes with a validation snapshot.

The workflow has four layers. Cursor CLI is the developer-facing agent interface. SQLcl MCP is the tool boundary. Oracle AI Database is the durable substrate for memory, traces, and retrieval. The notebook is the build-and-validation harness that proves the pieces are wired correctly before the workflow is handed to Cursor.

Layer	Responsibility
Cursor CLI	Developer-facing MCP client and agent interface.
SQLcl MCP	Exposes declared Oracle tools to Cursor; it is the tool boundary.
Oracle AI Database	Stores durable data, retrieval evidence, vectors, metadata, traces, and enforces database privileges.
Oracle AI Agent Memory	Provides application APIs for users, agents, threads, durable memories, scoped retrieval, and context assembly.
LangChain	Wraps Oracle-backed retrieval results as Document objects and supports application-side orchestration.

The Two Execution Loops

Building on that split between tool boundary and durable store, the system naturally forms two execution loops:

Loop A is the operational interaction loop: Cursor CLI works with MCP to discover tools, inspect data, run bounded read-only queries, and return results immediately.
Loop B is the durable memory and retrieval loop: application code writes history, tool logs, memory records, chunks, and embeddings to Oracle AI Database, then retrieves context before a later answer or workflow step.

Dual Execution Loop: MCP Interaction and Durable Memory

SQLcl MCP handles live tool use. Oracle AI Agent Memory handles durable memory and scoped recall. Most production setups need both loops, but they solve different problems.

Reproducing the SQLcl MCP and Cursor CLI Workflow

The setup should be reproducible. SQLcl runs in MCP mode with sql -mcp. Cursor CLI launches it as an MCP server and talks to Oracle through declared tools, not through direct access. Connections come from saved SQLcl profiles that you create and test before Cursor uses them.

Cursor does not invent them at runtime; it reuses profiles you have already created and validated.

Prerequisites before you connect Cursor CLI:

Oracle SQLcl 25.2.0 or higher.

Oracle JRE 17 or 21.

Cursor IDE or Cursor CLI if you want to use the generated MCP configuration outside the notebook.

At least one saved SQLcl connection profile under ~/.dbtools, created with password persistence for MCP use.

A database user with the minimum permissions required for the workflow. Start with read-only access and a sanitized development or replica environment where possible.

The notebook deliberately treats the saved SQLcl connection alias as a first-class artifact. In local development, that alias is what lets SQLcl MCP connect without forcing the agent to assemble credentials dynamically.

The notebook then generates a sanitized Cursor MCP config preview. The preview is intentionally safe: it shows the server command and arguments without exposing secrets. It does not overwrite your real Cursor MCP configuration.

For the saved connection itself, the important detail is -savepwd. MCP cannot stop and ask a human for the password every time Cursor invokes a database tool. The saved alias becomes the repeatable local path Cursor can use after you have reviewed it.

{ 
  "mcpServers": { 
    "sqlcl": { 
      "command": "/absolute/path/to/sql", 
      "args": ["-mcp"] 
    } 
  } 
}

That small JSON block defines the connection between Cursor and SQLcl MCP Server. Cursor interacts with the database through the tools and permissions exposed by the MCP server, using the saved SQLcl connection profile you created and tested first. Save this configuration in .cursor/mcp.json for a project-scoped setup or ~/.cursor/mcp.json globally, restart Cursor or Cursor CLI, then run:

cursor-agent mcp list 
cursor-agent mcp list-tools sqlcl

A useful first prompt is intentionally constrained:

Use SQLcl MCP to list available saved Oracle connections. Do not run DML or DDL.

Validation checklist before expanding access: Run sql -mcp locally and confirm the server starts.

Restart Cursor CLI and confirm the SQLcl tools are discoverable.

Run one read-only query against an approved schema.

Check database-side MCP activity logs and session metadata.

Document the connection alias, database user, grant scope, restrict level, and troubleshooting owner.

Good first proof looks like this:

The MCP server starts without a Java or path error.

Cursor lists the SQLcl MCP tools after restart.

A read-only query succeeds against the expected schema.

The notebook audit trail records the expected tool interaction in cursor_tool_logs.

For live Cursor CLI + SQLcl MCP validation, confirm the database/session activity through your normal Oracle monitoring path.

A denied query fails because of the database role, not because a prompt asked nicely.

A useful MCP boundary is more than tool discovery. The notebook models read-only defaults, confirmation requirements, scope checks, and controlled failure examples so denied and warning states are visible.

Read-only default: start with inspection and diagnostics before allowing changes.

Confirmation gate: require explicit approval for medium-risk, write-like, or destructive actions.

Scope control: keep user, tenant, and schema filters close to the database query.

Failure trace: store denied calls and warnings as evidence instead of hiding them.

What a Successful Notebook Run Shows

The notebook is not just setup prose. It produces concrete checkpoints that make the workflow inspectable.

The first useful result is a deterministic Cursor/MCP timeline. The sample data uses explicit event sequence values and simulated event timestamps so the workflow order is stable every time the notebook is rerun:

step  event_kind    actor                              result 
1     CONVERSATION  user                               initial support-job request 
2     CONVERSATION  assistant                          SQLcl MCP read-only plan 
3     MCP_TOOL      sqlcl.connections/list_connections SUCCESS 
4     MCP_TOOL      sqlcl.sql/query                    SUCCESS 
5     MCP_TOOL      sqlcl.sql/query                    DENIED / PRIVILEGE_SCOPE 
6     CONVERSATION  assistant                          grounded summary

That ordering matters because operational memory is only useful if the answer can be traced back to the request, the tool calls, and the permission boundary that shaped the result.

The grounding package also returns visible evidence before the assistant answer is assembled:

Status: READY 

Top evidence: 

- Saved SQLcl connections for MCP 

- SQLcl MCP execution boundary 

- Tool logging baseline 

- LangChain as orchestration glue

If retrieval is empty or too weak, the notebook returns INSUFFICIENT_CONTEXT and displays a safe empty-result message instead of trying to select columns from a missing evidence table.

The final snapshot should show every local layer that is ready:

Cursor MCP config                  generated 
SQLcl MCP runtime                  ready 
SQLcl saved connection             ready 
Oracle AI Database memory          ready 
Oracle AI Agent Memory package     ready 
Lexical search                     ready 
Native VECTOR execution path       ready 
Demo embeddings                    demo ready 
Hybrid retrieval                   ready 
LangChain wrapper                  ready 
validation_action_needed           0

That is the practical bar for this demo: Cursor setup artifacts are generated, SQLcl MCP prerequisites are validated, Oracle memory tables are populated, retrieval works, Agent Memory initializes, and the notebook separates native VECTOR readiness from deterministic demo embeddings.

Simulated Teaching Data, Not Live Cursor Telemetry

One important boundary in the companion notebook is that the operational records are simulated teaching data. The notebook inserts sample conversation rows and sample tool-log rows to show what a production workflow should preserve: the user's request, Cursor's plan, tool calls, outcomes, controlled failures, and retrieval evidence.

Those rows are not live telemetry captured from Cursor CLI, and the notebook does not automatically observe, scrape, or stream Cursor CLI activity. Live Cursor validation still happens through Cursor's MCP configuration and the SQLcl MCP server. The notebook proves the database-backed memory, retrieval, and validation pattern around that workflow so the pieces are inspectable and repeatable.

Why Put Application Memory Records in Oracle AI Database, Not Just Outputs

Once the first MCP tool calls work, the next challenge is continuity. This is where long-term memory for AI agents becomes different from short-lived chat context.

If memory lives only in chat context, the system is fragile. If memory is scattered across files without structure, retrieval and auditing become expensive over time.

A database-backed memory model is usually cleaner and more scalable. The companion notebook builds this memory layer from scratch so the mechanics are visible, then shows how Oracle AI Agent Memory sits on top of it once the substrate is working.

Memory categories that matter in practice:

Conversation memory keeps the important user and assistant turns that future sessions may need.
Operational memory keeps tool calls, outcomes, warnings, and failures so a team can debug what happened.
Semantic memory adds embeddings so the system can find relevant context even when the user asks in different words.

In practice, hybrid retrieval for agent memory usually combines exact operational terms, such as sql -mcp or cursor_mcp, with semantic search over memory records.

The notebook shows the lower-level mechanics first so the storage and retrieval path is visible. This is also a context engineering problem: the application has to decide which memories, tool traces, and retrieval results should be assembled before Cursor or another assistant answers. Oracle AI Agent Memory then gives application code a cleaner package API over that same database-backed idea.

Where Oracle AI Agent Memory Fits

Oracle AI Agent Memory sits between your application code and Oracle AI Database. The package manages conversation threads, durable memory records, scoped retrieval, and context assembly while Oracle AI Database remains the storage layer underneath.

The notebook includes an abbreviated package-backed memory pattern. It initializes OracleAgentMemory with a database connection pool and a custom local deterministic embedder. LocalCursorEmbedder is notebook code, not a built-in Oracle AI Agent Memory embedder.

The local embedder is intentionally billing-free, which makes the notebook runnable for people who do not want to attach paid model usage to a tutorial.

In Oracle AI Agent Memory 26.6, use MemoryExtractionConfig(extract_memories=False) instead of the older inline extract_memories=False parameter, and use memory_store_id instead of table_name_prefix.

from oracleagentmemory.apis.searchscope import SearchScope 
from oracleagentmemory.core import MemoryExtractionConfig 
from oracleagentmemory.core.oracleagentmemory import OracleAgentMemory 
  
db_pool = oracledb.SessionPool( 
    user=CONFIG["ORACLE_USER"], 
    password=CONFIG["ORACLE_PASSWORD"], 
    dsn=CONFIG["ORACLE_DSN"], 
    min=1, 
    max=4, 
    increment=1, 
) 
  
agent_memory = OracleAgentMemory( 
    connection=db_pool, 
    embedder=LocalCursorEmbedder(dimensions=32), 
    llm=None, 
    memory_extraction_config=MemoryExtractionConfig(extract_memories=False), 
    schema_policy="create_if_necessary", 
    memory_store_id="cursor_oam_local", 
)

Use oracleagentmemory from your application layer when you need package-managed users, agents, memories, threads, scoped retrieval, and context assembly. Keep systems of record separate from memory records: memory helps provide context, but application logic and authoritative data sources should still decide what is true, allowed, and final.

Implementation note: use a schema whose default tablespace supports the JSON objects created by Agent Memory. If a local SYSTEM schema sits on a tablespace that rejects JSON object creation, the better production answer is a dedicated application schema.

Production note: the package-managed expired-record purge job needs CREATE JOB or an equivalent scheduler-job privilege, or a managed schema setup flow. Without that privilege, expired messages and memories will not be purged automatically. For a local notebook demo, this is acceptable as long as the limitation is visible.

Store and Search: What a Realistic Memory Looks Like

A realistic Cursor memory is not generic trivia about a user. For this workflow, memory should capture how a developer actually works: the connection name they used, the SQLcl path that succeeded, the MCP config location, the failed privilege boundary, the retrieval query that helped, and the final fix that should be reused later.

thread = agent_memory.create_thread( 
    user_id=AGENT_MEMORY_USER_ID, 
    agent_id=AGENT_MEMORY_AGENT_ID, 
) 
  
thread.add_memory( 
    "Developer validated Cursor CLI with SQLcl MCP alias cursor_mcp " 
    "against local Oracle AI Database service FREEPDB1." 
) 
  
results = agent_memory.search( 
    query="Cursor SQLcl MCP alias validation and Agent Memory setup", 
    scope=SearchScope(user_id=AGENT_MEMORY_USER_ID, agent_id=AGENT_MEMORY_AGENT_ID), 
)

That kind of memory pays off because it is operational. It can help Cursor answer the next question with context from the previous debugging session, but it is still scoped and retrievable through a database-backed API.

Vector Search, Native VECTOR, and Demo Embeddings

Vector search is part of the Oracle AI Database memory story. In a real application, embeddings usually come from a model and are indexed with Oracle AI Database vector capabilities.

The notebook separates two ideas that are easy to accidentally blur:

Native VECTOR readiness means the database can store and search vectors through the Oracle AI Database vector path.
Deterministic demo embeddings are local, repeatable vectors used so the notebook can run without external model billing.

The deterministic embeddings are useful for portability and inspection, but they should not be described as productionsemantic embeddings. For production, replace the notebook's demo_embed() or LocalCursorEmbedder with a supported embedding model after cost, latency, privacy, and retrieval-quality review.

The final notebook snapshot makes this separation explicit with two rows: Native VECTOR execution path and Demo embeddings.

Hybrid search pipeline combining tenant filtering, Oracle Text lexical search, Oracle VECTOR search, scoring, and a grounding package.

Where LangChain Adds Value

LangChain should not be treated as the source of truth. Cursor CLI does not call LangChain directly in this architecture, and LangChain is not the permission boundary, memory store, or audit layer.

In this notebook, LangChain is used as a compatibility layer. The custom Oracle-backed hybrid_search() path performs retrieval, then the results are wrapped as LangChain Document objects so applications that already expect LangChain interfaces can consume them.

By the time LangChain is introduced, the database tables, package memory, retrieval scores, and validation snapshot already exist. LangChain becomes a wrapper around evidence, not a substitute for evidence.

Use it when the consuming application already expects retrievers, documents, chains, or tool orchestration. If the application only needs direct SQL, package-backed Agent Memory search, or a simple evidence table, the extra abstraction can make debugging harder.

How to Move This from Demo to Production

The difference between demo success and production success is disciplined operations. In this workflow, the first failures to check are usually integration issues: SQLcl discovery, Java runtime, saved connection aliases, database permissions, and retrieval configuration. A local notebook can prove the wiring. A production workflow needs smaller database roles, managed secrets, clear MCP approval policy, repeatable environment setup, and monitoring aroundmemory writes and tool calls.

Access and privilege model:

Use a dedicated application schema where possible, then tighten grants with least-privilege roles and quotas.

Keep saved SQLcl aliases separate by role and environment instead of sharing one broad connection.

Start read-only wherever possible and gate write operations with explicit confirmation workflows.

Use schema allowlists and separate accounts for development, test, and production.

Observability model:

Log tool name, thread ID, timestamp, status, and sanitized inputs and outputs.

Classify failures into runtime, discovery, connection, permission, query, and retrieval categories.

Keep a troubleshooting playbook in the repo so setup issues do not become tribal knowledge.

Check whether retrieval quality changes as more data and memory records are added.

Reliability model:

Prefer deterministic SQL patterns with bounded result sets.

Use retrieval-first context assembly for memory-heavy tasks.

Avoid giant context stuffing as a substitute for memory design.

Review and prune tool surfaces periodically.

Move from the local deterministic embedder to a supported embedding model after cost, latency, and privacy review.

What to Check When the Workflow Fails

When this workflow fails, start with the integration points below.

Runtime failure: sql -mcp does not start.

Check the absolute SQLcl path, confirm Java is available, and run sql -mcp outside Cursor first. Resolve runtime issues before checking assistant behavior.

Discovery failure: Cursor does not see tools.

Check the Cursor MCP configuration, confirm the configured command points to the SQLcl executable, and restart or reload Cursor CLI after edits.

Connection failure: tools are present but queries fail immediately.

Check the saved SQLcl connection alias, confirm the profile lives under the expected SQLcl connection store, and verify password persistence for the MCP workflow. Then test the same connection outside Cursor.

Permission failure: queries execute selectively and fail on specific objects.

Check the database role first. A selective failure can be the right outcome when least privilege is working. Add grants intentionally and keep read-write access separate from the initial validation path.

Retrieval quality failure: answers are fluent but weakly grounded.

Inspect the retrieved records before blaming the model. Check chunk size, metadata filters, embedding choice, top-k settings, and whether the query is asking for exact history, semantic similarity, or operational logs. The notebook's grounding package includes an INSUFFICIENT_CONTEXT path and guards empty evidence before displaying result columns.

Why the Hybrid Model Is Usually the Best Long-Term Design

No single layer handles both execution and memory well. Trying to force everything into Cursor context gets messy fast: you either lose control over execution, or you stuff too much state into prompts just to keep things working. On the other side, if you only build backend memory systems, you lose the speed and usability that makes an assistant useful during development.

The hybrid approach works because it does not force one layer to do everything. MCP handles live tool execution, Oracle AI Database keeps durable evidence, Oracle AI Agent Memory provides the memory API, and LangChain is added only when the application needs that shape.

In real teams this usually evolves over time. Start with Cursor CLI, SQLcl MCP, read-only access, and strong logging. Once people rely on the workflow, the gaps become visible: lost context, weak traceability, repeated setup work, or retrieval that is hard to explain. That is when database-backed memory and structured retrieval become worth adding.

For Cursor specifically, the value is that the assistant can stay in the developer loop without becoming an unreviewed database actor. Cursor can help plan, inspect, and explain. SQLcl MCP exposes the database path as tools. Oracle AI Database keeps the durable evidence. That is the combination that makes the workflow useful after the demo.

Conclusion

A Cursor and SQLcl MCP workflow becomes useful when it is treated as an engineering pattern, not just a setup trick. Cursor keeps the developer moving, SQLcl MCP keeps database access explicit, and Oracle AI Database keeps the evidence durable enough to inspect later.

The result is a workflow a team can inspect. You can see what Cursor asked for, which tool path ran, what the database allowed, which memory records were retrieved, and how the final answer was assembled.

That is the shift that matters: from assistant access that is implicit and hard to audit, to explicit boundaries, durable memory, and evidence a developer can actually debug. For database-connected development, that is what turns Cursor from a helpful local assistant into part of a controlled engineering workflow.

Frequently Asked Questions

What is MCP in this context?

MCP is the protocol boundary that lets Cursor call explicit tools exposed by a server instead of accessing systems implicitly.

What does MCP protect, and what does it not protect?

MCP makes the tool interface explicit and reviewable: Cursor can call only the tools exposed by the server. It does not replace database security. The saved SQLcl connection profile, database user, grants, roles, network controls, and database policies determine what those tools can actually access or change.

Why use SQLcl for Oracle MCP?

SQLcl already understands Oracle workflows and can run as the Oracle SQLcl MCP server with sql -mcp, making the Oracle integration practical and direct.

Is this Cursor IDE or Cursor CLI?

The workflow is Cursor CLI oriented because the notebook validates the SQLcl MCP path using a local MCP serverdefinition and manual Cursor CLI checks. The same MCP server can be configured in project-scoped .cursor/mcp.json or global ~/.cursor/mcp.json, depending on how your team uses Cursor.

Why include Oracle AI Database if MCP already works?

MCP handles the execution boundary. Oracle AI Database handles durable memory, retrieval, vector search, concurrency, observability, and governance.

Do I need an OpenAI API key?

An external model-provider API key is only needed if you change the notebook to use a provider-backed embedding or LLM service. The default notebook path uses a local deterministic embedder so people can run the Agent Memory package section without adding billing details.

Why include LangChain if Oracle already stores memory?

Because many teams already use LangChain-shaped retrievers and chains. The notebook shows how Oracle-backed retrieval can fit that interface.

Is this RAG vs agent memory?

Not exactly. RAG retrieves external knowledge for a response, while agent memory preserves useful context, decisions, tool traces, and workflow state across sessions. In practice, production systems often use both.

What is the minimum viable setup?

SQLcl MCP configured in Cursor CLI, one safe saved Oracle connection, and a read-only validation flow.

Should production start with read-write permissions?

Usually no. Start read-only, log everything important, and add write scopes gradually with explicit approvals.

What is the best rollout strategy?

Pilot in development with read-only access and strong logging, then expand capabilities in controlled phases as the team learns which memory and tool paths are actually useful.

Companion Troubleshooting Appendix

Minimum viable setup: SQLcl MCP configured in Cursor CLI, one approved Oracle connection, read-only validation, and database-side activity logging.

First checks: confirm sql -mcp starts, Cursor sees the tools after restart, and the saved SQLcl connection alias resolves.

Environment model: use separate credentials and policies for dev, test, and prod, with stricter controls as capability expands.

Logging model: capture tool name, timestamp, thread ID, status, sanitized input/output summaries, and relevant SQLcl MCP log records.

Retrieval quality: tune chunk size, enrich metadata, review embedding choice, and evaluate retrieval against representative queries.

Common anti-pattern: expanding tool surfaces before ownership, logging standards, and runbooks are in place.

Rollout path: pilot in dev with read-only access and strong logging, then expand capabilities in controlled phases.

Schema: Agent Memory package tables need a schema and tablespace that can create the package's JSON-backed objects.

Scheduler job: package-managed expiry purge needs scheduler-job privilege or managed setup for production.

Model calls: an API key can be present but still fail if the model provider account has no quota.

Resources

Oracle AI Agent Memory documentation

Oracle AI Database 26ai documentation

oracleagentmemory on PyPI

SQLcl MCP Server

End-to-end Companion notebook

Hybrid Retrieval for Agent Memory: Vector, Lexical, and Metadata Together

Anya Summers — Fri, 28 Aug 2026 11:42:47 +0000

Hybrid retrieval for agent memory is a multi-stage pipeline. Pure vector search is the prototype.

The companion repo implements the full pipeline against the schema and two-layer pattern from Persistent Memory and Derived Context, From Prompt to Persistence part 1, and part 2: hybrid SQL with in-database PREDICTION reranking, a LangChain retriever, a LangGraph retrieval graph, and the evaluation suite with its golden-set labeling flow, runnable end to end against an Oracle AI Database 26ai Free instance on the 23-document research corpus built for this article.

Companion notebook: Hybrid Retrieval Pipeline

Key takeaways

Hybrid retrieval for agent memory starts with database-enforced metadata filters. It then combines vector and lexical candidates through rank fusion, with optional reranking.
Vector search finds semantic matches. Lexical search pins identifiers and exact strings. SQL and JSON metadata constrain scope and lifecycle before applying source and type restrictions. Those filters run before either search mode ranks a row.
Every stage has to earn its place on a labeled set. In the companion experiment, equal-weight hybrid fusion underperformed vector search on the small corpus, while reranking improved ordering at a material latency cost.

Jane's research assistant at Acme has good days and bad days. Some answers are sharp, cited, exactly right. Others are vaguely on-topic in a way that's hard to complain about but impossible to trust. There's no obvious pattern. Same model, same prompt template, same memory schema underneath. The team suspects the model, because the model is the part everyone watches.

Retrieval is often the first place to look. When the right rows reach the context window, the model has a better chance of producing a grounded answer. When they do not, even a capable model may answer confidently from the wrong material. The model cannot recover a passage the retrieval pipeline never supplied.

This is the third piece in a series. From Prompt to Persistence part 1 and part 2 built the multi-tenant schema: eight typed memory tables, four scope columns, row-level security on the tenant boundary, and provenance on every durable row. Persistent Memory and Derived Context separated the canonical layer from the derived layer so every retrieved row still points to something true.

Part 2 ended with vector and lexical scores side by side, deliberately unfused. This article picks up there. The schema stores the memory. The two-layer pattern keeps it honest. This pipeline reads it and assembles the working set the agent uses.

Why your agent feels slightly off

The question we'll use throughout comes from the research assistant scenario the whole series has been building. Jane asks:

"What did the Letta paper say about memory eviction, and how does that compare to what the AgentCore docs recommend?"

This one sentence stresses every part of a retrieval system:

"Letta" and "AgentCore" are exact-match identifiers. Embedding models under-weight rare proper nouns, so vector search will happily return chunks about memory management from papers that never mention either name.
"Memory eviction" is a concept. The Letta paper might call it context pruning, message eviction, or recursive summarization. Lexical search will miss every paraphrase.
"The docs the user has access to" is a scope constraint. Only ingested papers and vendor docs in collections Jane's agent is allowed to read, inside Acme's tenant boundary, should be candidates at all. No similarity score enforces that. Only a filter does.
Comparing two sources means the context window needs good chunks from both documents rather than five redundant chunks from whichever one embeds closer to the query.

Run it through pure vector top-k against the research corpus the companion notebook builds and the answer surfaces AgentCore only incidentally, never the passage that answers the question. Run it through hybrid retrieval and both sources appear, with the first AgentCore retention passage at fused rank eleven. Reranking brings that passage to rank five, after four Letta passages. Context budgeting then preserves coverage from both sources so the agent can make the comparison Jane asked for.

Same model every time. The thing that moved was retrieval. That's the thesis: retrieval is an architecture.

Vector, lexical, metadata: three retrieval modes, three failure modes

This pipeline uses three retrieval modes, and each one covers a failure mode the other two leave exposed. Whether both ranking modes earn a place depends on the corpus and query mix.

Vector retrieval ranks by semantic similarity. It's the mode that handles paraphrase, synonyms, conceptual matches, the reason memory eviction can find a chunk about "pruning stale messages from the context window." Its failure mode is specificity. Rare tokens, product names, error strings, version numbers, benchmark identifiers: all of them get diluted into an embedding that mostly encodes the surrounding topic. Ask a vector index about "Letta" and it hears "a paper about agent memory," which describes half the corpus.

Lexical retrieval ranks by term matching using Oracle Text relevance scores. It's the mode that nails exact identifiers, code fragments, error messages typed verbatim. A user who pastes ORA-51805 into the chat deserves the document containing ORA-51805, and no embedding model should be trusted with that job. Its failure mode is the inverse: any vocabulary shift between the query and the document breaks it. "Memory eviction" finds nothing in a paper that only ever says "context pruning."

Metadata retrieval does not rank. It filters on scope, lifecycle, time, source, collection, and type. Those predicates become a security boundary only when the database enforces them with row-level security or an equivalent policy. An application filter is useful for retrieval scope, but it is not sufficient tenant isolation because a caller can forget it. A relevance score is an opinion. A database-enforced scope predicate is a boundary.

Mode	Strong at	Fails at	Role in the pipeline
Vector	Paraphrase, synonyms, concepts	Rare terms, identifiers, exact strings	Candidate generation
Lexical	Identifiers, code, verbatim phrases	Rephrasing, vocabulary drift	Candidate generation
Metadata	Scope, time, policy, source, type	Ranking anything	Filtering; security when policy-enforced

Jane's question needs all three at once: metadata to bound the search to her tenant and collections, lexical to pin "Letta" and "AgentCore," vector to find eviction discussed in other words. Only the filter is mandatory for correctness — a missing scope predicate is a security bug, while a missing retriever is only a quality one.

Figure 1: Three retrieval modes and the failure mode each one covers for the others.

The hybrid retrieval pipeline, stage by stage

The order of stages follows one principle borrowed directly from query optimization: filter early, rerank late. Cheap, high-selectivity operations run first and shrink the candidate set. Expensive, high-precision operations run last, on the smallest set that still contains the answer. Databases have applied this thinking to query plans for fifty years. Retrieval pipelines are query plans, and most of them are written by people who've never looked at one.

Stage 1: query understanding. Before anything touches an index, the raw question gets decomposed: extract entities ("Letta", "AgentCore"), expand the concept vocabulary (memory eviction to context pruning, message eviction, summarization), and classify intent. A factual lookup, a procedural recall, and an open-ended comparison want different candidate mixes from different tables. A production implementation can use one fast-model call that returns a small JSON object with entities and expanded terms, plus intent and time bounds. The companion notebook uses fixed rules and a small thesaurus for the same step. This keeps the demo repeatable and removes model-response variance from the retrieval comparison. The plan must still yield a valid lexical expression when a question has no proper nouns or thesaurus hits; the fallback is the question's own content words.

Stage 2: metadata filtering. Apply scope and policy before any ranking happens. In the schema from From Prompt to Persistence (Part 1), the tenant predicate is appended by row-level security, so it's enforced even if every other stage of the pipeline has a bug. The remaining predicates are the working scope block from part 1 (user_id/agent_id/thread_id with NULL inheritance, deleted_at IS NULL, valid_until still open) plus whatever the query understanding stage produced: collection restrictions, time windows, source types. This stage is typically cheaper than ranking and can shrink the candidate set substantially when the predicates are selective. Every row it removes is a row the expensive stages never touch.

Stage 3: hybrid candidate generation. Vector search and lexical search run against the filtered set, each producing its own ranked top-k (50 is a reasonable default for each side). They disagree with each other constantly, and that's the point. The vector pool has the paraphrases. The lexical pool has the identifiers. The answer to Jane's question lives in the union.

Stage 4: score fusion. Vector distances and lexical scores live on incompatible scales, so don't combine the scores. Combine the ranks. Reciprocal rank fusion (RRF) gives each candidate 1/(60 + rank) from each list it appears in and sums them. RRF avoids score calibration and has one constant, conventionally set to 60. A calibrated linear combination can outperform it when measured score distributions support the added complexity, so treat RRF as a strong baseline rather than a guaranteed winner.

Stage 5: reranking. A cross-encoder scores the query and candidate together, which can improve ordering after candidate generation. It is also the most expensive stage, so it runs on 40 candidates instead of the full corpus. In the companion evaluation, this stage produced the largest NDCG gain, along with roughly 2.2 seconds of p50 added latency.

Stage 6: context budgeting. The token budget is fixed before retrieval starts, and the reranked list gets cut to fit it. The notebook uses a greedy budget pass instead of simple truncation: prefer diversity across source documents for a comparison question, drop near-duplicate chunks even when they score well, and stop early when scores fall off a cliff. More tokens of bad context is just more expensive bad context, and evaluations of long-context models show that task performance can drop when relevant material appears in the middle, exactly where a lazy pipeline dumps its marginal hits. What you didn't include is a decision too. The output of this stage is the working set: the ranked, budgeted, provenance-tagged slice of memory that the agent loop's Infer & Act phase actually consumes. How that working set gets ordered and laid out inside the prompt is the next article in this series; this pipeline's job ends at deciding what earned a seat.

Figure 2: The six-stage pipeline. Candidate counts shrink left to right; per-candidate cost grows.

One thing worth making explicit, because it connects this article to the last one: the pipeline reads the derived layer, and only the derived layer, for ranking. Embeddings, text indexes, materialized projections: all of it is derived context, rebuilt from canonical memory under the sync policies from the two-layer article. The metadata filters run against canonical scope and lifecycle columns. Every candidate that survives to the working set carries its provenance (source_event_id, document ID, version) back to a canonical row. Fast path for ranking with true path for citation. The arrow between them points one direction.

The hybrid query in one SQL statement

Now the centerpiece. Stages 2 through 5 (metadata filtering, both candidate generators, RRF fusion, and the cross-encoder rescore) as one SQL statement against the knowledge base tables from part 1. Not an integration project. A query.

One piece of DDL is needed on top of the part 1 schema. Entity and summarization memory already carry Oracle Text indexes for their lexical halves; the knowledge base chunks need the same:

CREATE INDEX idx_kb_chunk_text ON knowledge_base_chunk (content)
  INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('SYNC (ON COMMIT)');

With that in place, here's Jane's question as a query plan. :query is the natural-language question; :lex_query is the Oracle Text expression the query understanding stage built from the extracted entities and expanded terms (something like {Letta} OR {AgentCore} OR {memory eviction} OR {context pruning}. Brace every term. Oracle Text reserves ABOUT, ACCUM, AND, NEAR, NOT and WITHIN as operators, so an ordinary English word lifted from a user's question is a syntax error unescaped. The question "where do models attend least within a long context?" fails on within. Braces make the content literal, which also handles hyphens, and it saves maintaining a reserved-word list that has to track Oracle's):

WITH
-- Stage 3a: vector candidates over the metadata-filtered set
vec_pool AS (
  SELECT c.id,
         ROW_NUMBER() OVER (
           ORDER BY VECTOR_DISTANCE(c.embedding,
             VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING :query AS DATA),
             COSINE)) AS vec_rank
    FROM knowledge_base_chunk c
    JOIN knowledge_base_document d ON d.id = c.document_id
   WHERE d.collection IN ('ingested-papers', 'vendor-docs')     -- Stage 2 starts here
     AND d.valid_until IS NULL AND d.deleted_at IS NULL
     AND c.deleted_at IS NULL
     AND (d.user_id IS NULL OR d.user_id = :user_id)
     AND JSON_VALUE(c.metadata, '$.section_type') <> 'references'
   ORDER BY VECTOR_DISTANCE(c.embedding,
             VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING :query AS DATA),
             COSINE)
   FETCH FIRST 50 ROWS ONLY
),
-- Stage 3b: lexical candidates over the same filtered set
lex_pool AS (
  SELECT c.id,
         DENSE_RANK() OVER (ORDER BY SCORE(1) DESC) AS lex_rank
    FROM knowledge_base_chunk c
    JOIN knowledge_base_document d ON d.id = c.document_id
   WHERE CONTAINS(c.content, :lex_query, 1) > 0
     AND d.collection IN ('ingested-papers', 'vendor-docs')
     AND d.valid_until IS NULL AND d.deleted_at IS NULL
     AND c.deleted_at IS NULL
     AND (d.user_id IS NULL OR d.user_id = :user_id)
     AND JSON_VALUE(c.metadata, '$.section_type') <> 'references'
   ORDER BY SCORE(1) DESC
   FETCH FIRST 50 ROWS ONLY
),
-- Stage 4: reciprocal rank fusion across the two pools
fused AS (
  SELECT COALESCE(v.id, l.id) AS id,
         COALESCE(1 / (60 + v.vec_rank), 0)
       + COALESCE(1 / (60 + l.lex_rank), 0) AS rrf_score
    FROM vec_pool v
    FULL OUTER JOIN lex_pool l ON v.id = l.id
),
candidates AS (
  SELECT id, rrf_score
    FROM fused
   ORDER BY rrf_score DESC
   FETCH FIRST 40 ROWS ONLY
)
SELECT c.id, c.content, c.chunk_index, c.metadata,
       d.id AS document_id, d.title, d.source_uri, d.version,
       k.rrf_score,
       PREDICTION(BGE_RERANKER USING :query AS FIRST_INPUT,
                  d.title || '. ' || c.content AS SECOND_INPUT) AS rerank_score
  FROM candidates k
  JOIN knowledge_base_chunk c    ON c.id = k.id
  JOIN knowledge_base_document d ON d.id = c.document_id
 ORDER BY rerank_score DESC;
-- tenant_id predicate appended automatically by RLS on every table reference

Walking the clauses:

This example demonstrates the inherited user_id scope predicate inside each pool. The tenant predicate does not appear because it cannot be forgotten: RLS appends it to every table reference, in both pools and on the final join, every time. Apply corresponding agent_id or thread_id predicates when knowledge-base documents use those scopes; this shared research corpus is tenant- and user-scoped. That holds only if the policy loop from part 1 matches KNOWLEDGE_BASE% as well as %_MEMORY. Both knowledge-base tables carry tenant_id but neither name ends in _MEMORY, so an obvious loop leaves precisely the two tables this query reads without a policy, and the query has no predicate of its own to fall back on.
JSON_VALUE(c.metadata, '$.section_type') is a derived-metadata filter, here excluding bibliography chunks that match "Letta" forty times without saying anything. Part 1's functional index pattern on JSON paths makes this predicate indexable rather than a per-row parse.
VECTOR_DISTANCE with an in-database VECTOR_EMBEDDING means the query text is embedded by the engine in the statement itself, with the HNSW (Hierarchical Navigable Small World) index from part 1 (ORGANIZATION INMEMORY NEIGHBOR GRAPH) available to the optimizer. On this 137-chunk run, Oracle chose a different physical plan.
CONTAINS ... SCORE(1) is the Oracle Text half, running against the index we just created. Note that both pools repeat the same metadata predicates: each generator ranks only what the filters allow, which is what filter early means in practice. Note also that the lexical pool ranks with DENSE_RANK rather than ROW_NUMBER. Oracle Text SCORE() is a coarse integer, and on short chunks ties are the norm rather than the exception: one query against the companion notebook's corpus matched 80 chunks across ten distinct scores. ROW_NUMBER would order those tied chunks arbitrarily, and the next stage would read that arbitrary order as signal. This is the sharp edge on rank fusion generally: discarding the scores and trusting the order is what makes RRF insensitive to score scaling, and equally what makes it credulous when the order is partly noise.
The FULL OUTER JOIN plus COALESCE arithmetic is RRF, in four lines. A chunk found by both generators gets both reciprocal terms and rises; a chunk found by only one still competes. This is the fusion that part 2's query deferred: where that statement returned vec_score and lex_score side by side and left the ranking to a later stage, this is the later stage.
The candidates CTE caps the set at 40 rows before reranking, which is Stage 5's input contract.

The CTEs describe the logical stages. The optimizer still chooses the physical plan. In the companion run, the 137-chunk corpus was small enough that Oracle used the collection and chunk indexes, window sorts, and a full outer hash join without an HNSW step. A larger corpus may produce a different plan. Read the plan Oracle chose before tuning the plan you expected.

The plan below is condensed from the companion notebook's DBMS_XPLAN output:

SORT ORDER BY STOPKEY
 NESTED LOOPS
  HASH JOIN FULL OUTER (RRF fusion)
   WINDOW SORT PUSHED RANK (vector pool)
    INDEX RANGE SCAN IDX_KB_DOC_COLLECTION

And one property that's easy to miss because nothing visibly does it: the whole statement reads one transactional snapshot. The vector pool, the lexical pool, the final join: all of them see the same instant of the database. If a document is being superseded mid-query by a Promote step on another connection, this query sees it entirely old or entirely new, never a chunk from each. After the two-layer article made the case for keeping canonical and derived in sync, this is the read-side payoff: consistency you don't write code for.

The polyglot version, for contrast

The same pipeline with a dedicated vector store, Elasticsearch for lexical, and Postgres for scope looks like this:

# 1. Resolve scope: which document IDs may this user see? (Postgres)
doc_ids = pg.fetch_allowed_documents(tenant_id, user_id,
                                 collections=['ingested-papers', 'vendor-docs'])

# 2. Vector candidates (vector store), filtered to allowed docs
vec_hits = vector_store.query(vector=embed(query), top_k=50,
                              filter={'tenant_id': tenant_id,
                                      'document_id': {'$in': doc_ids}})

# 3. Lexical candidates (Elasticsearch), same filter re-expressed in its DSL
lex_hits = es.search(index='kb_chunks', size=50, query={
    'bool': {'must':   {'query_string': {'query': lex_query}},
             'filter': [{'term':  {'tenant_id': tenant_id}},
                        {'terms': {'document_id': doc_ids}}]}})

# 4. RRF, in application code
fused = rrf_merge(vec_hits, lex_hits, k=60)[:40]

Four numbered steps, three systems, and every number hides a cost. Three round trips run serially because each depends on the last, so network and service latency accumulate before the application can fuse the results. The tenant boundary is enforced three times in three different filter dialects, and each one is a place it can be forgotten; the schema articles made the case that this is the failure mode that matters most in multi-tenant SaaS. The $in filter ships potentially thousands of document IDs over the wire because the systems can't join. There's no shared snapshot, so a document superseded between step 2 and step 3 can contribute its old chunks to one pool and its new chunks to the other. And the RRF merge is now application code you own and debug at 2 AM.

Concern	Converged SQL pipeline	Three-system pipeline
Application round trips	One statement through reranking	Three serial calls before application fusion
Tenant enforcement	One database policy model	Repeated in three filter dialects
Data crossing boundaries	Candidate data stays in the database through fusion	IDs and candidates cross service boundaries
Read consistency	One transactional snapshot	No shared snapshot across systems
Fusion code	SQL in the query	Application code to build and operate
Latency and service cost	Measure the deployed database and reranker	Measure network, service, and merge costs together

None of these are exotic failures. They are the ordinary tax of putting a join across a network boundary. When retrieval can live in SQL, that tax disappears into the engine, and there's no merge layer to build or operate. Sidecars and dedicated stores can still be the right choice when hardware, latency, or existing infrastructure justifies the additional boundary.

Figure 3: One statement, one snapshot, one security model versus three systems, three filters, an application-layer merge.

Reranking: precision after candidate generation

Everything so far uses bi-encoders: the query and every chunk were embedded separately, at different times, with no knowledge of each other, and relevance was approximated by the distance between those independent points. That's what makes candidate generation cheap and scalable, and it's also the ceiling on its precision.

A cross-encoder can improve on that approximation by scoring the pair together. Query and candidate go through the model in one pass, attention flows between their tokens, and the output is a single relevance score computed with full knowledge of both texts. It can see that a chunk mentioning message eviction policy in MemGPT-style agents answers a question about memory eviction in the Letta paper (same lineage, different names), and that a chunk merely containing the word "Letta" in a citation list answers nothing. Bi-encoders can't make either judgment. This is also why cross-encoders can't generate candidates: scoring every chunk in the corpus against every query is a full scan of the most expensive kind. They only work as the late, narrow stage of a funnel that something cheaper built.

Placement and sizing follow from that. The reranker runs after fusion and before budgeting, on the fused top-N. Size N generously enough to recover from candidate-generation mistakes (the right chunk at fused rank 35 can still be rescued) and small enough to afford: The notebook uses 40. Choose a smaller or larger candidate set from measured recall and latency on the deployed workload. Reranking cannot fix candidate-set recall. If the answer isn't in the candidate set, no amount of rescoring surfaces it, which is why the generators cast a wide net and the reranker exists to clean it up.

One implementation detail earns its own paragraph, because getting it wrong is silent. Rerank the chunk with its document title prepended. Chunks are short, and a cross-encoder handed a bare sentence has nothing telling it which document that sentence came from. On the companion notebook's corpus, scoring the chunk text alone lost document context the model needed. For one tenant-isolation question, the best Multi-Tenant chunk ranked 11th; the next two landed at 21st and 24th. Prepending the title moved all three into the top three. The fix is d.title || '. ' || c.content where you would otherwise have passed c.content. Nothing in the output announces when you have this wrong, which is the first argument for the evaluation section below.

For the reference implementation, run a small open-source cross-encoder in the database as a PREDICTION expression so stages 2 through 5 remain one SQL statement and candidate text stays inside the database boundary. The companion notebook uses BAAI/bge-reranker-base because it exported and loaded successfully in the tested Oracle AI Database 26ai and OML4Py 2.1.1 environment. The notebook also demonstrates the documented DBMS_VECTOR_CHAIN.UTL_TO_RERANK interface. Treat model compatibility and size limits as release-specific.

In the latest clean 36-query evaluation, the reranked path took 2,121 ms at p50 on the tested CPU, compared with 8 ms for pure vector search: 2,113 ms of added latency. Recent standalone 40-candidate runs took about 2.9–3.1 seconds after model warmup. These measurements cover different query sets and are not latency guarantees. BAAI/bge-reranker-v2-m3 exported to a 2.2 GB artifact and failed to load in the tested environment. Report those observed errors with the environment and release instead of turning them into a permanent product limit. Hosted rerankers send candidate chunks to a third party. A sidecar can be a valid latency or hardware choice, but it moves tenant text across an additional trust boundary and needs its own isolation, access controls, and observability.

On the companion notebook's illustrative, purpose-built corpus of 23 documents and 137 chunks, with a labeled golden set built the way the evaluation section below describes, the three configurations produce the following results. The passages are paraphrases written for the notebook, so treat this as a worked retrieval experiment rather than a general benchmark:

Strategy	NDCG@10	Recall@20	p50 added latency
Pure vector top-k	0.61	0.76	—
Hybrid (RRF)	0.56	0.69	+10 ms
Hybrid + rerank	0.71	0.78	~+2.1 s

Experiment scope

23 documents and 137 chunks, or roughly six chunks per document on average
Short, paraphrased passages written for the notebook
36 labeled queries and 106 graded labels
One embedding model, one fusion policy, one reranker, and one database environment
Per-query NDCG@10 variation large enough that small metric differences should be treated as noise

Read the table columnwise. On this small, short-chunk corpus, hybrid costs more than it returns: the lexical pool adds a weak, tie-heavy signal that equal-weight RRF then uses to dilute the vector ranking. That configuration result does not establish a universal verdict on hybrid retrieval. Run the same ablation on your own labeled corpus, with the same lexical tie policy in the hybrid and hybrid-plus-rerank paths, before attributing a gain to reranking alone. Reranking cannot improve candidate recall at 40 because it only reorders the set it receives. It can improve Recall@20 by moving relevant candidates above that cutoff, and it can improve NDCG because ordering is what the context budget consumes. Two seconds is a serious cost in an interactive agent, and it is the price of keeping tenant text inside the database on CPU. If that budget does not fit, a GPU or sidecar is a deployment trade-off; either introduces an additional trust boundary that needs its own tenant controls.

Integrating the pipeline with LangChain and LangGraph

A framework doesn't write your retrieval pipeline. It gives you a place to put it. A well-defined place to put things is useful, as long as nobody mistakes the socket for the appliance.

LangChain's retriever interface is the right entry point, and the integration follows the same pattern as the OracleVS integration in the Oracle LangChain modules: a custom retriever that wraps the SQL pipeline and goes through the Memory Manager, because part 2's rule hasn't changed. The manager is the only code allowed to touch the schema, and a framework adapter doesn't get an exemption:

from langchain_core.retrievers import BaseRetriever
from langchain_core.documents import Document

class OracleHybridRetriever(BaseRetriever):
    """LangChain entry point for the SQL retrieval pipeline."""

    manager: object     # from part 2: the one door into the schema
    user_id: str                 # request scope passed to the manager
    k: int = 8                 # final results after budgeting
    candidates: int = 40       # fused candidates reranked in the SQL statement

    def _get_relevant_documents(self, query: str, *, run_manager) -> list[Document]:
        plan = self.manager.understand_query(query)                 # stage 1
        rows = self.manager.search_knowledge_base(                  # stages 2-5: one SQL statement
            plan, user_id=self.user_id, top_k=self.candidates)
        working_set, _ = budget_context(                        # stage 6
            rows, max_tokens=plan["token_budget"], diversify_by=plan["diversify_by"])
        return [Document(page_content=r["content"],
                         metadata={"title": r["title"], "source": r["source_uri"],
                                   "document_id": r["document_id"],
                                   "version": r["version"],
                                   "score": r["rerank_score"]})
                for r in working_set[: self.k]]

That class drops into anything expecting a LangChain retriever. The consuming chain does not change when the retrieval strategy changes; whether the answers improve is a result for the evaluation suite rather than an assumption built into the integration.

When the pipeline needs observable stages (and in production it does) LangGraph is the better frame. One node per stage, explicit state between nodes, and a graph you can checkpoint and instrument:

from typing import TypedDict
from langgraph.graph import StateGraph, START, END

class RetrievalState(TypedDict):
    query: str
    plan: dict          # stage 1 output: entities, expanded terms, intent, budget
    candidates: list    # stages 2-5 output: reranked, provenance-tagged rows
    working_set: list   # stage 6 output: what Infer & Act consumes

g = StateGraph(RetrievalState)
g.add_node('understand', understand_query)      # stage 1: one fast-model call
g.add_node('retrieve',   retrieve_and_rerank)   # stages 2-5: one SQL statement
g.add_node('budget',     budget_context)        # stage 6: pure function

g.add_edge(START, 'understand')
g.add_edge('understand', 'retrieve')
g.add_edge('retrieve', 'budget')
g.add_edge('budget', END)

retrieval = g.compile()

Notice the graph has three nodes for a six-stage pipeline. Stages 2 through 5 are one node because they are one SQL statement; the database already turned stages 2 through 5 into a single plan. Re-separating them in the orchestration layer would add round trips to recreate a boundary the engine deliberately erased. Oracle AI Database does the retrieval and reranking. LangGraph orchestrates the state around it.

Figure 4: The LangGraph pipeline. Three nodes, six stages, because the database collapsed stages 2 through 5.

Evaluating retrieval quality

Every number in this article came from somewhere specific, because unmeasured retrieval is unmanaged retrieval. Teams that eyeball a few answers after each change are tuning by anecdote, and anecdotes have terrible recall.

The metrics worth knowing, and why they’re important:

Recall@k measures how many truly relevant items made the top k. It is the candidate-generation metric. If recall@50 is poor, measure it first and at the fused-candidate boundary because downstream stages cannot recover missing candidates.
Precision@k measures how many of the top k were relevant. It matters most at the working-set boundary, where every irrelevant item consumes context budget.
MRR measures how high the first relevant item lands. Use it when one good hit is enough, such as an exact-fact lookup against entity memory.
NDCG@10 rewards putting the most relevant items highest, using graded rather than binary relevance, over roughly the number of results that fit a context budget. For agent memory retrieval, it captures the ordering that the budget stage consumes. If you track one metric, start with this one.

Warm the reranker before you time it, the same as you would any model server. In recent notebook runs, the first scoring call took roughly 0.9–1.1 seconds. Report that one-time model load separately from warm-path query latency. Assert the range while you are at it. NDCG is normalized to [0, 1], so a value above 1 exposes an evaluation bug, most often an ideal DCG computed from the label list instead of from every gradeable chunk in the corpus. A metric with defined bounds provides a free correctness check on the measurement, and that check is worth more than the metric on the day it fires.

The golden set is the part that sounds hard and isn't, because the schema already did the work. conversation_memory is the flight recorder: every question, retrieval, answer, and downstream signal (a follow-up, a thumbs-down, a correction) sits in the trace, scoped and timestamped. Log the candidate IDs as a retrieval_result event alongside the tool calls. Harvesting question/relevant-chunk pairs is a query against tables you already operate, followed by a labeling pass where a human (or a strong model, with spot checks) marks which retrieved chunks actually supported a good answer. Two hundred labeled questions can be a useful starting target, but the number you need depends on query variance and the regression size you need to detect. The companion notebook runs on a 36-question demonstration set, which is enough to see a large effect and not enough to split hairs. Production traffic is the test set you already have. You just have to harvest it.

Cadence matters as much as metrics. Once per release is the minimum. On every PR that touches the pipeline is better, and it's cheap once the golden set exists, because the whole evaluation is a few hundred SQL statements and a scoring script. On every embedding model change it's mandatory, and this is non-negotiable in a way the others aren't: new embeddings re-shape the entire vector space, the two-layer article made the derived-layer rebuild an afternoon's work, and an afternoon is exactly enough time to ship a model that's better on the benchmark and worse on your corpus without noticing.

LLM-as-judge deserves an accurate framing: it's a directional signal, useful for catching large regressions between releases and for triaging where to spend labeling effort, and it is not a benchmark. Judges drift with their prompts and inherit the biases of whatever model is judging. Use one to point the flashlight. Use the labeled golden set to make claims.

Beyond hybrid: where agentic retrieval comes in

Everything to this point is one-shot: a question goes in, the pipeline runs once, a working set comes out. The next evolution is agentic retrieval, where the model itself issues queries, reads intermediate results, decides what to fetch next, and stops when it has enough. This is the direction serious production systems are converging, and it reframes classical single-pass RAG as the legacy pattern: not wrong, just the special case where one round trip happens to be enough.

Look back at Jane's question and you can see why. "What did Letta say about memory eviction, and how does that compare to AgentCore" is two retrievals wearing one question mark. The one-shot pipeline handles it, with the budget stage working to keep both sources represented. An agentic retriever handles it more naturally: query for the Letta paper's eviction discussion, read the result, notice the paper frames eviction as recursive summarization, then query the AgentCore docs for that specific mechanism, with vocabulary learned from the first result. The second query is better than anything Stage 1 could have written up front, because it was informed by an actual intermediate read.

The part that matters architecturally: agentic retrieval doesn't replace the hybrid pipeline, it composes it. Every query the model issues (through the retriever tool the LangChain section just built) runs through the same six stages, with the same scope filters, RLS boundary, and reranker. The agent decides what to ask. The pipeline decides what to return, and enforces what may be returned. Getting that separation wrong, letting the model's queries bypass the pipeline and hit indexes directly, reopens every tenant-isolation hole the schema articles closed.

Cost draws the boundary for when to use it. Each retrieval round is a model call plus a pipeline run, so an N-hop agentic retrieval multiplies both latency and spend by N, and N isn't known in advance. Compositional, multi-hop, exploratory questions warrant it. "What's our refund threshold" does not, and an agent that spends four round trips on it is burning money to feel thorough. The practical pattern is an escalation policy: the intent classification from Stage 1 routes simple lookups through one pipeline pass and releases the multi-hop loop only when the question's structure demands it, under an explicit budget of rounds and tokens.

One question remains open at the end of this pipeline, and it's the next article. The working set this article produces still has to become a context window, and that assembly (what order, what framing, what gets the model's strongest attention) is a deterministic construction problem rather than another retrieval problem. There's a tempting non-answer that would skip it: context windows are a million tokens now, why not retrieve loosely and let the model sort it out? The lost-in-the-middle problem is the counterargument, and the next piece makes it fully: evaluations show that task performance can drop when relevant material sits in the middle of a long context. A larger window changes what fits, while position can still affect performance.

Where this leaves you

The schema articles established tenant-isolated memory with typed records. The two-layer piece separated canonical memory from derived context. This article shows how the agent retrieves from it: filter first, generate vector and lexical candidates, fuse and rerank them, then budget the working set.

If you take one action from this piece, run the ablation yourself. Pull real questions from your traces and hold the model and prompt fixed. Compare the current pipeline against a vector-only baseline, then test hybrid fusion and reranking separately. Measure ranking quality and answer outcomes beside p50 and p95 latency. Keep only the stages that earn their cost on your corpus.

Frequently asked questions

How do I implement hybrid search for AI agent memory?

Apply database-enforced metadata scope first, then generate vector and lexical candidate pools from the allowed rows. Fuse their ranks and optionally rerank the survivors before cutting the result to a context budget. Keep provenance on every returned chunk.

When should hybrid search be used instead of vector search alone?

Test it when queries contain identifiers, error codes, product names, or vocabulary that embeddings tend to blur. Do not assume it wins: on a small corpus with short chunks and a noisy lexical signal, vector search alone may rank better.

How do vector search, lexical search, and metadata filtering work together?

Metadata determines which rows may compete. Vector search finds semantic matches and paraphrases. Lexical search finds exact terms. Rank fusion combines the candidate lists, and reranking can improve their final order.

How do you evaluate whether hybrid retrieval improves AI agent memory?

Build a labeled set from real agent questions and hold the model and prompt fixed. Compare ablations with NDCG and recall, while tracking MRR, answer quality, and p50/p95 latency. Report corpus size and chunking details alongside candidate counts and uncertainty.

What we learned about letting agents into a production database

Anya Summers — Mon, 24 Aug 2026 10:48:04 +0000

Oracle is bringing its agent memory and Model Context Protocol work to AGNTCon + MCPCon Europe, including a workshop showing developers how to use Oracle AI Database as durable memory for AI agents, and how to let agents query enterprise data without generating their own SQL.

Someone in your business asks their agent what discount rate you should offer in APAC next quarter. It thinks for a moment and says 25%.

Would you be confident to ship that answer?

That question came up when we sat down with Jeff Smith, who leads much of our Model Context Protocol work at Oracle, to talk about what our customers are running into. We had expected a conversation about protocols and plumbing. It was mostly about trust.

Why does an agent need a database at all?

Start with the thing that makes agents hard to build. Language models are stateless. Ask one a question today and it answers as though it has never met you, because unless you send the entire history back with every request, it has no durable awareness of anything that came before.

Fine for a chat window. It falls apart the moment you want an agent that runs for weeks, remembers what it decided in March, and can explain why. Memory is what turns a stateless model into an application that survives contact with a real business.

Agents need somewhere to keep what they learn, and once that memory holds real company information it belongs where the rest of your data already lives, under the same access rules, backups and auditing. That makes it a database problem, and we have been working on those since 1979, when Oracle shipped the first commercially available SQL database.

It is why Oracle AI Agent Memory extends Oracle AI Database into a persistent memory core for agents: working memory for the task in hand, long-term factual memory, and procedural memory for how a job actually gets done. Our researchers published a paper on it in July, and teams across the company are building the connective tissue that lets agents reach it.

Which brings you back to the question we opened with. Once an agent can reach your data, how far do you trust what it does with it?

What actually goes wrong when an agent talks to a database?

The failure people brace for is the agent writing broken SQL. That is not the failure that hurts.

The one that hurts is syntactically perfect SQL returning a number that is confidently wrong. The agent goes through your schema, finds an employees table, a sales table, a partners table, works out how it thinks they relate, and comes back with an answer. The query runs clean. Nothing errors. The number is just wrong, because the agent decided “month” meant days since first purchase rather than a calendar month, and nobody catches it for a quarter.

BIRD benchmark graphic comparing best published execution accuracy with human performance.

Ask again tomorrow and it may write a different query. So you end up engineering your prompts harder and harder to coax good SQL out of a model that has no idea which of your forty tables finance actually trusts. On the BIRD benchmark, as published on 21 August 2026, the best model on the leaderboard reaches 80.04% execution accuracy against 92.96% for humans. Respectable engineering, and worth rechecking before you quote it, because leaderboard positions move. Either way, not a number you want underneath a pricing decision.

What if the agent did not write the query at all?

This is the part we keep coming back to at Oracle. Your business already solved this, years ago.

Somewhere in your organisation, there are two dozen reports your analysts use to answer exactly these questions, and behind each is a query somebody wrote, argued about, checked and eventually trusted enough to put in front of the business.

So our managed service lets you publish those reports to the agent as governed MCP tools. Each one arrives with the same contract any other tool on the protocol gets: a name, a description the agent can reason about, typed inputs it has to fill, and access control deciding who may call it at all.

Governed SQL tool design: an agent selects a trusted analyst report and supplies typed inputs.

The agent sees something like a sales trends report with clearly typed inputs for quarter, region and business unit. It picks one and fills in the inputs. It does not write a line of SQL. The query underneath is fixed, so the same question follows the same trusted query path every time. If the number moves, it is because the data moved, not because the model phrased the question differently on a Tuesday. And the path it followed is one your analysts already stand behind.

The shift sounds small but is significant in practice. The agent no longer has to be clever enough to work out the right SQL. Only clever enough to pick the right report.

We should be straight about how new this is. Not many people work this way yet and we are still learning where it breaks. Natural language to SQL works perfectly well on our stack and plenty of teams will keep using it. But reusing work your analysts already trust, rather than asking a model to reinvent it live, is worth arguing about in public.

Who is the agent, and what is it allowed to see?

The other half of every customer conversation is about security, and the industry has not been subtle about why.

In May the United States National Security Agency published seventeen pages on Model Context Protocol security, finding that adoption has outpaced the safeguards. More than forty security vulnerabilities have been filed against implementations of the protocol this year. As Vipin Samar, our senior vice president for database security, put it, agents are increasingly acting as autonomous insiders.

We treat an agent as what it is: another application that proves who it is before it gets anything. It signs in through the corporate identity system your staff already use, and it inherits that person’s permissions rather than getting a set of its own.

AI agent access controls: corporate sign-in, inherited permissions, and SQL-enforced policy.

Underneath sits the database security we have been building for more than four decades. Oracle Deep Data Security extends the access controls our customers already rely on rather than replacing them, expressing them as policies you declare in SQL instead of code you maintain. It reaches individual rows, columns and cells, so two people asking an agent the same question see only what each is cleared to see. Salaries stay hidden from those who should not see them, whether the request comes from a person or an agent acting for them.

None of that makes prompt injection go away. It does not. What it decides is how much of your data is reachable when something does go wrong, which is the question your security team will actually ask.

Four ways to connect

There is no single Oracle connector for this, because there is no single way people run Oracle. One runs locally on a developer’s machine, which is how most people meet this first. One is built into Autonomous Database with nothing to install. One is a managed service in Oracle Cloud that also reaches Oracle databases running inside Amazon, Microsoft and Google’s clouds, at no extra charge. And where data stays in a customer’s own data centre, there is a version they run themselves.

Alongside those we publish an open repository of reference connectors for the wider Oracle estate, from MySQL to a toolkit that reads your database driver logs and tells you why last night got slow.

Come and talk to us in Amsterdam

Oracle is a Gold member of the Agentic AI Foundation, the Linux Foundation body that now hosts the Model Context Protocol, and we are sponsoring AGNTCon + MCPCon Europe at the RAI in Amsterdam on 17 and 18 September.

We’re also bringing a hands-on workshop. It walks through giving an agent durable memory with Oracle AI Database underneath and connecting the agent to that memory over the Model Context Protocol. If you are building AI applications and have not yet worked out where the database fits, that session is built for you.

We will be in the Solutions Showcase for the rest of the event. Come and tell us what you are building, and we will talk through how to put a database behind it. If you think letting agents call pre-approved reports is the wrong shape, tell us that too. We are early enough in this to still be changing our minds.

Learn more: oracle.com/mcp and github.com/oracle/mcp

References

Oracle AI Agent Memory, a unified memory core for enterprise AI systems: https://blogs.oracle.com/database/introducing-oracle-ai-agent-memory-a-unified-memory-core-for-enterprise-ai-systems
Oracle Agent Memory as an Enterprise Memory Substrate for Long-Horizon AI Agents, July 2026: https://arxiv.org/abs/2607.13157
What is agent memory, a beginner’s guide for AI developers: https://blogs.oracle.com/developers/what-is-agent-memory-a-beginners-guide-for-ai-developers
MCP Server for Oracle Database, announced 16 July 2025: https://blogs.oracle.com/database/introducing-mcp-server-for-oracle-database
Autonomous AI Database MCP Server: https://www.oracle.com/autonomous-database/mcp-server/
Managed MCP servers in Oracle Cloud Infrastructure, 12 May 2026: https://docs.oracle.com/en-us/iaas/releasenotes/database-tools/mcp.htm
Running reports as agent tools: https://docs.oracle.com/en-us/iaas/database-tools/doc/database-tools-sql-reports.html
Self-managed streaming endpoint, Oracle REST Data Services 26.2: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/26.2/ordig/configuring-model-context-protocol-mcp.html
National Security Agency, Model Context Protocol security design considerations, 20 May 2026: https://media.defense.gov/2026/Jun/02/2003943289/-1/-1/0/CSI_MCP_SECURITY.PDF
BIRD benchmark leaderboard, figures as published on 21 August 2026: https://bird-bench.github.io/
Agentic AI Foundation: https://www.linuxfoundation.org/press/linux-foundation-announces-the-formation-of-the-agentic-ai-foundation
AGNTCon + MCPCon Europe: https://events.linuxfoundation.org/agntcon-mcpcon-europe/
Oracle MCP servers: https://www.oracle.com/mcp/
Oracle MCP reference connectors on GitHub: https://github.com/oracle/mcp
Oracle Deep Data Security: https://www.oracle.com/security/database-security/features/deep-data-security/ and https://blogs.oracle.com/database/oracle-deep-data-security-is-now-available-in-oracle-ai-database-26ai

How to Build a REST API for an AI Application

Anya Summers — Fri, 21 Aug 2026 13:46:30 +0000

Build a REST API for an AI application that stays reliable when inference is slow, retried, or connected to real data. Use a thin FastAPI layer to validate and authenticate requests, create a durable prediction job, return 202 Accepted with a status URL, and let a worker handle model execution outside the HTTP request.

This guide shows how to keep the public API provider-neutral, persist job state with Oracle AI Database in the production path, stream interactive responses when needed, and enforce tenant-aware access. It is grounded in maintained Oracle AI Developer Hub proof cases for SSE delivery, retrieval orchestration, and enterprise data access.

Illustrative patterns vs. maintained proof cases
The code in this guide explains the API boundaries. The maintained proof cases later in the article show those boundaries in working applications. For production, use Oracle AI Database persistence, tenant-scoped access, a separate worker or durable queue, approved identity, retention, monitoring, and distributed rate limiting. The SQL, VPD, streaming, and BackgroundTasks snippets are design guidance, not copy-and-paste deployment code.

Key takeaways

Build the REST API around a resource lifecycle: submit work, return 202 Accepted, and expose a status URL.
Validate bounded JSON with Pydantic before calling a model, and generate an OpenAPI contract from the implementation.
Keep model-provider credentials server-side and isolate provider-specific behavior behind an adapter.
Persist job state durably, scope every read to the authenticated tenant, and support idempotent retries.
Use a worker and durable queue for variable or long-running inference; use synchronous responses only when the timing is predictable.
Treat FastAPI, Oracle AI Database, ORDS, OpenAI Responses API, streaming, and queues as separate architectural boundaries with different jobs.
Test authentication, tenant isolation, input rejection, rate limiting, provider failures, worker transitions, and the public OpenAPI contract before deployment.

What will you design and verify?

You will design a small FastAPI service that accepts a prediction request, authenticates the caller, returns 202 Accepted with a Location header, processes the request outside the HTTP request, and exposes final state through GET /v1/predictions/{id}. The same service generates /docs and /openapi.json automatically.

Use the three maintained proof cases in the Developer Hub section to validate the boundary that matters to your application: interactive streaming, retrieval and orchestration, or identity-aware enterprise data access.

The rest of the article gives you the design checklist: thin routes, clear resource contracts, provider adapters, asynchronous work, streaming, identity, and governed durable state. The proof cases show how those pieces behave together in maintained applications.

What are the best practices for building a REST API for an AI application?

Use a clear resource contract, validate inputs before model calls, keep provider credentials server-side, choose synchronous or asynchronous execution deliberately, persist job state, scope reads to authenticated tenants, and expose OpenAPI plus safe errors. Test the failure path as carefully as the successful model response.

The recommended implementation applies those practices through five boundaries:

HTTP boundary: FastAPI, Pydantic, OpenAPI, authentication, rate limiting, and request IDs.
Job boundary: POST /v1/predictions creates a durable resource and returns 202 Accepted.
Worker boundary: a separate process claims work and calls a model adapter.
Data boundary: Oracle AI Database stores the request lifecycle and applies tenant scope.
Provider boundary: the model integration is replaceable and must not expose provider credentials to clients.

How do you design a thin REST API layer for an AI app?

Keep the REST API thin: authenticate and validate at the HTTP edge, create or read a job resource, and delegate model calls, retrieval, and durable state to dedicated boundaries. The route should not contain provider-specific logic, long-running inference, or authorization rules that the data layer must enforce again.

Treat inference as a resource lifecycle rather than a single long-running HTTP function. The API accepts and records work, the worker performs inference, and the client reads the resulting resource using a stable status URL. In the production path, FastAPI owns the application-facing boundary and Oracle AI Database owns durable prediction-job state.

Which endpoints should a chatbot REST API expose in FastAPI?

A chatbot REST API should separate conversation resources, messages, prediction jobs, and optional streams. This keeps chat history addressable, makes slow inference observable, and lets authorization apply to each resource instead of hiding the entire lifecycle behind one /chat request.

For a chatbot-style REST API, expose endpoints that map cleanly to the same resource lifecycle:

POST /v1/conversations/{conversation_id}/messages — create a message and schedule the underlying prediction job.
GET /v1/conversations/{conversation_id} — return conversation metadata and current state.
GET /v1/conversations/{conversation_id}/messages — return authorized message history.
GET /v1/predictions/{id} — poll the durable job until the model response is ready.
GET /v1/conversations/{conversation_id}/messages/{message_id}/stream — optionally stream incremental output with Server-Sent Events (SSE).
DELETE /v1/conversations/{conversation_id} — delete conversation data according to the retention policy.

The client-facing flow stays consistent: FastAPI authenticates and creates work, a worker updates durable job state in Oracle AI Database, and the client reads the response through a status URL or an authorized stream.

The pattern is useful when model inference may outlive the request timeout, when a client needs reliable status information, or when an AI application must retain request context beside governed business data.

Why Oracle AI Database for the production path?

Oracle AI Database gives the production path one place to keep durable prediction-job state beside governed application data. It can scope work to a tenant, support reliable status reads and idempotency records, and apply data-access controls close to the rows that the API exposes.

Proof cases: apply the API pattern to a real AI developer job

The REST API pattern is useful only when it connects to work a developer actually has to ship. These three Oracle AI Developer Hub assets are not substitutes for this tutorial; each is a working proof case for one boundary in the design.

Job: ship an interactive AI experience without turning the route into the agent

Use Total Recall when you need to move from a POST /v1/chat prototype to an interactive application that streams progress while keeping the agent harness outside the browser. It is a FastAPI backend and browser app that streams over SSE, then exposes the surrounding concerns—context, retrieval, memory, tools, and traces—as separate layers.

What this proves for an API design: streaming is a delivery mechanism at the HTTP boundary; it does not eliminate the need for a stable resource model, server-side provider credentials, or durable state. Use this proof case when the product requirement is “show useful progress now” rather than “return one blocking response later.”

Run it now: prepare Python 3.11+, a reachable Oracle AI Database with its required embedding model, and an approved model-provider credential; then follow the appbook setup and run ./run.sh. Its documented success signal is a local browser application with the FastAPI service available and status indicators for the database, harness, reranker fallback or availability, and configured model provider. Use the live trace and streamed chat to check that your route delivers incremental output without moving the harness into the client.

Job: make a retrieval-backed AI API answer from the right evidence

Use the From RAG to Agents workshop when the API must do more than call a model. The workshop builds a research-paper assistant from data loading and retrieval through RAG, agent tools, multi-agent orchestration, and persistent session memory.

What this proves for an API design: retrieval and orchestration belong behind a provider-neutral service boundary. Keep POST /v1/predictions or POST /v1/chat responsible for validation, identity, status, and response shape; let a worker or agent runtime assemble retrieval, tool calls, and model execution. Use this proof case when the developer job is “turn grounded retrieval into an application feature without exposing the internal pipeline as the public API.”

Run it now: install Docker, Python, and Jupyter; start the workshop’s Oracle AI Database container, install its requirements, and open workshop/notebook_student.ipynb. The learning path moves from data loading and retrieval through RAG, tools, orchestration, and session memory. The proof is not only that a model answers—it is that you can inspect which retrieval and orchestration boundary produced the answer.

Job: expose enterprise data without bypassing identity and access policy

Use the Enterprise Data Agent workshop when the API serves people who should see different data. It pairs a notebook with a running application and demonstrates identity-aware row and column policies, retrieval, memory, tools, and a live chat interface against the same database.

What this proves for an API design: authentication at the route is necessary but not sufficient. Derive the caller identity in FastAPI, carry that identity into the database session through an approved pattern, and make retrieval, conversation reads, and tool access apply the same scope. Use this proof case when the developer job is “add an AI capability to enterprise data without creating a second, ungoverned access path.”

Run it now: use the workshop’s GitHub Codespaces path for the lowest-friction setup, or follow its local Docker, Python, and Node.js setup to start the notebook and companion application. The proof to look for is identity-dependent data access: the same application and agent workflow should be constrained by the active identity and the data policy, rather than trusting an unscoped identifier supplied in a chat request.

Evidence boundary: These links are pinned to a specific Developer Hub commit so their code and setup instructions remain reviewable. They demonstrate an API boundary in a working application: Total Recall demonstrates FastAPI and SSE; From RAG to Agents demonstrates retrieval and orchestration; Enterprise Data Agent demonstrates identity-aware access. They do not benchmark the latency, throughput, availability, or security of your deployment. Measure those against your model, data, identity provider, and traffic before release.

How do you keep your public API independent of the AI provider?

Define a provider-neutral request, result, and error contract at the application boundary. Put each model SDK behind an adapter that translates the internal request into the provider call and maps the result back into your API’s stable schema. Clients should not receive provider response objects, provider-specific error payloads, or credentials.

from typing import Protocol

class ModelResult(BaseModel):
    text: str
    provider_request_id: str | None = None

class ModelAdapter(Protocol):
    def generate(self, *, prompt: str, temperature: float) -> ModelResult: ...

Select the adapter through server-side configuration. This lets the HTTP contract, job schema, authorization, and tests remain stable when the provider or model changes.

How do FastAPI `/docs` and `/openapi.json` help during development?

FastAPI generates an interactive /docs page and a machine-readable /openapi.json contract from the request and response models. Use them to try authenticated development requests, inspect validation rules, share an exact contract with frontend or platform teams, generate clients, and add contract tests. They improve developer feedback; they do not replace authentication, authorization, rate limits, or gateway controls.

Before deployment, confirm that /openapi.json exposes only intentional fields and responses. Decide whether /docs should remain available outside development, and protect it according to the deployment’s operational policy.

Should AI inference be synchronous or asynchronous?

Use a synchronous response only when completion time is predictable and fits the API contract. Use an asynchronous job resource when inference can take an unpredictable amount of time, requires retries, or must be audited independently of the HTTP request.

An AI response may not be ready within a predictable HTTP deadline. A job resource lets the API acknowledge valid work quickly, prevents a request thread from waiting on inference, and gives the client a stable resource to poll.

Use these resources:

Endpoint	Purpose
`POST /v1/predictions`	Validate a prompt and submit a prediction job. Return `202 Accepted`.
`GET /v1/predictions/{id}`	Read the job’s queued, running, succeeded, or failed state.
`GET /healthz`	Let platform health checks confirm that the API process is available.

Do not make a URL such as /generateText the primary interface. The prediction is a resource with a lifecycle; the API should make that lifecycle visible.

client → FastAPI → prediction_jobs in Oracle AI Database
                  ↓
             durable worker → approved model adapter
                  ↓
client ← GET /v1/predictions/{id}

An in-process background task can be useful for a local prototype. For production, run a separate worker process and use a durable queue or equivalent platform service so accepted work can survive a web-process restart.

How do I add streaming responses for chat in FastAPI?

Use Server-Sent Events when the client benefits from incremental response content. Streaming is an output-delivery choice, not a replacement for durable job state: persist the authoritative result and expose a status resource even when the user interface receives partial content.

import asyncio
import json

from fastapi import Request
from fastapi.responses import StreamingResponse

@app.get("/v1/conversations/{conversation_id}/messages/{message_id}/stream")
async def stream_message(request: Request, conversation_id: str, message_id: str):
    async def events():
        try:
            async for chunk in worker.stream(message_id):
                if await request.is_disconnected():
                    return
                yield "event: message\\ndata: " + json.dumps({"delta": chunk}) + "\\n\\n"
            yield "event: done\\ndata: {}\\n\\n"
        except asyncio.CancelledError:
            return
        except Exception:
            yield 'event: error\\ndata: {"code":"stream_failed"}\\n\\n'

    return StreamingResponse(events(), media_type="text/event-stream")

Define event types and payload shapes as part of the public contract. Serialize each chunk as JSON rather than interpolating raw model output into an SSE frame; then check client disconnects, handle cancellation, keep provider errors server-side, and finalize the durable result for later retrieval.

How do I build an AI REST API with FastAPI?

Define bounded Pydantic request and response models, reject unknown fields, use FastAPI dependencies for authentication, return an explicit status code, and let FastAPI generate the OpenAPI contract from the implementation.

The next two snippets show the request model and submission boundary. Treat them as patterns to adapt to your identity provider, job store, and worker runtime; the maintained proof cases show the surrounding application behavior.

Step 1: Define a bounded request schema

Use Pydantic to reject unknown fields and bound input before the request reaches an expensive model call.

class PredictionRequest(BaseModel):
    model_config = ConfigDict(extra="forbid")

    prompt: Annotated[str, Field(min_length=5, max_length=8_000)]
    temperature: Annotated[float, Field(default=0.2, ge=0.0, le=1.0)]

The limits are application decisions, not universal defaults. Set them from the model context window, your abuse policy, and your expected payload shape.

Step 2: Submit work and return `202 Accepted`

The endpoint creates a prediction job before it schedules work. It returns a Location header and a JSON status URL.

from fastapi import BackgroundTasks, Depends, Header, Request, status
from fastapi.responses import JSONResponse

@app.post("/v1/predictions")
async def create_prediction(
    payload: PredictionRequest,
    background_tasks: BackgroundTasks,
    request: Request,
    identity: VerifiedIdentity = Depends(require_identity),
    idempotency_key: str | None = Header(default=None, alias="Idempotency-Key"),
):
    job_id = str(uuid4())
    status_url = f"/v1/predictions/{job_id}"
    repository.create(
        job_id=job_id,
        tenant_id=identity.tenant_id,
        client_id=identity.user_id,
        prompt=payload.prompt,
        temperature=payload.temperature,
        request_id=request.state.request_id,
        idempotency_key=idempotency_key,
    )
    background_tasks.add_task(worker.process_once)
    return JSONResponse(
        status_code=status.HTTP_202_ACCEPTED,
        headers={"Location": status_url},
        content={"id": job_id, "status": "queued", "status_url": status_url},
    )

Use 202 Accepted when the API has accepted work but has not completed it. Do not choose a fixed time threshold such as two seconds; use a job resource whenever the result cannot be completed reliably within the request contract.

This BackgroundTasks example is illustrative, not a production runtime pattern. For work that is heavy, retried, or must survive a process restart, use a separate worker and durable queue; FastAPI makes the same distinction in its background-task guidance.

How would I persist AI jobs with Oracle AI Database in production?

Store the job request, tenant scope, lifecycle status, safe result or error code, request correlation ID, and idempotency fingerprint in Oracle AI Database. Keep transactions short and use bind variables through a connection pool.

Step 3: Persist prediction status in Oracle AI Database

The service records the request, client scope, status, result, safe error code, and request ID in a prediction_jobs table. This keeps API state close to the application data that may shape the model request or govern who can retrieve the result.

CREATE TABLE prediction_jobs (
    id             VARCHAR2(36) PRIMARY KEY,
    tenant_id      VARCHAR2(255) NOT NULL,
    client_id      VARCHAR2(255) NOT NULL,
    status         VARCHAR2(16) NOT NULL,
    prompt         CLOB NOT NULL,
    temperature    NUMBER(3,2) NOT NULL,
    generated_text CLOB,
    error_code     VARCHAR2(128),
    request_id     VARCHAR2(64) NOT NULL,
    idempotency_key VARCHAR2(255),
    request_fingerprint VARCHAR2(64),
    created_at     TIMESTAMP WITH TIME ZONE NOT NULL,
    updated_at     TIMESTAMP WITH TIME ZONE NOT NULL,
    CONSTRAINT prediction_jobs_idempotency_uq
      UNIQUE (tenant_id, idempotency_key)
);

Use bind variables for database calls. A production implementation should use oracledb.create_pool, then acquire a connection only for the short transaction that creates, claims, or completes a job.

Step 4: Let a worker handle model inference

The worker claims one queued job, calls a model adapter, and records either the result or a safe error code.

def process_once(self) -> bool:
    job = self._repository.claim_next()
    if not job:
        return False
    try:
        result = self._model.generate(prompt=job.prompt, temperature=job.temperature)
        self._repository.mark_succeeded(job.id, result)
    except Exception:
        self._repository.mark_failed(job.id, "model_inference_failed")
    return True

Keep raw provider errors out of public responses. Log only redacted, correlated diagnostics, then use request_id to investigate the failure internally.

How do I build a minimal FastAPI chat endpoint using the OpenAI SDK?

For a minimal, synchronous prototype, validate the message, call the OpenAI SDK from the server, and return your own stable JSON shape. Keep this route small: it demonstrates the request boundary, not a durable production job system.

import os
from fastapi import FastAPI
from openai import AsyncOpenAI
from pydantic import BaseModel, Field

app = FastAPI()
client = AsyncOpenAI(api_key=os.environ["OPENAI_API_KEY"])

class ChatRequest(BaseModel):
    message: str = Field(min_length=1, max_length=8_000)

@app.post("/v1/chat")
async def create_chat(payload: ChatRequest):
    response = await client.responses.create(
        model=os.environ["OPENAI_MODEL"],
        input=payload.message,
    )
    return {"output_text": response.output_text}

For variable, costly, or auditable inference, replace the direct SDK call with the 202 prediction-job pattern described above. Add the authenticated Depends pattern in the security section before using this route outside a local prototype. Do not return the provider’s raw response object or expose provider credentials to the client.

How do I connect a REST API to the OpenAI Responses API?

Keep the model provider behind a server-side adapter. Load OPENAI_API_KEY from a secret manager or environment, set OPENAI_MODEL explicitly, call the Responses API from the worker, capture the provider request ID for diagnostics, and return only your API’s stable response contract.

How should you store and load the OpenAI API key securely?

Store the OpenAI API key in an approved secret manager and inject it into the server or worker at runtime. Environment variables are suitable as the process-level delivery mechanism, but the value should originate from managed secret storage rather than source code, a Docker image, a notebook, browser JavaScript, or a committed .env file.

Use a different secret for each environment and workload identity.
Restrict who and what can read the secret.
Fail startup when the required secret is missing instead of accepting it from a client request.
Do not print the key in logs, traces, exceptions, health responses, or configuration dumps.
Rotate the key according to the organization’s credential policy and after suspected exposure.

Load the value server-side and pass it directly to the provider client:

import os
from openai import OpenAI

api_key = os.environ["OPENAI_API_KEY"]
openai_client = OpenAI(api_key=api_key)

This is an illustrative provider boundary, not a downloadable implementation or configuration recipe. The current OpenAI API reference describes Responses as the direct model-request API, requires server-side bearer credentials, and recommends logging request IDs and reviewing rate limits before production.

Do not put the provider key in browser JavaScript or accept it from the request body. Return a stable internal error instead of a raw provider error. The API’s caller authentication and the model provider’s authentication are separate trust boundaries.

How do I authenticate and secure an AI REST API?

Authenticate before expensive work, resolve tenant identity from verified credentials, apply authorization to every job read and update, enforce request limits, bound payloads, support idempotent retries, redact provider errors, and define retention for prompts and generated responses.

How do you store conversations and enforce user ownership checks?

Store each conversation with a server-derived tenant_id and owner_id, then carry the conversation identifier into its messages and prediction jobs. Every read, update, stream, and delete operation must include the authenticated ownership scope; knowing a conversation ID is not authorization.

SELECT conversation_id, status, updated_at
FROM ai_conversations
WHERE conversation_id = :conversation_id
  AND tenant_id = :authenticated_tenant_id
  AND owner_id = :authenticated_user_id;

Apply the same scope when loading message history or returning generated content. For defense in depth, enforce the tenant predicate in Oracle AI Database with an approved policy such as VPD, and test that one user cannot read, stream, update, or delete another user’s conversation.

For chatbot-style endpoints, store conversations and messages with an owner or tenant identifier and enforce that ownership on every endpoint that returns context, history, or generated content. Also account for prompt injection, unsafe generated output, unintended tool calls, sensitive-data disclosure, and cost exhaustion. Mitigate these risks with strict schema validation, tool allowlists, least-privilege data access, timeouts, token budgets, redaction, and human approval for consequential actions.

How do I add user authentication with FastAPI `Depends`?

Inject a verified identity object before creating an inference job or calling a provider. Derive tenant_id and user_id from a validated JWT, OAuth session, or equivalent credential; do not accept them as authoritative request-body fields.

from fastapi import Depends, HTTPException, status
from pydantic import BaseModel

class VerifiedIdentity(BaseModel):
    tenant_id: str
    user_id: str

async def require_identity() -> VerifiedIdentity:
    identity = await verify_request_credentials()
    if identity is None:
        raise HTTPException(status_code=status.HTTP_401_UNAUTHORIZED)
    return identity

@app.post("/v1/chat")
async def chat(request: ChatRequest,
               identity: VerifiedIdentity = Depends(require_identity)):
    # Scope reads and writes with identity.tenant_id and identity.user_id.
    return await create_scoped_job(request, identity)

This dependency is illustrative. Replace verify_request_credentials() with the approved authentication mechanism for the deployment and enforce the same identity in the database policy.

Authentication and authorization: Require authenticated callers and scope every job to a tenant or user. An opaque prediction ID is not authorization.
Rate limiting: A process-local guard is suitable only for a demonstration. Enforce distributed limits with an API gateway, Redis-backed service, or equivalent platform control in multi-instance deployments.
Input controls: Bound prompt length, reject unknown JSON fields, and validate media uploads separately from text requests.
Idempotency: Add an idempotency key for clients that may retry POST after network failures.
Observability: Return a request ID, record the job ID and tenant-safe correlation data, and measure queue delay, inference duration, errors, and abandonment.
Retention: Define when prompts and generated responses expire or must be deleted; do not retain them indefinitely by default.

Oracle AI Database pattern: tenant scope and request correlation

The API must authenticate the caller before it opens a database session. The following is simplified Oracle pseudocode, based on application contexts and Oracle Virtual Private Database (VPD) and DBMS_APPLICATION_INFO. It is not a copy-and-paste production policy: configure a trusted context-setting package and review the VPD licensing and security requirements for the target deployment. DBMS_RLS, which implements VPD, is available with Oracle AI Database Enterprise Edition only; confirm the edition and deployment controls before adopting this pattern.

-- The API derives these values from authenticated identity; do not accept a
-- tenant ID or client identity as an untrusted request-body field.
BEGIN
  trusted_ai_api_context.set_identity(
    tenant_id  => :authenticated_tenant_id,
    client_id  => :authenticated_client_id
  );

  -- Safe correlation only: do not place prompts, tokens, or secrets here.
  DBMS_APPLICATION_INFO.SET_MODULE(
    module_name => 'ai_predictions_api',
    action_name => 'create_prediction'
  );
  DBMS_APPLICATION_INFO.SET_CLIENT_INFO(:request_id);
END;
/

-- A VPD policy function can add this predicate whenever PREDICTION_JOBS is
-- queried or changed. The application query stays simple; the database
-- enforces the current tenant scope.
CREATE OR REPLACE FUNCTION prediction_tenant_predicate(
  object_schema VARCHAR2,
  object_name   VARCHAR2
) RETURN VARCHAR2 AS
BEGIN
  RETURN q'[tenant_id = SYS_CONTEXT('AI_API_CTX', 'TENANT_ID')]';
END;
/

BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema   => 'APP_SCHEMA', -- replace with the schema that owns PREDICTION_JOBS
    object_name     => 'PREDICTION_JOBS',
    policy_name     => 'PREDICTION_TENANT_SCOPE',
    policy_function => 'PREDICTION_TENANT_PREDICATE',
    statement_types => 'SELECT,INSERT,UPDATE,DELETE',
    update_check    => TRUE
  );
END;
/

-- Idempotency belongs in durable state, not only in the API process.
-- The PREDICTION_JOBS table definition above already enforces it with
-- UNIQUE (tenant_id, idempotency_key); do not add the constraint twice.

Use DBMS_APPLICATION_INFO to correlate a safe request ID with database work; its CLIENT_INFO field is not a location for secret or sensitive prompt data. For systems that use VPD, the policy function is attached to the protected table and Oracle applies the predicate to the configured statement types. Treat this entire SQL section as illustrative architecture guidance and have it reviewed for the target edition, schema design, session-pooling behavior, and identity model.

When should I use FastAPI, ORDS, streaming, or a queue?

Use FastAPI for application-facing validation and model orchestration, ORDS for a governed database-owned resource, SSE for incremental interactive output, and a durable queue for production worker delivery. These choices can coexist, but they solve different boundaries.

Need	Best-fit boundary	Example or resource
Short predictable inference	Synchronous REST response	Direct route with bounded input and timeout
Long-running inference	`202` job resource plus worker	Job-resource and worker pattern described above
Token-by-token interaction	SSE or another streaming protocol	Total Recall appbook, as an advanced path
Database-owned status resource	ORDS	Oracle REST Data Services documentation
Retrieval-backed application	Separate retrieval API	From RAG to Agents workshop

ORDS is useful when a database-owned resource needs a governed REST interface. It does not need to replace FastAPI as the public API boundary for model orchestration. In this pattern, FastAPI owns request validation and model lifecycle; ORDS can provide a narrowly scoped, read-only integration for job-status data when that is useful to an existing database-facing application.

How do I test and deploy an AI REST API?

Test the public contract, authentication, tenant isolation, idempotency, rate limiting, worker transitions, provider failures, and OpenAPI output before deployment. Run the worker separately in production and configure provider and database credentials outside source control.

Start with the existing Total Recall appbook for the FastAPI/SSE boundary, then use the From RAG to Agents workshop for database-backed orchestration patterns.
Implement the resource contract with bounded Pydantic schemas and an authenticated identity dependency.
Add durable job state, tenant scope, idempotency, and a worker before exposing a provider call.
Add Oracle AI Database persistence and connection pooling through an approved repository implementation.
Run contract, authentication, tenant-isolation, streaming, worker, and provider-adapter tests before deployment.

The OpenAPI contract should be available at /openapi.json. Use it to generate client SDKs, run contract tests, and review request/response schemas during integration. During development, FastAPI’s /docs page can help inspect endpoints and validated models, but neither endpoint is a security control: authorization, tenant scoping, and rate limiting must still be enforced in API logic and gateway policies.

Troubleshooting an AI REST API

401 unauthorized: Verify that the API derives identity from approved credentials and that the client presents the required credential.
422 Unprocessable Entity: Check the prompt length, temperature range, JSON field names, and Content-Type: application/json header.
409 idempotency_conflict: Reuse an Idempotency-Key only for the same logical request body; generate a new key for changed work.
404 not_found while polling: Scope the status lookup to the same authenticated tenant that created the job.
Work disappears after a restart: Move accepted work into durable state and process it through a separate worker or queue.
/docs loads but requests fail: OpenAPI describes the contract; it does not supply authentication or configuration. Inspect the request credential and JSON error code.

Production checklist

Validate and reject unknown request fields.
Authenticate before creating a job or calling a provider.
Scope every status read by authenticated tenant identity.
Support Idempotency-Key for safe client retries.
Use a durable worker and queue for production inference.
Record request, queue, inference, and provider correlation IDs without logging prompts or secrets.
Set OPENAI_MODEL or the equivalent provider model explicitly.
Define retention and deletion behavior for prompts and outputs.
Run contract and failure-path tests before publishing.

FAQ

How do I build a REST API for an AI application?

Use FastAPI to validate and authenticate requests, persist an inference job, return 202 Accepted with a status URL, and let a worker call the model provider. Store job state in a durable database such as Oracle AI Database, scope reads to the authenticated tenant, and return stable JSON status and error responses.

What are the best practices for an AI REST API?

The core practices are bounded input validation, server-side provider authentication, rate limiting, durable asynchronous jobs, tenant-aware authorization, idempotency, structured errors, request correlation, retention controls, and tests for failure paths—not only successful inference.

How do I authenticate an OpenAI-powered REST API?

Authenticate the API caller at your service boundary, then authenticate separately with the model provider from the server or worker. Keep the provider key in a secret manager or protected environment variable, set the model explicitly, capture a safe provider request ID, and do not send the key or raw provider error to the client.

Should I use FastAPI or ORDS for an AI application API?

Use FastAPI for application-facing validation, authentication, orchestration, and model lifecycle management. Use ORDS when a database-owned resource needs a governed REST interface. They can coexist; ORDS does not need to replace FastAPI as the public model-orchestration boundary.

Should an AI REST API return a result synchronously?

It can for predictable, short operations. Use a job resource and 202 Accepted when an operation may not finish reliably in the request window or when you need durable retry, audit, and status behavior.

Is FastAPI enough for a production AI application?

FastAPI is a useful HTTP framework, but it is only one layer. Production behavior also needs durable persistence, a worker or queue, authentication, limits, observability, and retention controls.

Which maintained proof case should I run for an AI REST API?

Run Total Recall for FastAPI and SSE, the From RAG to Agents workshop for retrieval and orchestration, or the Enterprise Data Agent workshop for identity-aware, governed data access. Choose the proof case that matches the boundary you need to validate first.

How does Oracle AI Database help with an AI API?

It can persist job state and application data in the same governed data platform. The application API still validates and orchestrates requests; Oracle AI Database provides the durable state and data-access layer behind it.

Next steps

Choose one maintained proof case, then apply the API contract in this guide:

Total Recall for FastAPI, SSE, traces, and the agent harness boundary.
From RAG to Agents for retrieval, tools, orchestration, and session memory.
Enterprise Data Agent for identity-aware, governed access to enterprise data.

Documentation

How to Build a Controlled MCP Workflow for Codex and Oracle AI Database

Anya Summers — Wed, 12 Aug 2026 15:28:59 +0000

Connect Codex CLI to Oracle AI Database through SQLcl MCP, then Add Oracle AI agent memory and LangChain Retrieval.

Companion notebook: Codex MCP Oracle AI Database

Key Takeaways

MCP turns AI-to-database access into an explicit tool contract instead of implicit system access. 
Oracle SQLcl in MCP mode (sql -mcp) is a direct, documented way to connect Codex CLI to Oracle AI Database through an MCP server. 
Oracle AI Database provides the persistent storage and vector search layer for memory workloads, while Oracle AI Agent Memory gives teams a Python API for threads, durable memory records, scoped retrieval, and context assembly on top of it.
LangChain can provide application-side wrappers and orchestration after the Oracle-backed memory and retrieval path is in place.
A strong default is hybrid: Codex CLI plus MCP for interactive database work, Oracle AI Database plus Oracle AI Agent Memory for durable memory, and LangChain only when the application needs reusable retrieval orchestration.

Here is how those components connect in this pattern. Codex works with Oracle through SQLcl MCP’s explicit tools. The saved SQLcl profile, database user, grants, and database policies remain the enforcement boundary. Oracle AI Agent Memory is the Python package your application uses to manage memory records on Oracle AI Database. LangChain is optional application-side orchestration after the database-backed memory and evidence path is in place; it is not the permission boundary or source of truth.

Production success depends less on clever prompting and more on boundaries, privileges, logging, scoped retrieval, and repeatable runbooks.

This guide is for developers who want Codex CLI to work with Oracle AI Database through explicit tools, durable memory, and reviewable retrieval evidence. It also shows an AI agent memory architecture where Codex handles the developer interaction, SQLcl MCP provides the tool boundary for database access; the saved SQLcl profile and database user permissions determine what Codex can actually do, and Oracle AI Database stores durable memory and retrieval evidence.

The developer path through this guide is simple:

Start with one approved Oracle connection and a read-only validation query. 
Put SQLcl MCP in front of that connection so Codex sees tools, not raw database credentials. 
Check the audit and activity trail before adding more tool access. 
Add Oracle AI Agent Memory when the workflow needs durable thread context, scoped recall, or reusable context cards. 
Add LangChain only when you need application-side retrieval orchestration beyond the MCP interaction loop.

Controlled Codex MCP + Oracle AI Database Workflow

Why This Architecture Is Useful for Developers

Giving an AI assistant database access is easy. But making that access controlled, inspectable, and repeatable is the hard part.

Codex is useful because it sits close to the developer’s actual work: files, commands, notebooks, configuration, and implementation details. That closeness becomes more sensitive when the workflow touches a database.

The question is not whether Codex can produce SQL-shaped text. The question is whether the database path is approved, observable, and easy to debug later. A useful workflow needs to preserve the request, the tool call, the database identity, the retrieved context, and the reason a risky action was allowed or blocked.

By the end of this guide, you should know how to connect Codex CLI to Oracle AI Database through a controlled MCP boundary, when Codex context is enough and when Oracle-backed memory is needed, and how to build a retrieval path that can be queried, audited, and scaled.

The workflow has four layers. Codex CLI is the developer-facing agent interface. SQLcl MCP is the tool boundary. Oracle AI Database is the durable substrate for memory, traces, and retrieval. The notebook is the build-and-validation harness that proves the pieces are wired correctly before the workflow is handed to Codex.

That separation matters. Codex does not need raw credentials in its prompt. SQLcl MCP can be configured once using the saved alias. Oracle AI Database owns persistence and retrieval. The notebook shows the setup in a way that can be rerun, inspected, and debugged.

A simple way to divide the responsibilities:

Layer	Responsibility
SQLcl MCP	Exposes declared Oracle tools to Codex; it is the tool boundary.
Oracle AI Database	Stores durable data, retrieval evidence, vectors, metadata, traces, and enforces database privileges.
Oracle AI Agent Memory	Provides application APIs for users, agents, threads, durable memories, scoped retrieval, and context assembly.
LangChain	Wraps Oracle-backed retrieval results as `Document` objects and supports application-side orchestration.

The Two Execution Loops

Building on the separation of responsibilities above, the system naturally forms two execution loops:

Loop A is the operational interaction loop: Codex CLI works with MCP to discover tools, inspect data, run bounded read-only queries, and return results immediately.
Loop B is the durable memory and retrieval loop: application code writes history, tool logs, memory records, chunks, and embeddings to Oracle AI Database, then retrieves context before a later answer or workflow step.

Dual Execution Loop: MCP Interaction and Durable Memory 

SQLcl MCP is for Codex operating interactively through declared tools. Oracle AI Agent Memory is for application code that stores turns, retrieves history, and assembles context before Codex or another assistant sees a prompt. You can drop either one for a narrower use case, but most production setups need both loops.

Reproducing the SQLcl MCP and Codex CLI Workflow

The setup should be reproducible. SQLcl runs in MCP mode with sql -mcp. Codex CLI launches it as an MCP server and talks to Oracle through declared tools, not through direct access. Connections come from saved SQLcl profiles that you create and test before Codex uses them.

Codex does not invent them at runtime; it reuses profiles you have already created and validated.

Prerequisites before you connect Codex CLI:

Oracle SQLcl 25.2.0 or higher. 
Oracle JRE 17 or 21. 
Codex CLI or another MCP-capable client you are explicitly configuring and testing. 
At least one saved SQLcl connection profile under ~/.dbtools, created with password persistence for MCP use. 
A database user with the minimum permissions required for the workflow. Start with read-only access and a sanitized development or replica environment where possible.

The notebook then generates a sanitized Codex MCP config preview. The preview is intentionally safe: it shows the server command and arguments without exposing secrets.

For the saved connection itself, the important detail is -savepwd. MCP cannot stop and ask a human for the password every time Codex invokes a database tool. The saved alias becomes the repeatable local path Codex can use after you have reviewed it.



```toml 
[mcp_servers.sqlcl]
command = "/absolute/path/to/sql"
args = ["-mcp"]
```

That small TOML block defines the connection between Codex and SQLcl MCP Server. Codex interacts with the database through the tools and permissions exposed by the MCP server, using the saved SQLcl connection profile you created and tested first. Save this configuration in ~/.codex/config.toml, restart Codex, then run codex mcp list to confirm that the SQLcl MCP server and its tools are discoverable.

Validation checklist before expanding access

Run sql -mcp locally and confirm the server starts. 
Restart Codex CLI and confirm the SQLcl tools are discoverable. 
Run one read-only query against an approved schema. 
Check database-side MCP activity logs and session metadata. 
Document the connection alias, database user, grant scope, restrict level, and troubleshooting owner.

Good first proof looks like this:

The MCP server starts without a Java or path error. 
Codex lists the SQLcl MCP tools after restart. 
A read-only query succeeds against the expected schema. 
The notebook audit trail records the expected tool interaction in codex_tool_logs.
For live Codex CLI + SQLcl MCP validation, confirm the database/session activity through your normal Oracle monitoring path.
A denied query fails because of the database role, not because a prompt asked nicely.

A useful MCP boundary is more than tool discovery. It is an operating contract. The notebook models that contract with risk levels, read-only defaults, confirmation requirements, scope checks, and controlled failure examples. Denied and warning states are useful because they prove the workflow can say no.

Read-only default: start with inspection and diagnostics before allowing changes.
Confirmation gate: require explicit approval for medium-risk, write-like, or destructive actions.
Scope control: keep user, tenant, and schema filters close to the database query.
Failure trace: store denied calls and warnings as evidence instead of hiding them.

Why put application memory records in Oracle AI Database, not just outputs 

Once the first MCP tool calls work, the next challenge is continuity. This is where long-term memory for AI agents becomes different from short-lived chat context.

If memory lives only in chat context, the system is fragile. If memory is scattered across files without structure, retrieval and auditing become expensive over time.

Memory Categories That Matter in Practice:

You do not need to start by memorizing table names or column names. The useful idea is simpler: different kinds of memory serve different jobs.

Conversation memory: Keeps the important user and assistant turns that future sessions may need.
Operational memory: Keeps tool calls, outcomes, warnings, and failures so a team can debug what happened.
Semantic memory: Adds embeddings so the system can find relevant context even when the user asks in different words.

In practice, hybrid retrieval for agent memory usually combines exact operational terms, such as sql -mcp or codex_mcp, with semantic search over memory records.

The notebook shows the lower-level mechanics first so the storage and retrieval path is visible. This is also a context engineering problem: the application has to decide which memories, tool traces, and retrieval results should be assembled before Codex or another assistant answers. Oracle AI Agent Memory then gives application code a cleaner package API over that same database-backed idea.

Where Oracle AI Agent Memory Fits

Oracle AI Agent Memory sits between your application code and Oracle AI Database. The package manages conversation threads, durable memory records, scoped retrieval, and context assembly. Oracle AI Database remains the storage and enforcement layer underneath; the package does not replace that, it makes working with it faster and more consistent.

The notebook includes an abbreviated package-backed memory pattern. It initializes OracleAgentMemory with a database connection pool and a custom local deterministic embedder. LocalCodexEmbedder is notebook code, not a built-in Oracle AI Agent Memory embedder.

The local embedder is intentionally billing-free, which makes the notebook runnable for people who do not want to attach paid model usage to a tutorial.

Abbreviated setup pattern: define CONFIG, import and initialize oracledb, implement LocalCodexEmbedder, and set stable values for AGENT_MEMORY_USER_ID and AGENT_MEMORY_AGENT_ID before running this example.



```python 
from oracleagentmemory.core.oracleagentmemory import OracleAgentMemory 
from oracleagentmemory.apis.searchscope import SearchScope 
 
db_pool = oracledb.SessionPool( 
    user=CONFIG["ORACLE_USER"], 
    password=CONFIG["ORACLE_PASSWORD"], 
    dsn=CONFIG["ORACLE_DSN"], 
    min=1, 
    max=4, 
    increment=1, 
) 
 
agent_memory = OracleAgentMemory( 
    connection=db_pool, 
    embedder=LocalCodexEmbedder(dimensions=32), 
    llm=None, 
    extract_memories=False, 
    schema_policy="create_if_necessary", 
) 
```

Use oracleagentmemory from your application layer when you need package-managed users, agents, memories, threads, scoped retrieval, and context assembly. Keep systems of record separate from memory records: memory helps provide context, but application logic and authoritative data sources should still decide what is true, allowed, and final.

Implementation note: Use a schema whose default tablespace supports the JSON objects created by Agent Memory. If a local SYSTEM schema sits on a tablespace that rejects JSON object creation, the better production answer is a dedicated application schema. The notebook can still explain the issue clearly instead of hiding it.

Store and Search: What a Realistic Memory Looks Like

A realistic Codex memory is not generic trivia about a user. For this workflow, memory should capture how a developer actually works: the connection name they used, the SQLcl path that succeeded, the MCP config location, the failed privilege boundary, the retrieval query that helped, and the final fix that should be reused later.



```python 
thread = agent_memory.create_thread(
    user_id=AGENT_MEMORY_USER_ID,
    agent_id=AGENT_MEMORY_AGENT_ID,
)
 
thread.add_memory( 
    "Developer validated Codex CLI with SQLcl MCP alias codex_mcp " 
    "against local Oracle AI Database service FREEPDB1." 
) 
 
results = agent_memory.search(
    query="Codex SQLcl MCP alias validation and Agent Memory setup", 
    scope=SearchScope(user_id=AGENT_MEMORY_USER_ID), 
) 
```

That kind of memory pays off because it is operational. It can help Codex answer the next question with context from the previous debugging session, but it is still scoped and retrievable through a database-backed API.

Where LangChain Adds Value

LangChain should not be treated as the source of truth. Codex CLI does not call LangChain directly in this architecture.

LangChain runs in your application layer, where it can format context, wrap retrieval behavior, expose a familiar retriever shape, and plug Oracle-backed memory into chains or agents that already expect LangChain interfaces. In this notebook, LangChain is used as a compatibility layer: the notebook’s custom Oracle-backed hybrid_search() path performs hybrid retrieval, and its results are wrapped as LangChain Document objects so they can flow into applications that already expect LangChain interfaces.

Use Oracle AI Database for persistence, retrieval, tenancy, validation evidence, and tool traces.
Use Oracle AI Agent Memory for package-backed memory threads, durable memories, and scoped search.
Use LangChain for retriever wrappers and orchestration compatibility when the application already expects that shape.
Use Codex CLI for developer interaction, planning, and guided execution.

The notebook's LangChain section is valuable because it comes late. By the time it appears, the database tables, package memory, retrieval scores, and validation snapshot already exist. LangChain becomes a wrapper around evidence, not a substitute for evidence.

Do not add LangChain just to make the workflow sound more agentic. If the application only needs direct SQL, package-backed Agent Memory search, or a simple evidence table, the extra abstraction can make debugging harder. LangChain is a good fit when the consuming application already expects retrievers, documents, chains, or tool orchestration. It is not the permission boundary, the memory store, or the audit layer.

Engineering Guidance

The difference between demo success and production success is disciplined operations. Most failures at this stage come from integration gaps, not model behavior. A local notebook can prove the wiring. A production workflow needs smaller database roles, managed secrets, clear MCP approval policy, repeatable environment setup, and monitoring around memory writes and tool calls.

Access and privilege model

Use a dedicated application schema where possible, then tighten grants with least-privilege roles and quotas.
Keep saved SQLcl aliases separate by role and environment instead of sharing one broad connection.
Start read-only wherever possible and gate write operations with explicit confirmation workflows.
Use schema allowlists and separate accounts for development, test, and production.

Observability model

Log tool name, thread ID, timestamp, status, and sanitized inputs and outputs.
Classify failures into runtime, discovery, connection, permission, query, and retrieval categories.
Keep a troubleshooting playbook in the repo so setup issues do not become tribal knowledge.
Check whether retrieval quality changes as more data and memory records are added.

Reliability model

Prefer deterministic SQL patterns with bounded result sets.
Use retrieval-first context assembly for memory-heavy tasks.
Avoid giant context stuffing as a substitute for memory design.
Review and prune tool surfaces periodically.
Move from the local deterministic embedder to a supported embedding model after cost, latency, and privacy review.

Typical Failure Modes

Most teams hit a predictable set of issues.  

Runtime failure: sql -mcp does not start

Check the absolute SQLcl path, confirm Java is available, and run sql -mcp outside Codex first. Resolve runtime issues before checking assistant behavior.

Discovery failure: Codex does not see tools

Check the Codex MCP configuration, confirm the configured command points to the SQLcl executable, and restart or reload Codex CLI after edits.

Connection failure: tools are present but queries fail immediately

Permission failure: queries execute selectively and fail on specific objects

Retrieval quality failure: answers are fluent but weakly grounded

Why the Hybrid Model Is Usually the Best Long-Term Design

No single layer handles both execution and memory well. Trying to force everything into Codex context gets messy fast: you either lose control over execution, or you stuff too much state into prompts just to keep things working. On the other side, if you only build backend memory systems, you lose the speed and usability that makes an assistant useful during development.

The hybrid approach works because it does not try to solve everything in one place. Execution stays controlled through MCP. Memory stays durable and queryable in Oracle AI Database. Oracle AI Agent Memory gives application code a cleaner memory API. LangChain adds orchestration only after evidence exists.

In real teams this usually evolves over time. Start with Codex CLI, SQLcl MCP, read-only access, and strong logging. Once people rely on the workflow, the gaps become visible: lost context, weak traceability, repeated setup work, or retrieval that is hard to explain. That is when database-backed memory and structured retrieval become worth adding.

Conclusion

A Codex and SQLcl MCP workflow becomes useful when it is treated as an engineering pattern, not just a setup trick. Codex keeps the developer moving. MCP makes tool access explicit. SQLcl connects those tools to reviewed Oracle profiles. Oracle AI Database stores durable memory records, traces, and retrieval evidence. Oracle AI Agent Memory gives applications a practical memory API. LangChain helps when retrieval needs to become reusable orchestration.

The result is a workflow a team can inspect. You can see what Codex asked for, which tool path ran, what the database allowed, which memory records were retrieved, and how the final answer was assembled.

That is the shift that matters: from assistant access that is implicit and hard to audit, to explicit boundaries, durable memory, and evidence a developer can actually debug.

Frequently Asked Questions

What is MCP in this context?

MCP is the protocol boundary that lets Codex call explicit tools exposed by a server instead of accessing systems implicitly.

What does MCP protect, and what does it not protect?

MCP makes the tool interface explicit and reviewable: Codex can call only the tools exposed by the server. It does not replace database security. The saved SQLcl connection profile, database user, grants, roles, network controls, and database policies determine what those tools can actually access or change.

Why use SQLcl for Oracle MCP?

SQLcl already understands Oracle workflows and can run as the Oracle SQLcl MCP server with sql –mcp, making the Oracle integration practical and direct.

Is this Codex CLI or Codex app?

The workflow should be described as Codex CLI/TUI oriented because the MCP configuration is written as a local Codex config preview and the SQLcl MCP server is launched as a command.

Why include Oracle AI Database if MCP already works?

MCP handles the execution boundary. Oracle AI Database handles durable memory, retrieval, vector search, concurrency, observability, and governance.

Do I need an OpenAI API key?

Why use SQLcl MCP instead of connecting directly from Codex?

MCP gives the workflow a defined tool boundary. Direct connections can be useful in notebooks, but Codex should call tools through a contract that can be logged, scoped, and reviewed.

Why include LangChain if Oracle already stores memory?

Because many teams already have LangChain-shaped retrievers and chains. The notebook shows how to expose Oracle-backed retrieval through that shape while keeping memory and enforcement in the database.

Is this RAG vs agent memory?

What is the minimum viable setup?

SQLcl MCP configured in Codex CLI, one safe saved Oracle connection, and a read-only validation flow.

Should production start with read-write permissions?

Usually no. Start read-only, log everything important, and add write scopes gradually with explicit approvals.

What is the best rollout strategy?

Pilot in development with read-only access and strong logging, then expand capabilities in controlled phases as the team learns which memory and tool paths are actually useful.

Companion Troubleshooting Appendix

Minimum viable setup: SQLcl MCP configured in Codex CLI, one approved Oracle connection, read-only validation, and database-side activity logging. 

First checks: confirm sql -mcp starts, Codex sees the tools after restart, and the saved SQLcl connection alias resolves. 

Environment model: use separate credentials and policies for dev, test, and prod, with stricter controls as capability expands. 

Logging model: capture tool name, timestamp, thread ID, status, sanitized input/output summaries, and relevant SQLcl MCP log records. 

Retrieval quality: tune chunk size, enrich metadata, review embedding choice, and evaluate retrieval against representative queries. 

Common anti-pattern: expanding tool surfaces before ownership, logging standards, and runbooks are in place. 

Rollout path: pilot in dev with read-only access and strong logging, then expand capabilities in controlled phases. 

Schema: Agent Memory package tables need a schema and tablespace that can create the package's JSON-backed objects.

Model calls: an API key can be present but still fail if the model provider account has no quota.

Resources

Agent memory is a database problem: Oracle research makes the case

Anya Summers — Tue, 11 Aug 2026 15:16:38 +0000

A technical report on arXiv sets out a database-native memory substrate for long-horizon agents: 93.8% on LongMemEval, roughly 10.7× fewer input tokens than flat conversation history, and governance treated as a design constraint, not an afterthought.

Key Takeaways

Agent memory is fundamentally a data-management problem. Long-horizon agents need persistent, scoped, retrievable, and governable memory, not just larger context windows or more conversation history.
Oracle Agent Memory manages working memory, long-term factual memory, and procedural memory directly on Oracle AI Database, with a lifecycle spanning extraction, consolidation, retrieval, summarisation, and revision.
In the report, Oracle Agent Memory achieved 93.8% on LongMemEval and kept estimated input context to about 1,300 tokens per request at turn 80, compared with roughly 13,900 for flat conversation history.
Keeping agent memory in the database keeps it close to the enterprise data it reasons over, while bringing transactions, relational, JSON and vector capabilities, scoped retrieval, and database-enforced governance into the same substrate.

Prompt and context engineering emerged for a reason: to elicit specific behaviours from large language models whilst maintaining a high signal-to-noise ratio in the context window, through systematic curation of the data injected into it. But as models grow more capable at reasoning, extensive context-window management is becoming less necessary.

The real job to be done sits elsewhere: adapting agents to new information, and making them dependable on long-horizon tasks. That job is the subject of a new technical report from Oracle's AI teams, Oracle Agent Memory as an Enterprise Memory Substrate for Long-Horizon AI Agents, published this month on arXiv.

Agent memory has emerged as the solution: the capability that lets agentic systems learn, adapt, and personalise from continuous interaction between the agent, its users, and its environment. The report grounds this in recognisable enterprise use cases: support agents recalling incident history across weeks, coding agents reusing prior design decisions, analytics agents that know what "average spending" means in the business.

The core problem investigated is not recall; it is maintaining heterogeneous memory under explicit scope, retention, and retrieval constraints; this is effective memory management. Yet although many memory-management solutions have emerged, few have met the demanding requirements of enterprise AI workloads. Most are variations on "just add a vector store": fragments assembled around the model (reflection buffers, framework add-ons, standalone memory services) that each work in a demo, and that together complicate governance, security, data locality, and reliability in an enterprise setting.

The gap is enterprise-grade memory: a substrate where governance and security are entry requirements rather than roadmap items, and where scale, reliability, and data locality come built in rather than bolted on. The report is written squarely at that gap. Its starting observation is simple: the list of things agent memory demands (persistence, indexing, scoped retrieval, transactions, governance) is a list of database requirements.

Oracle Agent Memory is a database-native memory substrate that treats agent memory as a managed lifecycle, spanning ingestion, extraction, consolidation, retrieval, summarisation, and revision, built directly on Oracle AI Database. The report organises that memory into three operational categories:

Working memory: the active state of a task, materialised as thread summaries and context cards compact enough for prompt injection.
Long-term factual memory: durable facts, preferences, and profile attributes that persist across sessions under user and agent scope.
Procedural memory: lessons, strategies, and guidelines drawn from prior outcomes; what the agent has learned to do, not just what it knows.

An active memory core does the opinionated work: synchronising threads, extracting durable facts, refreshing summaries and context cards, orchestrating search. Beneath it sits a passive memory-store contract (insert, search, retrieve, enforce scope) that stays reusable even for third-party memory libraries wanting Oracle AI Database as their backend.

The report measures the substrate across long-horizon memory benchmarks, a flat-history baseline, and estimated token behaviour:

93.8% on LongMemEval (469 of 500), including 100% on single-session assistant recall and 96.2% on temporal reasoning. Multi-session reasoning is the honest low point at 88%, and the report says so.
Roughly 10.7× fewer estimated input tokens than a flat-history baseline by turn 80 of a scripted conversation: about 1,300 tokens per request against roughly 13,900.
48 wins, 13 losses, 19 ties in pairwise judging against that same flat-history baseline, a 3.7× win ratio among non-tied turns, even though the baseline saw the entire transcript. Bounded, relevant context beat complete, unfocused context.
0.680 on BEAM at the 1M-token scale under event-presence scoring, ahead of previously reported external results, with the stricter order-sensitive score (0.630) and an early 10M stress test (0.510) published alongside it.

For enterprise leaders, the argument is about where institutional memory should live. Agent memory holds private conversations, inferred preferences, learned procedures, and retrieved enterprise facts. Store it in a side service with its own authorisation model and you replicate identity, role, and policy outside your primary data platform. Place it in the database and memory stays close to the enterprise data it reasons over, inheriting the controls you already trust: with database-enforced access control through Oracle Deep Data Security, a denied record is absent from retrieval because the acting identity was never allowed to see it, not because an application filter caught it afterwards. Memory stops being another shadow data store to govern.

For developers, the report reads like an engineering document, because it is one. Threads are the integration primitive, and the workflow has four recurring stages:

Initialise the memory client with a database connection, an embedder, and an optional LLM.
Create or reopen a thread, scoped to a user and an agent.
Add messages so the system persists history and runs extraction and summarisation.
Retrieve context via scoped search, context cards, or thread summaries before the next turn.

Defaults are conservative: bounded reads, bounded search, explicit scope with exact-match controls, no hidden schema side effects. The oracleagentmemory Python client is model-agnostic at the embedder and LLM layer, slots into harnesses such as LangGraph and WayFlow, and supports three integration patterns (middleware-centric, tool-centric, or hybrid). The appendices cover setup, thread lifecycle, and search semantics in runnable detail.

The report also stakes out an evaluation position both audiences should note: downstream accuracy alone is not enough. A correct answer can hide noisy retrieval; a wrong one can hide a memory layer that did its job. It measures memory as a system instead, separating evidence retrieval, evidence use, task outcome, and operational efficiency, and applies that standard to itself, publishing configuration caveats, threshold sweeps, and failure modes in plain sight.

Every team building agents eventually rediscovers the same requirements list, and then rebuilds it around a vector store. The position behind Oracle Agent Memory is that the list already has an answer. Give agents a substrate that speaks transactions, vectors, JSON, and policy natively, and the effort moves to where it actually matters: managing the lifecycle of what an agent remembers, not plumbing where it stores it. The storage question is settled. Remembering is the work. The full report is available on arXiv: https://arxiv.org/pdf/2607.13157

FAQs

What is agent memory?

Agent memory is the persistent state that lets an AI agent retain and retrieve useful information across interactions. That can include active task context, facts and preferences learned over time, and procedures or strategies derived from previous outcomes. Unlike the context window, that information does not have to disappear when the current conversation ends.

How is agent memory different from RAG or a vector database?

Vector retrieval can be part of agent memory, but retrieval alone is not memory management. An agent also needs to decide what to retain, how that information is scoped, when it should be updated or summarised, how long it should persist, and who is allowed to retrieve it. The Oracle report treats those as lifecycle and data-management problems rather than just search problems.

Why use a database for AI agent memory?

Agent memory needs many of the capabilities databases already provide: persistence, indexing, structured and semantic retrieval, transactions, access control, and governance. A database-native approach also allows relational data, JSON, vectors, and memory state to live on the same managed substrate rather than being distributed across separate memory services.

How does Oracle Agent Memory integrate with agent applications?

Developers create or reopen a thread scoped to a user and agent, add messages, and retrieve relevant context before subsequent turns. The oracleagentmemory Python client is model-agnostic at the embedding and LLM layers and supports middleware-centric, tool-centric, and hybrid integration patterns, including with frameworks such as LangGraph and WayFlow.

Custom Memory Extraction for AI Agents: Turning Conversations into Useful Facts

Anya Summers — Tue, 11 Aug 2026 15:11:32 +0000

Learn how Oracle AI Agent Memory uses custom extraction instructions, thread-level overrides, and tool-result metadata to turn support conversations into durable, scoped memory.

Companion notebook: custom_memory_extraction_agent_memory.ipynb

Key Takeaways

Oracle AI Agent Memory can transform raw user-agent messages into durable memories stored in Oracle AI Database.
Default extraction provides a general memory baseline; custom extraction instructions make memory formation reflect a domain-specific policy.
A customer-support agent should preserve durable facts such as order IDs, return requests, delivery issues, escalation commitments, tool-confirmed statuses, and stable communication preferences.
Good memory is selective: the extractor should ignore greetings, speculation, credentials, payment details, one-time codes, and temporary conversational wording.
Client-level custom instructions define the default extraction behavior, while thread-level overrides allow narrower policies for special workflows such as escalations.
Tool-result metadata can be inherited into memories, making later search more scoped, auditable, and aligned with enterprise retrieval boundaries.
The notebook verifies database connectivity and table-creation permissions before running memory operations.

Custom extraction turns raw conversation into governed memory

Why Agents Need Selective Memory

Oracle AI Agent Memory provides a database-backed memory layer for AI agents, so applications can store messages, durable memories, metadata, summaries, and prompt-ready context in Oracle AI Database.

AI agents are most useful when they can carry context forward. A support agent may need to remember that a customer reported a damaged item, prefers SMS updates, or was promised an escalation by Friday. Without persistent memory, the agent has to rediscover these facts from chat history or ask the user again.

But remembering everything is not the same as remembering well. Real conversations contain greetings, clarifications, temporary codes, apologies, repeated facts, speculation, and sometimes sensitive information. If every detail becomes durable memory, future retrieval becomes noisier and harder to govern.

Custom memory extraction addresses this by turning extraction into an application policy. Instead of only asking the model to summarize a conversation, developers can define what their workflow considers useful memory and what should be ignored.

The Customer-Support Use Case

The companion notebook uses a customer-support workflow because it has a realistic mix of natural language, exact identifiers, tool results, and policy boundaries. This is the kind of workflow where a generic transcript summary is not enough.

The support conversation includes durable facts such as order identifiers, return intent, delivery problems, replacement commitments, and customer preferences. It also includes details that should not become long-term memory, such as temporary wording, credentials, payment details, and speculation.

Preserve exact identifiers such as order IDs, return IDs, and case IDs.
Preserve confirmed support facts such as delivery issues, product defects, return requests, and replacement commitments.
Preserve stable preferences that should influence future interactions.
Preserve tool-confirmed facts when a tool result is the source of truth.
Ignore small talk, unconfirmed guesses, secrets, payment details, and one-time codes.

Customer-support extraction policy

From Conversation Text to Durable Memory

Oracle AI Agent Memory stores the conversation and uses an extraction workflow to form higher-level memories. In a support case, those memories should be concise, durable, and useful for later retrieval. A memory like "Customer prefers morning delivery windows for replacement shipments" is more reusable than several raw turns where the user mentioned their preference indirectly.

The notebook first establishes a baseline extraction path. This baseline is useful because it shows the general extraction behavior before any domain-specific policy is added. The custom path then uses the same kind of support conversation but adds explicit extraction instructions.

SUPPORT_EXTRACTION_INSTRUCTIONS = """
Extract only durable customer-support memory that can help future support interactions.

Preserve confirmed order IDs, return requests, delivery issues, escalation reasons,
stable customer preferences, and tool-derived support facts.

Ignore greetings, small talk, speculation, credentials, payment secrets,
one-time verification codes, and temporary conversational wording.
""".strip()

These instructions guide the LLM-assisted extraction step. They help shape what should become durable memory, but they should not be treated as deterministic filtering, guaranteed redaction, or a replacement for application-level security controls.

This instruction block is intentionally written like a product policy. It does not ask the extractor to remember more; it asks the extractor to remember better. The policy defines durable support memory, instructs the extractor to preserve exact identifiers, and excludes information that should not influence future agent behavior.

Baseline Extraction vs. Custom Extraction

The notebook shows the comparison directly. A baseline client uses the package's general extraction behavior. A custom client uses the same support scenario with custom extraction instructions. This makes the effect of the policy visible rather than theoretical.

from oracleagentmemory.core import MemoryExtractionConfig, OracleAgentMemory

base_memory = OracleAgentMemory(
    store=base_store,
    llm=memory_llm,
    memory_extraction_config=MemoryExtractionConfig(
        memory_extraction_frequency=1,
        enable_context_summary=False,
    ),
)

custom_memory = OracleAgentMemory(
    store=custom_store,
    llm=memory_llm,
    memory_extraction_config=MemoryExtractionConfig(
        memory_extraction_frequency=1,
        enable_context_summary=False,
        memory_extraction_custom_instructions=SUPPORT_EXTRACTION_INSTRUCTIONS,
    ),
)

The important design point is that the application owns the memory policy. The model helps extract memories, but the application defines what kinds of facts should survive, which exact identifiers matter, and what kinds of text should be excluded.

Because this policy is evaluated by an LLM-assisted extraction step, it should be treated as guidance rather than deterministic filtering, guaranteed redaction, or a replacement for application-level security controls.

For the reader, the expected outcome is straightforward: baseline extraction demonstrates general memory formation; custom extraction demonstrates memory formation shaped by support workflow priorities.

What to Look for in the Output

The most important signal is not that the custom path creates more memory records. The stronger signal is that the memory records are better shaped for the workflow. The custom extraction path should preserve durable support facts while avoiding temporary or sensitive details that should not guide future conversations.

The order identifier remains available as an exact support reference.
The return or replacement context is preserved as durable case state.
The customer's stable delivery or communication preference is retained.
Temporary details, such as a one-time lobby or access code, are not treated as reusable memory.
Tool-confirmed status can be preserved with metadata that describes the source and workflow tag.

This before-and-after comparison is what makes custom extraction practical: it shows how memory can become more useful, searchable, and governable without turning the full transcript into long-term state.

Signal in the notebook	Baseline extraction	Custom extraction
Exact support identifiers	May preserve the identifier if it is generally salient.	Explicitly preserves order, return, and case identifiers as durable support references.
Return or replacement state	May summarize the conversation at a general level.	Keeps confirmed return, replacement, delivery, and escalation facts as reusable case state.
Customer preference	May capture the preference if it appears important in the conversation.	Preserves stable communication and delivery preferences that should influence future interactions.
Temporary detail	May not have a domain-specific reason to exclude it.	Treats one-time codes, temporary instructions, and conversational noise as non-durable.
Tool-confirmed fact	May treat tool output like another message.	Preserves tool-derived support facts and selected source metadata for scoped retrieval.

Client-Level Defaults and Thread-Level Overrides

Not every conversation in an application needs the same extraction policy. Most support threads may follow a broad support-memory policy, while an escalation thread may need a narrower policy focused only on shipment commitments and customer-facing follow-up deadlines.

Oracle AI Agent Memory supports this pattern by allowing custom extraction instructions at the client level and overrides at the thread level. The client-level policy becomes the default. A thread-level override can refine the policy for one conversation.

escalation_thread = custom_memory.create_thread( 
    thread_id=f"escalation_support_{RUN_ID}", 
    user_id=USER_ID, 
    agent_id=AGENT_ID, 
    memory_extraction_config=MemoryExtractionConfig( 
        memory_extraction_frequency=1, 
        enable_context_summary=False, 
        memory_extraction_custom_instructions=ESCALATION_EXTRACTION_INSTRUCTIONS, 
    ), 
)

The notebook also shows lifecycle control for thread-level instructions. A workflow can update the thread policy when the support state changes, or clear the override to return to the client-level default.

updated_escalation_thread = custom_memory.update_thread( 
    escalation_thread.thread_id, 
    memory_extraction_config=MemoryExtractionConfig( 
        memory_extraction_custom_instructions=( 
            "Extract only shipment escalation commitments and customer-facing follow-up deadlines." 
        ) 
    ), 
) 
 
cleared_escalation_thread = custom_memory.update_thread( 
    escalation_thread.thread_id, 
    memory_extraction_config=MemoryExtractionConfig( 
        memory_extraction_custom_instructions=None 
    ), 
)

Client-level policy and thread-level override

Common Patterns for Custom Extraction Instructions

Custom extraction instructions are flexible by design. Developers do not need a different memory API for every workflow. Instead, they can express the workflow's memory policy through instructions passed into the extraction configuration.

The useful pattern is to write instructions as a boundary: what to preserve, what to ignore, and which source or metadata signals should influence the extracted memory. The following patterns are not separate package objects; they are practical ways to structure `memory_extraction_custom_instructions` for common agent workflows.

Pattern	Use when	Instruction focus
Support memory policy	A support agent needs durable case context across future interactions.	Preserve order IDs, return IDs, delivery issues, replacement commitments, escalation reasons, and stable preferences. Ignore greetings, credentials, payment details, one-time codes, and temporary wording.
Escalation-only policy	One thread needs a narrower policy than the default client-level behavior.	Preserve escalation reason, owner or team, customer-facing follow-up deadline, and confirmed commitments. Ignore general troubleshooting and repeated background context.
Tool-result policy	The agent receives tool outputs that should be treated as authoritative workflow state.	Preserve tool-confirmed facts, keep exact identifiers, inherit selected metadata such as tenant, source, and tags, and distinguish tool-confirmed facts from user-reported claims.

Tool-Aware Extraction with Metadata

Tool-aware extraction is especially useful in enterprise workflows because many agents do not rely only on user messages. They call tools, retrieve operational state, and produce tool results that may be more authoritative than the natural-language conversation.

In the support notebook, a tool result can carry metadata such as tenant, source, and tags. The extraction policy can tell the memory layer to preserve tool-derived support facts, and selected metadata can be inherited into the extracted memory.

tool_thread = custom_memory.create_thread( 
    thread_id=f"tool_support_{RUN_ID}", 
    user_id=USER_ID, 
    agent_id=AGENT_ID, 
    memory_extraction_config=MemoryExtractionConfig( 
        memory_extraction_frequency=1, 
        enable_context_summary=False, 
        memory_extraction_custom_instructions=TOOL_AWARE_EXTRACTION_INSTRUCTIONS, 
        memory_extraction_inherit_message_metadata=["tenant", "source", "tags"], 
    ), 
)

This pattern lets the application preserve not only the memory text, but also where the remembered fact came from. Later, scoped retrieval can use both natural-language query text and metadata filters.

tool_filtered_results = await tool_thread.search_async( 
    "replacement shipment delayed backordered hinge escalate", 
    max_results=5, 
    exact_thread_match=True, 
    record_types=["memory", "fact", "preference", "guideline"], 
    metadata_filter={ 
        "tenant": "acme", 
        "source": "support-copilot", 
        "tags": ["support", "tool:order_status", "replacement"], 
    }, 
)

Tool-result metadata and scoped retrieval

Why Metadata Matters for Memory Governance

Memory retrieval should be relevant, but it also needs boundaries. In enterprise systems, an application may need to search only within a tenant, case, workflow, source system, or review state. Similarity ranking alone is not an authorization or governance model. Metadata filters complement, but do not replace, application authentication and authorization.

Metadata gives the application a way to constrain which memories are eligible before ranking. In this article's use case, metadata can distinguish support-copilot facts from raw conversation messages, tag memories that came from an order-status tool, or keep tenant-specific facts scoped to the right customer context.

This is also why custom extraction and metadata inheritance belong together. Custom extraction shapes the content of the memory. Metadata inheritance shapes how that memory can be retrieved, filtered, reviewed, and governed later.

Running the Companion Notebook

The companion notebook starts with a practical database setup path. The setup cells connect to an Oracle AI Database schema, verify a simple query, and verify table-creation permissions before the memory store is initialized.

For a lightweight trial environment, developers can use FreeSQL to get an Oracle AI Database schema before moving to a managed development or production database environment.

That table-permission check matters because Oracle AI Agent Memory manages database-backed storage objects. If a developer can connect but cannot create tables, the memory workflow will fail later during schema setup. Surfacing that issue early makes the notebook more practical.

The notebook also sets the Developer Hub program identifier before creating the database connection pool. This follows the Developer Hub convention for technical assets that connect to Oracle AI Database and makes the notebook identifiable in database telemetry.

For environments with a database-resident embedding model, the same pattern can use Oracle AI Database-backed embeddings and Oracle AI Vector Search. For the custom extraction article, the main focus remains memory formation: how raw conversations become durable, domain-specific memories with the right policy and metadata.

For database-level retrieval background, see the Oracle AI Vector Search documentation.

What the Companion Notebook Demonstrates

Connect to Oracle AI Database and verify table permissions.
Set the Developer Hub program identifier before opening the database connection.
Configure Oracle AI Agent Memory for a support workflow.
Add a support conversation containing exact identifiers, support commitments, preferences, and details that should not become memory.
Run baseline extraction as a general-purpose reference point.
Apply support-specific custom extraction instructions.
Compare extracted memories before and after customization.
Override extraction instructions for one escalation thread.
Update and clear thread-level extraction instructions.
Extract tool-derived support facts and inherit selected metadata.
Search with metadata filters to retrieve scoped memory results.
Clean up memory records and database objects after the run.

Security and Deployment Notes

Custom extraction instructions are not a substitute for application security controls. Oracle AI Agent Memory persists data in the database schema selected by the application owner, while the surrounding application remains responsible for authenticating users, enforcing access control, and passing the correct user, agent, and thread scope into memory operations.

Applications should avoid sending secrets or unnecessary sensitive information into memory ingestion flows unless that behavior is explicitly intended and reviewed. Extraction instructions can guide what the model should preserve or ignore, but they should be treated as policy guidance rather than a hard redaction guarantee.

Keep credentials, payment details, and one-time verification codes out of memory ingestion when possible.
Use exact user, agent, thread, and tenant scope for retrieval.
Use metadata filters for workflow boundaries such as source system, review status, or tool tag.
Review custom extraction instructions with domain owners, not only with developers.
Use cleanup steps in notebooks and development schemas to avoid leaving test data behind.

Conclusion

Custom memory extraction makes agent memory more useful by making it selective. Instead of treating every conversation as durable knowledge, developers can define what their application should preserve, which identifiers matter, what metadata should follow extracted memories, and when a specific thread needs a narrower policy.

For customer-support agents, this means future interactions can draw on the right facts: order IDs, delivery issues, return requests, escalation commitments, stable preferences, and tool-confirmed statuses. The agent gets memory that is easier to retrieve, easier to scope, and better aligned with the workflow it serves.

A practical way to start is with a narrow extraction policy, inspect the extracted memories, and then expand the policy and metadata taxonomy with domain owners.

Frequently Asked Questions

What is custom memory extraction?

Custom memory extraction lets developers guide what Oracle AI Agent Memory should preserve as durable memory and what it should ignore when processing conversation messages.

Why should an agent not remember everything?

Full transcripts contain temporary wording, repeated details, speculation, and sometimes sensitive data. Durable memory should preserve reusable facts, preferences, commitments, and workflow state.

What is the difference between baseline and custom extraction?

Baseline extraction shows general-purpose memory formation. Custom extraction adds domain-specific instructions so the memory output reflects the application's workflow policy.

What is a thread-level extraction override?

A thread-level override is a custom extraction policy applied to one thread. It can narrow or change the default client-level policy for a specific workflow such as an escalation.

How does tool metadata help?

Tool metadata records where a memory came from and how it should be scoped. It can help retrieve only memories from a specific tenant, source system, or tool result.

Does the notebook require FreeSQL?

No. FreeSQL is a lightweight trial setup path. The same memory APIs can run against another Oracle AI Database environment with the required credentials and permissions.

Resources

Companion notebook

Oracle AI Agent Memory package

Oracle AI Agent Memory documentation

FreeSQL

Oracle AI Vector Search documentation

Hybrid Search for Oracle AI Agent Memory: Combining Semantic Recall with Exact Match

Anya Summers — Tue, 11 Aug 2026 14:59:04 +0000

Use Oracle AI Agent Memory hybrid search when persistent AI agent memory needs both semantic similarity and exact text precision.

Companion Notebook: Hybrid Search for Oracle AI Agent Memory: Combining Semantic Recall with Exact Match

Key Takeaways

Vector search is strong for semantic recall, but agent memory often contains exact strings that should remain first-class retrieval signals.
Hybrid search combines semantic retrieval and exact text matching so agents can recall both the named item and the surrounding context.
Scoped retrieval and hybrid search solve different parts of the problem: scope controls which memories are eligible to be searched, while hybrid search controls how those eligible memories are ranked.
Oracle AI Agent Memory uses `SearchStrategy.HYBRID` with `OracleDBEmbedder`, while `SearchIndexSyncMode` controls managed search-index refresh behavior.
The companion notebook validates hybrid retrieval across five exact, semantic, and mixed-query scenarios using deliberately similar memories and confirms the expected memory at rank one in every scenario.

This guide shows how to build hybrid retrieval for agent memory with Oracle AI Agent Memory, combining semantic recall with exact text matching.

The retrieval problem hybrid search solves

A useful agent memory system has to do two things at once. It must remember by meaning when the user paraphrases, and it must stay exact when the user refers to a specific business object, system event, or operational handle. Many retrieval misses happen in the gap between those two needs: the query is semantically close to several memories, but only one memory contains the exact identifier that should anchor the answer.

Hybrid search closes that gap. Instead of treating semantic recall and exact text matching as separate retrieval systems, it gives the memory layer a combined path for vector and keyword search. The agent can retrieve the surrounding context and preserve the literal token that makes the memory trustworthy.

That is the core value of agent memory hybrid search: long-term agent memory can stay flexible without losing the exact text that enterprise workflows depend on.

This article starts with a simple retrieval problem: a user may ask about a business event in natural language, then later refer to the same event by an invoice number, issue ID, error code, customer alias, or filename. A useful memory system should handle both query styles.

Setting up Oracle AI Agent Memory

Oracle AI Agent Memory provides a durable memory layer for AI agents. It stores scoped memories, retrieves relevant context for later turns, and helps applications separate what the agent is allowed to remember from how memories are ranked.

Hybrid search expands that retrieval model so an agent can recall by semantic meaning and by exact text in the same workflow, while memory governance remains tied to scope and application policy.

The setup has four main components:

`OracleAgentMemory` is the durable memory layer. It stores memories, applies scope, and exposes search.
`OracleDBEmbedder` connects retrieval to Oracle-backed embedding behavior used by the memory store.
`SearchStrategy.HYBRID` tells the memory layer to combine semantic and exact-text retrieval signals.
`SearchIndexSyncMode` controls when the managed search index is refreshed after memories are written.

Start by installing the oracleagentmemory package in the notebook or application environment. The hybrid-search API is configured in application code; the database schema and index setup are handled through the Oracle-backed memory store.

This makes Oracle AI Database agent memory a practical pattern for applications that need durable memories, scoped retrieval, and database-managed search.



```bash 
pip install oracleagentmemory==26.6.0 
```

The core imports for a hybrid-search configuration look like this:



```python 
from oracleagentmemory.apis.searchscope import SearchScope 
from oracleagentmemory.core import ( 
    MemoryExtractionConfig, 
    OracleAgentMemory, 
    SchemaPolicy, 
    SearchIndexSyncMode, 
    SearchStrategy, 
) 
from oracleagentmemory.core.embedders import OracleDBEmbedder 
```

Why vector memory is not always enough

Agent memory is not only a semantic archive of previous conversations. In enterprise systems, memory records often include short strings that carry operational meaning.

A single identifier can point to the correct incident, invoice, customer, model, file, run, or database error. If retrieval treats that identifier like ordinary prose, the agent can return a memory that is semantically related but operationally wrong.

Vector search helps when users paraphrase. It is especially useful when a user asks about a topic without repeating the exact words stored in memory.

But vector similarity is not the same as exactness. A short token can be diluted by the rest of the sentence, especially when the surrounding context is broad or when several memories are about similar workflows.

Where exact text matters

Exact text matters whenever a memory must point to a specific object rather than a general topic. Common examples include:

Issue IDs and ticket references used in engineering and support workflows.
Invoice, order, contract, and purchase-order numbers used in business workflows.
Database, application, and integration error codes used in troubleshooting.
Customer aliases, account IDs, and tenant names used for scoped recall.
Product SKUs, model numbers, filenames, branch names, and run IDs used by operational agents.

These values are small, but they often carry more authority than nearby prose. A robust agent memory layer should preserve that literal signal while still supporting semantic lookup and exact-match retrieval.

Types of search

Vector search	Keyword search	Hybrid search
Finds by meaning and paraphrase	Finds by exact text (literal identifiers)	Combines semantic and exact-text signals in one ranked retrieval path
Example query: “What blocked the renewal?”	Example query: “INV-48291”	Example queries: “INV-48291” and “What blocked the renewal?”

Table 1: Conceptual search-mode comparison

The differences become clearer when you compare what each mode optimizes for.

The following comparison explains the retrieval modes conceptually. The companion notebook executes the hybrid configuration and evaluates it across exact, semantic, and mixed queries; it is not a benchmark of three separately executed strategies.

Mode	Best at	Weak spot	Agent memory fit
Vector search	Paraphrase, semantic similarity, concept recall	Short identifiers may be low-signal	Good for natural-language recall
Keyword search	Literal identifiers, error codes, aliases, filenames	Brittle when wording changes	Good when exact strings matter
Hybrid search	Natural language plus exact handles	Requires database embedding and managed index setup	Strong fit when both semantic and exact-text retrieval matter

Table 2: Search mode comparison

Use hybrid search when:

Users ask in natural language but refer to exact business objects.
Memories include identifiers, aliases, filenames, error codes, or transaction IDs.
Several memories are semantically similar, but one exact token should decide the result.
The agent needs scoped retrieval across user, agent, tenant, or thread boundaries.

How Oracle AI Agent Memory supports hybrid retrieval for agent memory

Oracle AI Agent Memory exposes hybrid retrieval as a memory-store configuration. The application supplies an Oracle-backed embedder, selects `SearchStrategy.HYBRID`, and chooses an index synchronization mode. The result is one memory layer that can retrieve by meaning and exact text over the same stored memories, while still respecting user, agent, and thread scope.

`OracleDBEmbedder` is important because hybrid search relies on the database-managed embedding and index path. `SearchStrategy.HYBRID` selects the hybrid retrieval backend. `SearchIndexSyncMode` defines when new or updated memory content becomes searchable through the managed index. Together, these settings make retrieval behavior explicit in application code rather than hiding it behind an ad hoc query pipeline.

The configuration below shows the minimum pattern: choose an Oracle-backed embedder, set `SearchStrategy.HYBRID`, and choose how the search index should refresh:



```python 
db_embedder = OracleDBEmbedder( 
    connection=connection, 
    model=CONFIG["ORACLE_DB_EMBEDDING_MODEL"], 
    embedding_dimension=CONFIG["ORACLE_DB_EMBEDDING_DIMENSION"], 
) 
memory = OracleAgentMemory( 
    connection=connection, 
    embedder=db_embedder, 
    memory_extraction_config=MemoryExtractionConfig( 
        extract_memories=False 
    ), 
    schema_policy=SchemaPolicy.CREATE_IF_NECESSARY, 
    search_strategy=SearchStrategy.HYBRID, 
    search_index_sync=SearchIndexSyncMode.ON_COMMIT, 
    memory_store_id="hybrid_blog", 
) 
```

Memory extraction is disabled because the example inserts controlled durable memories directly and focuses specifically on retrieval behavior.

In a typical workflow, the agent writes durable memories with user, agent, or thread scope. Later, search requests use `SearchScope` to restrict eligible records before ranking happens. That separation matters: scope decides what the agent is allowed to remember, while hybrid search decides how eligible memories are ranked.

Hybrid retrieval flow

In an agent workflow, hybrid search is most useful when it sits between memory scoping and context assembly:

Figure 1. Scoped hybrid retrieval flow

`SearchScope` and hybrid search are complementary. Scope determines which records are eligible for retrieval; hybrid search ranks only those eligible records. In the notebook, each evaluation query uses the same generated finance-user and support-agent scope, and retrieval is restricted to durable memory records by passing `record_types=["memory"]`.

This flow is the reason hybrid search fits agent memory well. The application first narrows the memory universe with scope. Hybrid retrieval then combines semantic and exact-text signals only over eligible records. The final output is not just a matching row; it is a ranked context package the agent can use in the next step.



```python 
user_id = "finance_user_123" 
agent_id = "support_finance_agent" 
  
scope = SearchScope(user_id=user_id, agent_id=agent_id) 
  
memory.add_memory( 
    content=( 
        "Northstar Renewals has a renewal blocker: invoice INV-48291 "
        "failed reconciliation after ORA-27102 during month-end processing." 
    ), 
    user_id=user_id, 
    agent_id=agent_id, 
) 
 
async def search_memory(query, scope, max_results=5): 
    return await memory.search_async( 
        query=query, 
        scope=scope, 
        max_results=max_results, 
        record_types=["memory"], 
    ) 
 
exact_results = await search_memory("INV-48291", scope) 
 
semantic_results = await search_memory( 
    "What blocked the Northstar renewal?", 
    scope, 
) 
 ```

Both calls use the same `SearchScope`, hybrid strategy, and managed hybrid index. Only the query style changes.

For example, a user might ask “INV-48291” or “What blocked the renewal?” over the same scoped memories.

A vector-only search may understand the renewal-blocker question but underweight the exact invoice or error code. A keyword-only search may find INV-48291 or ORA-27102 but miss a paraphrased question such as “What blocked the renewal?”

Hybrid search gives both signals a chance to influence ranking, so the agent can retrieve the Northstar Renewals memory with the exact invoice ID, the ORA-27102 error code, and the natural-language explanation.

Testing hybrid retrieval with deliberately similar memories

The companion notebook does not test hybrid retrieval against a single obvious memory. It creates a deliberately similar set of memories, so the retrieval path has to choose between records that share overlapping business, invoice, customer, renewal, and error-code vocabulary.

The notebook stores five durable memories:

A target renewal-blocker memory for Northstar Renewals, containing invoice INV-48291, Oracle error ORA-27102, and the failed reconciliation that blocked the renewal.
A neighboring invoice memory for Northstar Renewals, containing invoice INV-48290, which is marked as paid and does not require follow-up.
A different customer renewal-delay memory for Milan Office Supplies, where the delay is caused by an approval workflow rather than reconciliation.
A general ORA-27102 troubleshooting memory that explains the database error without tying it to the Northstar invoice.
A Northstar customer-alias memory that maps Northstar Renewals to its enterprise account name.

This setup makes the demonstration more realistic. The expected result must outrank memories that are partially similar, such as another Northstar invoice, another renewal delay, or another ORA-27102-related record. That is the behavior an enterprise agent memory system needs: not just finding something related but retrieving the memory that preserves the right operational detail.

Evaluating exact, semantic, and mixed queries

The notebook then evaluates the same hybrid-search configuration across five query scenarios. Each query uses the same `SearchScope`, the same `SearchStrategy.HYBRID` configuration, and the same managed hybrid index. Only the query wording changes.

The five query categories are:

Exact invoice identifier: a query for INV-48291.
Exact Oracle error code: a query for ORA-27102.
Semantic renewal-blocker question: a natural-language question about what blocked the Northstar renewal.
Semantic customer question: a natural-language question about the Milan customer renewal delay.
Mixed customer and invoice question: a question that combines customer context with invoice reconciliation.

The validation checks whether the expected memory appears as the top-ranked result.

Query type	Expected memory	Rank	Validation
Exact invoice ID	Renewal blocker	1	PASS
Exact error code	Error reference	1	PASS
Semantic renewal question	Renewal blocker	1	PASS
Semantic customer question	Milan renewal	1	PASS
Mixed customer and invoice question	Renewal blocker	1	PASS

Table 3: Evaluation results table

This is a controlled functional evaluation, not a statistical benchmark comparing vector, keyword, and hybrid search. The purpose is to show that one hybrid-search configuration can retrieve the expected durable memory across exact, semantic, and mixed query styles.

Reading the notebook results

The results show that exact identifiers remained useful retrieval signals. The invoice query returned the renewal-blocker memory, while the ORA-27102 query returned the general error-reference memory. This matters because both the target renewal memory and the general troubleshooting memory contain the same error code, but they serve different user intents.

The semantic queries also returned the intended memories without requiring the user to repeat the exact stored wording. For example, the renewal-blocker question retrieved the Northstar renewal memory, while the Milan customer question retrieved the separate Milan renewal-delay memory.

The mixed query is the most representative enterprise case. It combines business context with an implied operational object: the user asks which invoice failed reconciliation for Northstar. The expected result is the memory containing INV-48291, ORA-27102, and the failed reconciliation context.

The notebook also displays distance values for the ranked results. In this result set, smaller distance values indicate stronger matches. However, distance should be treated as a retrieval signal for inspection, not as a universal accuracy score. The more important validation is whether the expected memory was returned at rank one for each query.

How hybrid search improves agent memory retrieval

Hybrid search can improve retrieval behavior because it matches how enterprise memory is actually written. A memory record rarely contains only natural-language explanation or only an identifier. It usually contains both: a named handle plus the context that explains why the handle matters.

Higher precision for identifier-driven questions because exact strings can influence ranking.
Better recall for paraphrased questions because semantic search still finds related memory records.
More useful context packages because the agent can retrieve both the handle and the surrounding explanation.
Lower risk of plausible but wrong recall when several memories are semantically similar.
Developers can inspect the ranked memories, metadata, and distances to verify whether the expected operational context was retrieved.

This is especially useful for support agents, finance assistants, operations copilots, developer agents, and workflow agents that must remember prior decisions, tool outputs, system errors, or customer-specific context.

The practical benefit is not only better search quality; it is better agent behavior.

Better retrieval can improve grounding, but the model still needs normal validation and application controls. Hybrid search gives the retrieval layer a stronger chance of selecting the memory that a human operator would have recognized immediately.

Production notes: schema and index setup

For production, treat hybrid search as part of the memory-store design, not as a last-minute query option. The schema should be owned by a dedicated application user with the right privileges, tablespace quota, and deployment controls. Memory scope should also match the application boundary, such as user, agent, tenant, or thread.

`SchemaPolicy.CREATE_IF_NECESSARY` is useful during first-time setup or when upgrading a schema so it can support hybrid search. For a large existing memory store, the first hybrid index build may take time because Oracle needs to prepare the managed search structures over stored memory content. Treat that step as a planned migration rather than a normal application startup task.

After the schema and index are ready, production applications may prefer `SchemaPolicy.REQUIRE_EXISTING`. That lets startup validate the expected schema instead of creating or modifying database objects.

`SearchIndexSyncMode` is a freshness and operations tradeoff:

`ON_COMMIT` is best for notebooks, demos, and interactive applications because newly committed memories become searchable immediately.
`MANUAL` is useful for bulk loads, backfills, and migrations where teams want to ingest many records first and refresh the index on a controlled schedule.
`AUTO` delegates background maintenance to Oracle-managed behavior, which can fit production workloads where some freshness lag is acceptable. `AUTO` is supported for `SearchStrategy.HYBRID`; keyword-only search does not support `AUTO`.

The database-resident embedding model is also part of the production contract. The same model and embedding dimension should be used by `OracleDBEmbedder` and the managed hybrid index so query embeddings and indexed memory vectors remain compatible.

Before publishing an agent that depends on durable memory, test the retrieval path with representative records. Include exact-identifier queries, semantic queries, mixed queries, scoped retrieval checks, and index freshness checks after writes. If the application has many similar records, include distractor memories in the test set so the expected result has to outrank nearby alternatives.

Teams can also pair hybrid retrieval with custom memory extraction when they need more predictable stored facts. Extraction controls what gets remembered; scope controls which records are eligible; hybrid search controls how eligible memories are ranked.

Conclusion

AI agent memory needs more than semantic similarity. Vector search helps agents remember by meaning, but enterprise workflows often depend on exact strings such as invoice numbers, error codes, customer aliases, file names, and transaction IDs. Keyword search helps with those strings, but it can be brittle when users ask in natural language.

Hybrid search gives the memory layer a practical middle ground. It preserves literal identifiers while still supporting semantic recall, so an agent can retrieve both the named object and the context that explains why it matters.

In the companion notebook, one `SearchStrategy.HYBRID` configuration retrieves the expected durable memory across exact, semantic, and mixed query styles. The deliberately similar memory set makes the demo more realistic: the target memory has to outrank records that overlap on customer, invoice, renewal, or error-code vocabulary.

For Oracle AI Agent Memory, `SearchStrategy.HYBRID`, `OracleDBEmbedder`, `SearchScope`, and `SearchIndexSyncMode` provide the core application controls for this pattern. Together, they help developers build memory systems that are scoped, durable, searchable by meaning, and precise when exact text matters.

Run the companion notebook to configure Oracle AI Agent Memory hybrid search, load the deliberately similar memory set, and inspect the ranked results for exact, semantic, and mixed queries.

Frequently Asked Questions

What is Oracle AI Agent Memory?

Oracle AI Agent Memory is a durable memory layer for AI agents. It lets applications store memories, retrieve relevant context later, and use scope controls such as user, agent, and thread scope so the agent searches only eligible memories before ranking results.

Why use hybrid search for agent memory?

Hybrid search is useful for agent memory because many memories contain both meaning and exact text. Vector search helps with paraphrased questions, while keyword search helps with identifiers such as issue IDs, SKUs, invoice numbers, aliases, and error codes. Hybrid search combines both signals so the agent can retrieve the right memory and the surrounding context.

Is vector search still useful for agent memory?

Yes. Vector search remains essential for paraphrase, concept recall, and long natural-language memories. Hybrid search adds exact-text strength instead of replacing vector search.

How do I build hybrid retrieval for agent memory?

Configure OracleAgentMemory with `OracleDBEmbedder`, set `SearchStrategy.HYBRID`, choose `SearchIndexSyncMode`, store scoped memories, then test exact, semantic, and mixed queries.

When should I use keyword-only search?

Use keyword-only search when retrieval is almost entirely literal text matching and embeddings are unnecessary. Many agent memory workloads need both exact strings and semantic context, which makes hybrid search a better default.

What is the difference between scoped retrieval and hybrid search?

Scoped retrieval controls which memories are eligible to be searched. Hybrid search controls how those eligible memories are ranked using both semantic and exact-text signals.

Why does hybrid search require `OracleDBEmbedder`?

The hybrid path uses Oracle-managed indexing and database-side embedding metadata. `OracleDBEmbedder` keeps the application embedder aligned with the database-backed retrieval path.

Which `SearchIndexSyncMode` should I start with?

For notebooks and interactive demos, `ON_COMMIT` is the simplest starting point. For bulk ingestion, `MANUAL` gives more control. `AUTO` is useful when Oracle-managed hybrid index maintenance fits the workload.

What should I test before publishing an agent memory workflow?

Test exact-identifier queries, semantic queries, scoped retrieval, index freshness after writes, and behavior when multiple similar memories exist. The strongest demos show both exact and natural-language retrieval over the same records.

Resources

Building Scalable Multi-Language Applications with Oracle Globally Distributed Database Using OCI-Based Drivers

Anya Summers — Tue, 11 Aug 2026 14:30:21 +0000

Modern enterprise applications are rarely built using a single programming language. A cloud-native application might use Python for AI and analytics, Node.js for REST APIs and C for latency-sensitive services. Despite the diversity of languages, the database-access goals are familiar: efficient connection pooling, intelligent routing, high availability, and predictable scalability.

Oracle Globally Distributed Database (GDD) enables applications to scale horizontally by distributing data across shards while presenting a single logical database to the application. To make these capabilities available consistently across languages, Oracle provides OCI-based (Oracle Call Interface) drivers that expose the same distributed database features through language-native APIs.

In this article, we'll explore how Python, Node.js and C applications use the same OCI foundation to build scalable, shard-aware applications.

Key Takeaways

Python and Node.js in Thick mode and C through OCI(Oracle Call Interface) give developers a consistent core workflow for Oracle Globally Distributed Database: create or reuse sessions, supply shard keys when needed, and release connections predictably. The APIs and feature coverage remain language-specific.
Shard-aware connection routing is consistent across languages. Applications acquire connections using sharding keys (and optionally super sharding keys), allowing Oracle Globally Distributed Database to automatically route requests to the appropriate shard.
Thoughtful pool sizing and high-availability configuration are essential in multi-shard systems: they support session reuse, help avoid uneven connection distribution, and can improve recovery behavior when the required services and client configuration are in place.

Use Python, Node.js, and C for shard-aware connection pooling and resilient database access.

A Common Foundation

One of the strengths of Oracle's language drivers is that they share a common implementation stack. Although the programming APIs differ, they ultimately leverage the same Oracle Call Interface (OCI) capabilities for connection management, sharding, and high availability. In Thick mode, the Python and Node.js drivers use Oracle Client libraries and Oracle Call Interface (OCI). C applications call OCI directly.

This means that developers can choose the programming language best suited to their workload while relying on consistent database behavior.

Why Thick Mode?

Thick mode is the Python and Node.js deployment mode that uses Oracle Client libraries and the Oracle Call Interface (OCI). It gives those applications access to the OCI-based connection behavior that C applications use directly.

High-performance session pooling enables session reuse, reducing connection-creation overhead under load and helping applications manage demand across shards.
Sharding and super sharding keys let the client request a connection for the data-owning shard, without embedding physical shard locations in application logic.
Oracle Net and the native-client or provider capabilities supply the connection behavior needed for enterprise deployments, including the routing information used to establish the right connection.
High availability is configured separately from driver selection. FAN, Application Continuity or Transparent Application Continuity (TAC), and Transparent Application Failover (TAF) can improve failure response and continuity during planned or unplanned events, but their availability and defaults depend on the driver or provider type, client version, database service, and pool or connection configuration.

Initializing Oracle Client enables Thick mode for Python and Node.js only. It does not, by itself, enable high-availability behavior. The same principle applies across C/OCI: configure and test the relevant services, client settings, and pool behavior for the driver in use.

Creating a Scalable Session Pool

Connection pooling is the first step toward scalable access. Instead of creating a new database connection for every request, applications reuse existing sessions, lowering latency and improving throughput. In a multi-shard system, pool configuration also matters because demand may not be evenly distributed across shards. Monitor pool utilization and shard-level workload patterns, then tune capacity for the traffic you actually observe.

The pool sizes below are illustrative only, not recommended defaults. Size pools using expected concurrency, request duration, database capacity, shard distribution, and service-level objectives.

Python

import oracledb

oracledb.init_oracle_client()

pool = oracledb.create_pool(
    user=user,
    password=password,
    dsn=connect_string,
    min=5,
    max=50,
    increment=5,
    getmode=oracledb.POOL_GETMODE_WAIT
)

Node.js

const oracledb = require('oracledb');

oracledb.initOracleClient();

const pool = await oracledb.createPool({
    user,
    password,
    connectString,
    poolMin: 5,
    poolMax: 50,
    poolIncrement: 5
});

C

OCIEnvCreate(...);

OCIHandleAlloc(...);

OCISessionPoolCreate(
    envhp,
    errhp,
    poolhp,
    &poolName,
    &poolNameLen,
    connectString,
    strlen(connectString),
    minSessions,
    maxSessions,
    increment,
    username,
    strlen(username),
    password,
    strlen(password),
    OCI_SPC_HOMOGENEOUS
);

Acquiring Shard-Aware Connections

The key benefit of shard-aware access is not that the application learns where shards live. It is that the client can direct work to the shard that owns the requested data. Supplying the sharding key and, where the data model uses one, the super sharding key helps avoid unnecessary data movement and keeps physical topology out of business logic. The following snippets are abbreviated examples. They omit credentials, error handling, variable declarations, pool shutdown, and full OCI environment and handle management. Production code should add those concerns and should release every acquired connection

Python

# The context manager releases the connection to the pool.
with pool.acquire(
    supershardingkey=[region],
    shardingkey=[customer_id]
) as connection:
    with connection.cursor() as cursor:
        cursor.execute(sql)

Node.js

let connection;
try {
  connection = await pool.getConnection({
    superShardingKey: [region],
    shardingKey: [customerId]
  });
  await connection.execute(sql);
} finally {
  if (connection) await connection.close(); // Return it to the pool
}

C

/* Create an authInfo handle, allocate key descriptors, add values,
   attach both keys, then request the pooled session.
   Error checks are omitted here. */
OCIAuthInfo *authInfo = NULL;
OCIShardingKey *shardKey = NULL, *superShardKey = NULL;

OCIHandleAlloc(envhp, (dvoid **)&authInfo,
               OCI_HTYPE_AUTHINFO, 0, NULL);

OCIDescriptorAlloc(envhp, (dvoid **)&shardKey,
                   OCI_DTYPE_SHARDING_KEY, 0, NULL);
OCIDescriptorAlloc(envhp, (dvoid **)&superShardKey,
                   OCI_DTYPE_SHARDING_KEY, 0, NULL);

OCIShardingKeyColumnAdd(shardKey, errhp, &customerId,
                         sizeof(customerId), SQLT_INT, OCI_DEFAULT);
OCIShardingKeyColumnAdd(superShardKey, errhp, region,
                         strlen((char *)region), SQLT_CHR, OCI_DEFAULT);

OCIAttrSet(authInfo, OCI_HTYPE_AUTHINFO, shardKey, sizeof(shardKey),
           OCI_ATTR_SHARDING_KEY, errhp);
OCIAttrSet(authInfo, OCI_HTYPE_AUTHINFO, superShardKey, sizeof(superShardKey),
           OCI_ATTR_SUPER_SHARDING_KEY, errhp);

OCISessionGet(envhp, errhp, &svcHandle, authInfo,
              poolName, poolNameLen, NULL, 0, NULL, NULL, NULL,
              OCI_SESSGET_SPOOL);

/* Execute work on the routed shard, then release the session. */
OCISessionRelease(svcHandle, errhp, NULL, 0, OCI_DEFAULT);
OCIDescriptorFree(shardKey, OCI_DTYPE_SHARDING_KEY);
OCIDescriptorFree(superShardKey, OCI_DTYPE_SHARDING_KEY);
OCIHandleFree(authInfo, OCI_HTYPE_AUTHINFO);

Although the APIs differ, each performs the same sequence:

Construct the sharding key.
Request a pooled connection.
Allow OCI to route the request to the correct shard.

Comparing the Drivers

The table helps an architect choose the API that matches the application language while preserving the same operating model.

Capability	Python (Thick)	Node.js (Thick)	C
Client / driver	Oracle Client	Oracle Client	Direct OCI calls
Session pool	create_pool()	createPool()	OCISessionPoolCreate()
Shard-aware checkout	pool.acquire()	pool.getConnection()	OCISessionGet()
Sharding / super sharding keys	Language-native parameters	Language-native parameters	OCIShardingKey + OCIAttrSet()
Connection release	Context manager or close()	connection.close()	OCISessionRelease()

The APIs are intentionally language-specific, while the core pooling and shard-aware routing workflow is consistent. Individual feature availability varies by driver mode, client version, database service, and configuration.

Conclusion

Oracle's OCI-based drivers provide a unified foundation for building scalable applications across multiple programming languages. Whether applications are written in Python, Node.js or C, developers have access to the same core capabilities—session pooling, shard-aware routing, high availability, and efficient native connectivity—through familiar language APIs.

The result is a more consistent deployment and operating model across the application stack, with work directed to the data-owning shard and with less duplicated connection-management knowledge between language teams.

Frequently Asked Questions

Why should I use Thick mode instead of Thin mode?

Thin mode is suitable for many Oracle Database applications, but Thick mode uses Oracle Client libraries and the Oracle Call Interface (OCI). For Oracle Globally Distributed Database, it gives Python and Node.js applications access to OCI-based shard-aware connections and advanced connection management. Features such as Fast Application Notification (FAN), Application Continuity, Transparent Application Continuity (TAC), and Transparent Application Failover (TAF) require support from the relevant driver mode and version, database services, and client or pool configuration. Initializing Oracle Client enables Thick mode for Python and Node.js; it does not by itself enable high-availability behavior.

Why do I need sharding keys?

Sharding keys allow Oracle Globally Distributed Database to determine which shard contains the requested data. By supplying sharding keys when acquiring connections, applications are automatically routed to the appropriate shard, minimizing cross-shard communication and improving performance.

Do I need to know which shard my data resides on?

No. Applications simply provide the appropriate sharding key. Oracle Globally Distributed Database handles the routing transparently, allowing developers to focus on application logic rather than shard location.

Do all drivers have the same programming model and capabilities?

They share a consistent core workflow: configure a client and pool, request a connection with routing information when needed, perform work, and release the connection. The APIs are intentionally language-native, and availability of individual features can vary by driver mode, client version, database service, and configuration.

References

End-to-End Agentic AI Observability: Tracing from Agents INTO the Oracle AI Database

Anya Summers — Thu, 30 Jul 2026 14:42:59 +0000

Build one continuous trace across a Spring Boot agent workload, Oracle JDBC, and Oracle AI Database server-side execution.

All source code, configuration, scripts, and supporting docs for the demo are available in the observability folder on GitHub.

Key Takeaways

Oracle AI Database lets the trace continue into database server-side execution instead of stopping at the database edge.
Spring Boot and Micrometer create the application trace context, while Oracle JDBC adds database client spans.
DBMS_OBSERVABILITY exports Oracle AI Database server-side spans to OpenTelemetry-compatible observability tools; this demo uses Jaeger.
Agent metadata, MODULE, ACTION, and CLIENT_IDENTIFIER create a foundation for security and audit correlation.

This walkthrough answers a practical question for agentic applications: can you see one trace that starts with an AI agent request, crosses the Spring Boot and JDBC layers, and continues inside Oracle AI Database? Yes. With Spring Boot, Micrometer, Oracle JDBC observability providers, and DBMS_OBSERVABILITY, the same request can appear in any OpenTelemetry-compatible observability backend as application spans, JDBC spans, and Oracle AI Database server-side spans. This walkthrough uses Jaeger because it is simple to run and easy to inspect, but the same signal can be viewed in any OpenTelemetry-compliant observability tool.

That database-internal span is the important part. Typical database observability stops at the database edge: the application trace shows that JDBC waited on the database, then the user must switch tools and manually investigate inside the database. Oracle AI Database keeps the trace continuous. Developers, DevOps teams, and security reviewers can see how long the database actually took, correlate it to database-side evidence, and use the same trace for performance profiling, troubleshooting, and security context.

Video walkthrough and summary: Tracing from Agents into the Oracle AI Database.

Featured cover diagram: one OpenTelemetry trace from an AI agent request into Oracle AI Database server-side execution.

What We Are Building

The demo app lives in observability/springboot-oracle-db-otel-demo. It is a Spring Boot app with two main endpoints:

Endpoint	Purpose
`GET /trace/roundtrip`	Simple app-to-database trace showing Spring Boot, Micrometer, JDBC, and database server-side spans.
`GET /trace/agent-task/view`	Browser-friendly agentic demo that returns the trace id, agent id, database session metadata, database security context, SQL ID, SQL text, bind values, SQL Monitor preview, `DBMS_XPLAN`, and an embedded Jaeger trace on one page. Jaeger is the demo viewer; the telemetry is OpenTelemetry-compatible.

The intended trace shape is:

Browser or curl
  -> Spring Boot HTTP span
  -> Micrometer observation
  -> Oracle JDBC provider spans
  -> Oracle AI Database DB Server span
  -> HTTPS OTLP endpoint
  -> OpenTelemetry-compatible observability backend (Jaeger in this demo)

Application and JDBC spans flow from the Java process, while database server-side spans flow from Oracle AI Database; both meet in the same OpenTelemetry collector.

The selected database server-side span shows database timing and database-side identity tags in the same trace as the application request.

The demo page keeps the agent task, database session context, database security context, SQL diagnostics, and trace bridge together.

How Do You Set Up and Run the Example?

The demo can run on any Linux environment or VM with enough memory for Oracle Database Free, Jaeger, an HTTPS proxy, and the Spring Boot app. You can also use an Autonomous Database in Oracle Cloud instead of a local database; the important requirements are JDBC connectivity and a database server-side exporter that can reach an HTTPS OTLP endpoint.

A clean OCI Linux VM verification with Oracle Database Free 23.26.2.0 produced the expected Spring Boot, Oracle JDBC, and oracle-db server-side spans without setting hidden KSTRC instance parameters. The normal setup below uses documented DBMS_OBSERVABILITY configuration, a reachable HTTPS OTLP endpoint, network ACLs, and the database disttrc trust wallet.

Install Java 25, Maven, Git, Podman, curl, jq, and OpenSSL.
Run Oracle Database Free on Linux, or provision an Autonomous Database in Oracle Cloud.
Clone the source from github.com/oracle-devrel/oracle-ai-for-sustainable-dev and use the observability folder.
Start Jaeger with OTLP HTTP enabled. In the sample setup, Jaeger receives app and JDBC spans on http://127.0.0.1:4318/v1/traces.
Put a small HTTPS proxy in front of Jaeger's OTLP endpoint for database server-side export. The sample uses NGINX as otel-tls-proxy, forwarding https://otel-tls-proxy:4318/v1/traces to Jaeger's http://oracle-db-otel-jaeger:4318/v1/traces.
Configure DBMS_OBSERVABILITY, network ACLs, and the database wallet/trust path so Oracle AI Database can push server-side spans to the HTTPS OTLP endpoint.
Configure, build, and run the Spring Boot app, then open the agent task view or call the JSON endpoint to generate a trace.

sudo dnf install -y podman git java-25-openjdk java-25-openjdk-devel maven jq curl openssl
export JAVA_HOME=/usr/lib/jvm/java-25-openjdk
export PATH="$JAVA_HOME/bin:$PATH"

git clone https://github.com/oracle-devrel/oracle-ai-for-sustainable-dev.git
cd oracle-ai-for-sustainable-dev/observability/springboot-oracle-db-otel-demo
mvn -DskipTests package

Keep database credentials and runtime endpoints outside the repository:

export DB_URL='jdbc:oracle:thin:@//127.0.0.1:1521/FREEPDB1'
export DB_USERNAME='FINANCIAL'
export DB_PASSWORD='<app-user-password>'
export OTLP_TRACES_ENDPOINT='http://127.0.0.1:4318/v1/traces'
export TRACE_SAMPLE_PROBABILITY=1.0
export ORACLE_JDBC_SERVER_TELEMETRY_TRACES_ENABLED=true
export ORACLE_JDBC_SERVER_TELEMETRY_LOGGING_ENABLED=false
export ORACLE_JDBC_TRACEPARENT_CLIENT_INFO_ENABLED=true
export ORACLE_JDBC_TRACELEVEL_CLIENT_INFO_ENABLED=true

java -jar target/springboot-oracle-db-otel-demo-0.0.1-SNAPSHOT.jar

curl -sS http://127.0.0.1:8080/trace/roundtrip | jq .

curl -sS \
  'http://127.0.0.1:8080/trace/agent-task?agentId=claims-investigator-agent&task=investigate_payment_anomalies' \
  | jq .

For the cohesive browser view, open:

http://127.0.0.1:8080/trace/agent-task/view?agentId=claims-investigator-agent&task=investigate_payment_anomalies

Maven Dependencies and the Oracle JDBC Observability Provider

The app uses Java 25, the Oracle JDBC 23.26 line from Maven Central, the published Maven Central ojdbc-provider-observability artifact, and the Oracle JDBC 17 production dependency.

<properties>
  <java.version>25</java.version>
  <oracle.jdbc.version>23.26.2.0.0</oracle.jdbc.version>
  <oracle-database.version>${oracle.jdbc.version}</oracle-database.version>
  <ojdbc.provider.observability.version>1.1.0</ojdbc.provider.observability.version>
</properties>

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-actuator</artifactId>
</dependency>

<dependency>
  <groupId>io.micrometer</groupId>
  <artifactId>micrometer-tracing-bridge-otel</artifactId>
</dependency>

<dependency>
  <groupId>io.opentelemetry</groupId>
  <artifactId>opentelemetry-exporter-otlp</artifactId>
</dependency>

<dependency>
  <groupId>com.oracle.database.jdbc</groupId>
  <artifactId>ojdbc17-production</artifactId>
  <version>${oracle.jdbc.version}</version>
  <type>pom</type>
</dependency>

<dependency>
  <groupId>com.oracle.database.jdbc</groupId>
  <artifactId>ojdbc-provider-observability</artifactId>
  <version>${ojdbc.provider.observability.version}</version>
</dependency>

Spring Boot Actuator creates the observation infrastructure, Micrometer bridges those observations to OpenTelemetry, and the OTLP exporter sends spans to the collector. The oracle-database.version property keeps Oracle JDBC, UCP, wallet/security, and related transitive artifacts aligned on 23.26.2.0.0.

The Oracle JDBC observability provider implements the JDBC driver TraceEventListener interface and publishes JDBC events into OpenTelemetry. Those events include database round trips and connection behavior, with attributes such as connection id, database operation, database user, tenant, and SQL ID. SQL text and connection details are treated as sensitive and are disabled by default unless explicitly enabled.

How Do the Spring Boot and JDBC Spans Get Created?

The Oracle JDBC OpenTelemetry extensions expect the application to already have an active OpenTelemetry context. They do not create the root application trace by themselves. In this demo, Spring Boot Actuator and Micrometer provide the HTTP and application observations, and the Oracle JDBC provider adds child spans for JDBC driver events such as database round trips. The provider also propagates the trace context to Oracle AI Database so the database can export its server-side span into the same trace.

If you do not want to add application instrumentation directly, use the OpenTelemetry Java agent for zero-code Java instrumentation. The Oracle JDBC extensions can then add database round-trip spans as children of the current application span and propagate that context to the server.

Spring Boot sends application and JDBC spans through OTLP HTTP. The demo sends them to Jaeger, but the same OpenTelemetry signal can be collected by any OpenTelemetry-compatible observability platform. Oracle JDBC provider properties enable the JDBC trace listener and identify the Java service in the collector.

spring:
  application:
    name: springboot-oracle-db-otel-demo
  datasource:
    url: ${DB_URL}
    username: ${DB_USERNAME}
    password: ${DB_PASSWORD}
    oracleucp:
      connection-factory-properties:
        "[oracle.jdbc.provider.traceEventListener]": observability-trace-event-listener-provider
        "[oracle.jdbc.provider.traceEventListener.unique_identifier]": springboot-oracle-db-otel-demo

management:
  otlp:
    tracing:
      endpoint: ${OTLP_TRACES_ENDPOINT:http://localhost:4318/v1/traces}
  tracing:
    sampling:
      probability: 1.0

How Do We Export Database Server-Side Spans?

The database server-side exporter needs a reachable HTTPS OTLP endpoint. The verified Linux demo uses Jaeger, which accepts OTLP HTTP on port 4318, and puts a small HTTPS proxy in front of it for database server-side export. In the sample, that proxy is NGINX with a trusted local certificate:

Oracle AI Database -> https://otel-tls-proxy:4318/v1/traces
otel-tls-proxy -> http://oracle-db-otel-jaeger:4318/v1/traces

Replace Jaeger with another OpenTelemetry-compatible collector or backend by changing the OTLP endpoint. The important requirement is that Oracle AI Database can reach the endpoint over HTTPS for server-side trace export.

Configure the database with DBMS_OBSERVABILITY, grant network ACL access to the user executing the SQL session, and place the trusted wallet where the distributed trace exporter expects it: WALLET_ROOT/<PDB_GUID>/disttrc.

begin
  dbms_observability.add_endpoint(
    endpoint_type => dbms_observability.otel_traces,
    endpoint => 'https://otel-tls-proxy:4318/v1/traces',
    credential_name => null);

  dbms_observability.enable_endpoint('https://otel-tls-proxy:4318/v1/traces');
  dbms_observability.enable_service_option(dbms_observability.capture_traces);
  dbms_observability.enable_service_option(dbms_observability.show_extra_metadata);
  dbms_observability.enable_service(dbms_observability.all_services);
end;
/

A clean verification run on Oracle Database Free 23.26.2.0 showed that the documented DBMS_OBSERVABILITY endpoint setup, the HTTPS OTLP proxy, the network ACL, and the WALLET_ROOT/<PDB_GUID>/disttrc trust wallet were sufficient for database server-side spans. Older local diagnostic runs used hidden KSTRC parameters while debugging export failures, but those settings are not part of the normal demo setup.

How Does JDBC Propagate the Trace Context?

The app uses the published ojdbc-provider-observability dependency. It is currently necessary to force a server telemetry state change before enabling traces, so the driver piggybacks the telemetry state to the database.

EnumSet<OracleConnection.ServerTelemetry> requestedTelemetry =
    EnumSet.of(OracleConnection.ServerTelemetry.Traces);

oracleConnection.setServerTelemetry(
    EnumSet.noneOf(OracleConnection.ServerTelemetry.class));
oracleConnection.setServerTelemetry(requestedTelemetry);

How Do We Make the Database Span Useful?

A generic DB Server span proves that the trace crossed into the database, but the demo makes it more useful by setting database session fields before the SQL runs. The app uses the Oracle JDBC end-to-end metrics API for this metadata, so it does not need a separate PL/SQL round trip just to set MODULE, ACTION, and CLIENT_IDENTIFIER.

String[] metrics = new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];
metrics[OracleConnection.END_TO_END_MODULE_INDEX] = "agent:claims-investigator-agent";
metrics[OracleConnection.END_TO_END_ACTION_INDEX] = "agent-workload-query";
metrics[OracleConnection.END_TO_END_CLIENTID_INDEX] = "traceId=<trace-id>";
metrics[OracleConnection.END_TO_END_ECID_INDEX] = "<trace-id>";
oracleConnection.setEndToEndMetrics(metrics, (short) 0);

In Jaeger, the orange oracle-db / DB Server span then carries database-side evidence such as oracle.db.module, oracle.db.action, oracle.db.session.id, oracle.db.pdb, and oracle.db.query.sql.id.

Some of these names can also appear in client-side JDBC spans because the driver can receive database metadata and the application deliberately sets MODULE, ACTION, and CLIENT_IDENTIFIER. The important difference is provenance and timing: the oracle-db / DB Server span is emitted by Oracle AI Database itself, with database-side duration, response status, session context, and SQL identity attached to the server's view of the work. A client span can time the Java round trip, but it cannot by itself prove the database server's own span timing in the same trace.

How Do We Link the Trace to SQL Diagnostics?

The demo returns a canonical SQL ID from the cursor used for its DBMS_XPLAN and SQL Monitor lookups. Oracle JDBC spans and database-exported spans can also expose oracle.db.query.sql.id, but do not assume that every layer will report the same cursor. A verified run for the screenshots in this article returned one canonical SQL ID on the demo page and a different SQL ID on the selected oracle-db / DB Server span.

Keep the correlation intact with the trace id, database ECID, MODULE, ACTION, and session id. Then use the canonical SQL ID returned by the page for its V$SQL, bind capture, SQL Monitor, and DBMS_XPLAN drill-down. A SQL ID on a particular span remains useful evidence, but it identifies that span's database cursor rather than a guaranteed cross-layer identifier.

Evidence	Where it comes from
Visual trace and database server evidence	Trace id, ECID, module, action, session id, and any layer-specific `oracle.db.query.sql.id` attribute
Full SQL text	Canonical `SQL_ID -> V$SQL.SQL_FULLTEXT`
Captured bind samples	`SQL_ID -> V$SQL_BIND_CAPTURE`
Runtime plan	`DBMS_XPLAN.DISPLAY_CURSOR`, the SQL execution plan with optimizer steps, estimated and actual rows, I/O, memory, and timing
SQL Monitor report	`DBMS_SQL_MONITOR.REPORT_SQL_MONITOR`

The demo also calls DBMS_SQL_MONITOR.BEGIN_OPERATION around the agent task. That call is different from setting JDBC end-to-end metrics: it creates a named database operation for SQL Monitor so the agent task has a database-side operation id and attributes. The trace and SQL-ID bridge still work without it, because the workload SQL uses the MONITOR hint and the app queries V$SQL, V$SQL_BIND_CAPTURE, SQL Monitor, and DBMS_XPLAN by SQL ID. Keeping BEGIN_OPERATION makes the demo stronger by giving SQL Monitor a named server-side operation for the agent task; removing it would simplify privileges and code, but would lose that named database-operation evidence.

SQL_ID lets the demo drill from the trace into full SQL text, bind-sample notes, and SQL Monitor output.

SQL Monitor and DBMS_XPLAN make the traced database span useful for performance profiling and SQL diagnostics.

What Does the Agentic AI Demo Page Show?

The browser endpoint keeps the demo cohesive by returning the agent story, database diagnostics, and Jaeger trace on one page:

http://localhost:8080/trace/agent-task/view?agentId=claims-investigator-agent&task=investigate_payment_anomalies

The page includes:

Agent id and task name.
Trace id and SQL ID bridge.
Database session context, including module/action/client identifier.
Application bind values used by this exact request.
Full SQL Text heading labeled from SQL_ID -> V$SQL.SQL_FULLTEXT.
Captured Bind Samples heading labeled from SQL_ID -> V$SQL_BIND_CAPTURE.
SQL Monitor and DBMS_XPLAN output.
An embedded Jaeger trace loaded after a short delay so spans have time to flush.

What Does Success Look Like?

In the verified Linux environment, Jaeger showed a single trace containing both services: springboot-oracle-db-otel-demo from the Java process and oracle-db from Oracle AI Database server-side export.

{
  "services": [
    "oracle-db",
    "springboot-oracle-db-otel-demo"
  ],
  "ops": [
    "DB Server",
    "Execute query",
    "Fetch a row",
    "http get /trace/agent-task",
    "oracle.demo.agent-database-investigation"
  ]
}

Oracle Database Metrics Exporter (External)

Server-side OpenTelemetry spans answer request-scoped questions: which agent request reached the database, how long that database work took, which SQL ID was involved, and how the database span fits into the same trace as the Java service. For fleet-level and time-series questions, pair that with the external Oracle AI Database Metrics Exporter from oracle/oracle-db-appdev-monitoring.

The exporter is a separate process or container that connects to one or more Oracle AI Database instances and exposes database metrics in standard Prometheus/OpenTelemetry formats. Oracle's exporter project includes default metrics, custom metrics defined in YAML or TOML, support for single instance, clustered, Autonomous, cloud, on-premises, Kubernetes, and container-based databases, plus sample Grafana dashboards.

Signal	Best question	How it complements this demo
Server-side OpenTelemetry trace export	What happened during this specific agent request?	Shows the database span in the same trace as Spring Boot, JDBC, SQL ID, module/action, and agent metadata.
Oracle AI Database Metrics Exporter	What is happening across the database over time?	Adds dashboards and alerts for resource use, sessions, waits, workload health, and custom business or security metrics.

A practical setup is to run both signals into the same observability platform. Keep this demo's OTLP trace path for request-level causality, and deploy the exporter beside the database environment for database metrics:

Spring Boot + Oracle JDBC provider -> OTLP traces -> collector/backend
Oracle AI Database server-side exporter -> HTTPS OTLP traces -> collector/backend
Oracle AI Database Metrics Exporter -> Prometheus/OpenTelemetry metrics -> collector/backend

Used together, traces and metrics give a stronger agentic AI operations story: the trace explains one request all the way into Oracle AI Database, while the metrics exporter shows whether that request happened during broader database pressure, session growth, wait spikes, alert-log events, or workload-specific metric changes.

How Does the Trace Connect to Security and Auditing?

The demo correlates its trace with Oracle AI Database Deep Data Security. Observability shows what the agent did; database security explains why Oracle AI Database allowed or denied it. The trace already carries the join keys needed for that correlation:

trace.traceId identifies the distributed request.
agent.id identifies the AI actor in the application span.
oracle.db.module=agent:<agent-id> identifies the same actor inside Oracle AI Database.
oracle.db.action identifies the database phase.
CLIENT_IDENTIFIER=traceId=<trace-id> gives database security and audit policy a compact correlation value.
oracle.db.query.sql.id bridges the trace to SQL text, SQL Monitor, DBMS_XPLAN, and later audit records.

The sample app now includes a Database Security Context panel backed by a real password-authenticated local Deep Data Security end user. It works without Entra ID, OCI IAM, OAuth tokens, or a JDBC EndUserSecurityContext call. The page correlates the trace with ORA_END_USER_CONTEXT.username, the associated schema, MODULE, ACTION, CLIENT_IDENTIFIER, enabled database roles, effective privileges, the granted data role, the applicable data grant, and V$END_USER_DATA_ROLE for this request. A row-filtering proof runs in that same traced session and shows both the number of rows allowed by the data grant and zero visible rows for other agents.

The panel deliberately separates regular Oracle Database roles from Oracle AI Database Deep Data Security data roles. A role such as SELECT_CATALOG_ROLE is a normal session role and appears in SESSION_ROLES. A DDS data role such as AGENT_CLAIMS_INVESTIGATOR appears in DBA_DATA_ROLES and receives data grants in DBA_DATA_GRANTS. The local setup script observability/sql/setup_local_deep_data_security.sql creates the local end user claims-investigator-agent, assigns AGENT_CLAIMS_INVESTIGATOR with GRANT DATA ROLE, and grants that role SELECT access to only the agent's event-log rows. The FINANCIAL database user remains the owning schema rather than the runtime principal.

A Deep Sec local end user is distinct from a conventional database user. Data roles cannot be granted directly to the FINANCIAL schema account. Direct password login as the local end user establishes the end-user security context in Oracle AI Database and activates the assigned data role without application-side token propagation. The Entra ID and OCI IAM provider/API examples remain the right pattern when an application must propagate externally managed end-user identities through a shared pool user.

A security-aware version can run the same agent workload against protected tables, then show effective database user, enabled roles, policy decisions, rows allowed or filtered, and a denied-operation example. After that, a scoped audit policy can record the same actor, module, action, SQL ID, and trace id so the page shows one story: this agent made this request, Oracle AI Database enforced these policies, and the audit trail recorded it.

The trace itself is not auditing. It is observability: useful for debugging, performance, causality, and visual correlation. Auditing requires durable database records, such as Unified Auditing or Fine-Grained Auditing entries, retained under a security-controlled policy. The best pattern is to use traces to explain the execution path and audit records to prove the security-relevant facts.

A companion design note expands this direction: Agent Security Observability With Oracle AI Database.

How Can This Connect to Model Observability?

Agent observability should not stop at infrastructure traces. The same task can also be correlated with model-level telemetry: prompt, model, tool calls, retrieval steps, latency, token usage, evaluator scores, and final answer quality. Tools such as LangSmith, OpenTelemetry-based GenAI semantic conventions, or enterprise AI observability platforms can capture those model and agent workflow details.

The useful pattern is to carry the same task or trace identifier through the agent workflow, the Java service, Oracle JDBC, and Oracle AI Database. Then one investigation can answer: which agent ran, which prompt or plan it used, which tool call reached the database, how long the database took, which SQL ran, and which database security policies applied. That ties model observability, application observability, database observability, and security review into one storyline.

References

FAQ

Does this replace database auditing?

No. The trace is observability, not an audit trail. Use Oracle AI Database Unified Auditing or Fine-Grained Auditing when you need durable security evidence about users, roles, objects, SQL, and policy decisions.

Do I have to use Jaeger?

No. Jaeger is used here because it is easy to run and inspect, but the demo exports OpenTelemetry traces. The same approach can feed any OpenTelemetry-compatible observability tool that can receive the app/JDBC spans and the Oracle AI Database server-side spans.

What makes this different from ordinary database tracing?

Ordinary application traces often stop at the database edge. Oracle AI Database can export database server-side spans into the same trace, so the database portion is visible without breaking continuity or switching immediately to a separate diagnostic workflow.

Why frame this as an AI agent workload?

Agentic applications need explainability across application and database boundaries. Adding agent id, task, module, action, client identifier, and SQL_ID makes the trace useful for operations, security review, and future audit correlation.

Develop Database-Enforced End-User Auth with Oracle AI Database Deep Data Security and Java

Anya Summers — Thu, 30 Jul 2026 14:34:15 +0000

A practical walkthrough for propagating Microsoft Entra identity through a Spring Boot application via a JDBC connection to Oracle AI Database, which enforces row, column, and cell-level access rules.

Source code and video

All source code for the app is available in the GitHub repo.

Video walkthrough (8 minutes): watch directly on YouTube if the embedded player is unavailable.

Key Takeaways

Pooled JDBC connections can carry end-user identity when application code or ojdbc-provider-spring attaches an Oracle JDBC end-user security context before SQL runs.
The OAuth on-behalf-of flow produces a separate database-access token, so the user token proves the browser user while the database token proves access to Oracle AI Database.
Microsoft Entra app roles such as EMPLOYEES and MANAGERS map to Oracle Deep Data Security data roles, letting the same SQL return different rows based on the signed-in user.
The Java app stays role-agnostic: Oracle AI Database becomes the enforcement point for row, column, and cell-level authorization.

Yes—an enterprise Spring Boot application can keep pooled JDBC connections while Oracle AI Database enforces authorization for each signed-in Microsoft Entra user. The application propagates user and database tokens; Deep Data Security applies declarative row, column, and cell-level rules when SQL executes.

Microsoft Entra identity flows through Spring Boot into Oracle AI Database, where Deep Data Security enforces fine-grained access.

Why This Matters

Most enterprise Java services use a connection pool. That is good engineering: fast, stable, observable, and easy to operate. It also means every request may reach the database through the same application database account.

That is where authorization gets tricky. If Emma and Marvin both use the same pooled database user, the database cannot naturally tell which rows belong to Emma, which columns Marvin may see, or which values a manager may update.

One option is to push that logic into every application query. Another is to create separate data sources or connection pools per user. Neither is a good fit for real enterprise systems.

Oracle Deep Data Security changes the shape of the problem. The application still uses a pooled connection, but it attaches an end-user security context to the JDBC connection before SQL runs. Oracle AI Database evaluates declarative data grants at execution time.

This matters even more for agentic applications. Agents, copilots, and natural-language SQL tools can fan out into many possible data paths. With Deep Data Security, the database remains the final policy enforcement point for fine-grained row, column, and cell-level access.

The important idea: the Spring Boot app does not rewrite SQL or implement authorization logic. It propagates identity and tokens to Oracle AI Database, which enforces authorization policies.

This walkthrough includes the Microsoft Entra setup explicitly instead of only linking to the Microsoft documentation. That part is easy to get subtly wrong, and writing it down forced me to understand it better too.

A good starting point, and the one I extended to create this Java app, is Richard C. Evans's excellent LiveLabs FastLab: Identity-Aware Database Access with Microsoft Entra ID and Oracle Deep Data Security. While I am giving credit due, I will mention that Michael McMahon is the fantastic and friendly developer who wrote the Java support for this Deep Data Security feature.

What We Are Building

The demo is a Spring Boot application that performs four main jobs:

Authenticates a browser user with Microsoft Entra ID.
Requests a database-access token using the OAuth 2.0 on-behalf-of flow.
Attaches both tokens to an Oracle JDBC connection with EndUserSecurityContext.
Runs a normal SQL query against an HR table while Oracle AI Database enforces data grants.

The repo includes two aligned demos, both using Oracle UCP for pooled JDBC connections:

Explicit JDBC API version: application code obtains both tokens and calls OracleConnection.setEndUserSecurityContext(...) and clearEndUserSecurityContext().
ojdbc-provider-spring provider/SPI version: Oracle JDBC reads the current Spring Security context and supplies the end-user security context through the provider SPI.

Runtime Flow

The browser opens GET /deepsec/query.
Spring Security redirects the browser to Microsoft Entra ID.
The user signs in.
Spring Security receives an authorization code and exchanges it for the user's Entra access token.
The backend exchanges that user token for a separate database-access token using OAuth 2.0 on-behalf-of.
Oracle JDBC receives an EndUserSecurityContext containing both tokens, either from explicit application code or through the Spring provider SPI.
Oracle AI Database validates the context, activates mapped data roles from the Entra roles claim, and enforces data grants while SQL runs.
The explicit API app clears the context before returning the connection to the pool; the provider manages that context lifecycle through the SPI.

Outcome: the application remains role-agnostic. Authorization logic is removed from Java code. Oracle AI Database becomes the enforcement point.

Identity and Token Model

Understand the Token Model Before OBO

The on-behalf-of flow is easier to follow if you separate the two tokens early:

Token	What it proves	Audience / important claims
User token	Proves the signed-in browser user to the Spring Boot application. The backend uses it as the assertion for OBO.	Audience is the Spring Boot app. It is requested during browser login.
Database-access token	Proves that the request is addressed to the Oracle AI Database protected resource.	Audience is the database/API app. For this demo, it should be v2 and include the roles claim used for DDS role mapping.

Both tokens matter. The explicit API demo creates the context with EndUserSecurityContext.createWithToken(databaseAccessToken, endUserToken); the provider demo supplies the equivalent context through the JDBC provider SPI.

Real Users and Sample HR Records

Real Entra users

The browser login uses a real Microsoft Entra user. That user receives app-role assignments on the database/API Enterprise Application, such as EMPLOYEES or MANAGERS.

Sample HR records

Emma, Marvin, Charlie, and Dana are sample HR rows in the database, not Entra users. For the employee demo, one HR row is mapped to the value Oracle sees in ORA_END_USER_CONTEXT.username.

Setup order: the remaining sections configure the database, Microsoft Entra, and then the Spring Boot implementations before running the demo.

Database Setup

1. Create an Oracle AI Database if You Do Not Have One

You need an Oracle AI Database with Deep Data Security support. Any 23.26.2 or later database is fine for this walkthrough, including Autonomous Database, Oracle AI Database Free in a container, or another local or cloud database you can reach from the Spring Boot app.

2. Configure Microsoft Entra Trust

Oracle AI Database needs to trust the Entra tenant and the database/API app registration before it can validate the database-access token. Choose the database-admin setup script for your environment:

sql-entraid/01_enable_adb_entra_external_authentication.sql uses DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION for Autonomous Database. Edit its DEFINE values and run it once as ADMIN.
sql-entraid/01_enable_database_free_entra_external_authentication.sql uses IDENTITY_PROVIDER_TYPE and IDENTITY_PROVIDER_CONFIG for Oracle AI Database Free or another non-ADB installation. Run it once as SYSDBA or equivalent.

For non-ADB databases, keep the domain-qualified Application ID URI aligned across the database/API app registration, DEEPSEC_ENTRA_DATABASE_SCOPE, and the optional tnsnames.ora azure_db_app_id_uri value.

-- Autonomous Database, run once as ADMIN after editing DEFINE values:
@security/deepdatasecurity-api-version/sql-entraid/01_enable_adb_entra_external_authentication.sql

-- Oracle AI Database Free container, run once as SYSDBA or equivalent:
@security/deepdatasecurity-api-version/sql-entraid/01_enable_database_free_entra_external_authentication.sql

Verify that the database picked up the identity provider:

SELECT name, value
  FROM v$parameter
 WHERE name IN ('identity_provider_type', 'identity_provider_config');

Grant the application connection-pool user only the privileges it needs to connect and attach an end-user security context:

GRANT CREATE SESSION TO hr_app_user;
GRANT CREATE END USER SECURITY CONTEXT TO hr_app_user;

3. Map Entra App Roles to Deep Data Security Roles

In the database, Microsoft Entra app roles map to Oracle Deep Data Security data roles:

CREATE OR REPLACE DATA ROLE hrapp_employees
  MAPPED TO 'AZURE_ROLE=EMPLOYEES';

CREATE OR REPLACE DATA ROLE hrapp_managers
  MAPPED TO 'AZURE_ROLE=MANAGERS';

If the signed-in user has the Entra app role EMPLOYEES, Oracle activates HRAPP_EMPLOYEES. If that same user later has the Entra app role MANAGERS, or a different signed-in user has MANAGERS, Oracle activates the manager data role. Users can also have multiple app roles.

4. Create the Data Grants

The policy itself lives in SQL. For an employee, the row predicate says a user can see rows where the table username matches ORA_END_USER_CONTEXT.username:

CREATE OR REPLACE DATA GRANT hr.HRAPP_EMPLOYEES_ACCESS
  AS SELECT, UPDATE(phone_number, first_name)
  ON hr.employees
  WHERE upper(user_name) = upper(ORA_END_USER_CONTEXT.username)
  TO hrapp_employees;

For managers, the policy permits access to direct reports while excluding sensitive columns such as ssn:

CREATE OR REPLACE DATA GRANT hr.HRAPP_MANAGER_ACCESS
  AS SELECT (ALL COLUMNS EXCEPT ssn),
     UPDATE (salary, department_id, first_name)
  ON hr.employees
  WHERE manager_id = ORA_END_USER_CONTEXT.HR.EMP_CTX.ID
  TO hrapp_managers;

Notice what is not happening here: the Java service or AI agent is not building custom SQL for employees and managers. The app can issue the same business query, and Oracle filters or restricts the result according to the active end-user security context.

Optional Connection Metadata in tnsnames.ora

This step is optional. The provider demo and its Deep Data Security flow do not require Entra metadata in tnsnames.ora; use it when you want direct JDBC Thin interactive Entra sign-in or centrally managed TNS connection metadata.

A wallet's tnsnames.ora entry can carry that Entra-specific connection metadata. The key additions are token_auth=azure_interactive and azure_db_app_id_uri=api://<DB_APP_CLIENT_ID> inside the security block. A mydb_high alias would look like this:

mydb_high =
  (description=
    (retry_count=20)(retry_delay=3)
    (address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))
    (connect_data=(service_name=asdf_mydb_high.adb.oraclecloud.com))
    (security=
      (ssl_server_dn_match=yes)
      (token_auth=azure_interactive)
      (azure_db_app_id_uri=api://4ad8a6b8-asdf-asdf-asdf-5d14bcc22c8a)))

Use your own service name and database/API app ID URI. token_auth=azure_interactive is useful for direct JDBC Thin testing because the driver can open the browser-based Entra sign-in flow. azure_db_app_id_uri tells the driver which Entra resource represents the database.

The Spring Boot demo itself uses a normal pooled application database user and attaches the Deep Data Security context programmatically, so I keep a separate application alias without token_auth=azure_interactive for the UCP pool.

Entra Setup

All of the identity setup below happens in the Azure Portal under Microsoft Entra ID. You will move between App registrations and Enterprise applications.

App registration responsibilities

Registration / app	Responsibility	Where assignments happen
Spring Boot app registration	Represents the Java web application and OAuth client. It owns the redirect URI, client ID, client secret, and application scope.	It requests permission to call the database/API app on behalf of the user.
Database/API app registration	Represents Oracle AI Database as the protected resource. It defines the database scope and app roles such as EMPLOYEES and MANAGERS.	User or group role assignments happen on the database/API Enterprise Application because that is the resource Oracle validates.

1. Create the Spring Boot app registration

Create a separate app registration for the Spring Boot application. Add a Web redirect URI:

http://localhost:8080/login/oauth2/code/entra

For the provider-version browser UI, also add a SPA redirect URI:

http://localhost:18080

Expose an API for the Spring Boot app too, using:

api://<SPRING_BOOT_APP_CLIENT_ID>

Add a delegated user_impersonation scope. This scope is requested during browser login so the resulting token has the Spring Boot app as its audience. That is required for the on-behalf-of exchange.

Figure 1. Microsoft Entra app registration overview for the DeepSec Sample Spring Boot App with identifiers redacted.

2. Create the database/API app registration

Create an app registration named something like Oracle AI Database DeepSec API. Set its Application ID URI to the default form:

api://<DB_APP_CLIENT_ID>

That default api://... URI is the Autonomous Database path I use in this walkthrough. If you are targeting Oracle AI Database Free or another non-ADB database, use a domain-qualified https://... Application ID URI instead and keep the later database configuration, scope, and tnsnames.ora values aligned.

Add a delegated scope named user_impersonation. Add app roles named EMPLOYEES and MANAGERS. These are the role values Oracle later sees in the database-access token and maps to DDS data roles.

Figure 2. Microsoft Entra enterprise application overview for the Oracle AI Database DeepSec API with identifiers redacted.

Figure 3. Azure Portal Create app role panel for an EMPLOYEES role.

Figure 4. Azure Portal app roles page showing EMPLOYEES and MANAGERS roles.

In the database/API app manifest, set requestedAccessTokenVersion to 2. In older Azure AD Graph and portal references this is often called accessTokenAcceptedVersion. It tells Microsoft Entra which access-token format the resource app supports. For this demo, the database-access token should decode with ver=2.0 and an aud value matching the database/API app client ID.

"requestedAccessTokenVersion": 2

I also added the upn optional claim for access tokens so the end-user context has a stable username value to compare against the sample hr.employees.user_name column.

Figure 5. Azure Portal token configuration blade adding the upn optional claim to access tokens.

3. Connect the two registrations

In the Spring Boot app registration, open API permissions, add a permission from My APIs, choose the database/API registration, and grant its delegated user_impersonation scope.

Figure 6. Azure Portal API permissions page showing Microsoft Graph User.Read and the Oracle AI Database DeepSec API user_impersonation scope.

Then go to Enterprise applications, open the database/API enterprise application, and assign users or groups to EMPLOYEES or MANAGERS. For database enforcement, the important assignment is on the database/API enterprise application, because that is the resource for the database-access token Oracle validates.

Figure 7. Azure Portal Enterprise application Users and groups page showing a user assigned to the EMPLOYEES role.

The Spring Boot YAML in the next section binds these tenant, client, secret, database-scope, application-scope, and browser-client values from environment variables. The provider-version browser UI can use the same client ID or a separate public-client app registration.

The Spring Boot Code

Oracle UCP Connection Pooling

Both demos let Spring Boot create Oracle UCP from spring.datasource properties. The explicit API demo sets and clears EndUserSecurityContext in service code; the provider demo adds its provider settings through spring.datasource.oracleucp.connection-properties. The Java side uses the 23.26.2.0.0 JDBC/UCP release, so keep the database and driver at that level or later when troubleshooting identity behavior.

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
  <groupId>com.oracle.database.jdbc</groupId>
  <artifactId>ojdbc17-production</artifactId>
  <version>${oracle.jdbc.version}</version>
  <type>pom</type>
</dependency>

The Oracle production dependency POM keeps the JDK 17 JDBC driver, UCP, wallet support, and related companion artifacts on the same release line.

Explicit API Version

OAuth2 client and database-token configuration

The explicit API application uses Spring Security OAuth2 login for the browser user and a separate deepsec.entra-id block for the on-behalf-of database-token exchange:

spring:
  security:
    oauth2:
      client:
        registration:
          entra:
            client-id: ${DEEPSEC_ENTRA_CLIENT_ID:}
            client-secret: ${DEEPSEC_ENTRA_CLIENT_SECRET:}
            client-authentication-method: client_secret_post
            authorization-grant-type: authorization_code
            redirect-uri: ${DEEPSEC_ENTRA_REDIRECT_URI:{baseUrl}/login/oauth2/code/{registrationId}}
            scope: ${DEEPSEC_ENTRA_APPLICATION_SCOPE:openid,profile,email}
        provider:
          entra:
            authorization-uri: https://login.microsoftonline.com/${DEEPSEC_ENTRA_TENANT_ID:}/oauth2/v2.0/authorize
            token-uri: https://login.microsoftonline.com/${DEEPSEC_ENTRA_TENANT_ID:}/oauth2/v2.0/token
            jwk-set-uri: https://login.microsoftonline.com/${DEEPSEC_ENTRA_TENANT_ID:}/discovery/v2.0/keys
            user-name-attribute: sub

deepsec:
  entra-id:
    tenant-id: ${DEEPSEC_ENTRA_TENANT_ID:}
    client-id: ${DEEPSEC_ENTRA_CLIENT_ID:}
    client-secret: ${DEEPSEC_ENTRA_CLIENT_SECRET:}
    database-scope: ${DEEPSEC_ENTRA_DATABASE_SCOPE:}

Explicit security-context lifecycle

The service first receives the browser user's Entra token from Spring Security. Then it requests a database-access token with the OAuth 2.0 on-behalf-of flow:

MultiValueMap<String, String> form = new LinkedMultiValueMap<>();
form.add("grant_type", "urn:ietf:params:oauth:grant-type:jwt-bearer");
form.add("client_id", entraId.getClientId());
form.add("client_secret", entraId.getClientSecret());
form.add("scope", entraId.getDatabaseScope());
form.add("assertion", endUserToken);
form.add("requested_token_use", "on_behalf_of");

Then the app attaches the Deep Data Security context to the Oracle JDBC connection. The method names are on oracle.jdbc.OracleConnection. The important calls are setEndUserSecurityContext(...) before SQL and clearEndUserSecurityContext() before returning the connection to the pool.

OracleConnection oracleConnection =
  connection instanceof OracleConnection
    ? (OracleConnection) connection
    : connection.unwrap(OracleConnection.class);

EndUserSecurityContext context =
    EndUserSecurityContext.createWithToken(databaseAccessToken, endUserToken);

oracleConnection.setEndUserSecurityContext(context);
try {
    return runQuery(connection, sql);
}
finally {
    oracleConnection.clearEndUserSecurityContext();
}

Connection-pool hygiene: the finally block is not cosmetic. In a pooled application, clearing the context before returning a connection to the pool is mandatory.

Provider Version: ojdbc-provider-spring

The explicit API version is useful because it shows the moving parts: Spring obtains the signed-in user token, the app exchanges it for a database-access token, and application code calls OracleConnection.setEndUserSecurityContext(...). The provider version keeps the same database policy model but changes where that context is assembled.

In the provider version, the protected HTTP endpoint is a Spring Security OAuth2 resource-server endpoint. A request reaches /deepsec/query with an OAuth2 bearer token. Spring validates the token and stores the authentication in the request security context. When the service borrows a JDBC connection, ojdbc-provider-spring reads that Spring Security context, obtains the database-access token through the configured OAuth2 client registration, and supplies the EndUserSecurityContext through the Oracle JDBC provider SPI.

Compared with the API version, the service no longer calls OracleConnection.setEndUserSecurityContext(...) or clearEndUserSecurityContext(). The Deep Data Security context setup moves from application service code into JDBC/Spring provider configuration.

Provider dependency

The provider app adds the Oracle JDBC Spring provider dependency alongside the Oracle JDBC driver and wallet/security jars:

<dependency>
  <groupId>com.oracle.database.jdbc</groupId>
  <artifactId>ojdbc-provider-spring</artifactId>
  <version>${ojdbc.provider.version}</version>
</dependency>

Provider configuration

Spring Boot 3.3 creates the Oracle UCP PoolDataSource directly from configuration, so the provider app no longer needs a dedicated UcpDataSourceConfiguration class. Set the datasource type to UCP and put the JDBC provider settings under spring.datasource.oracleucp.connection-properties. The registrationId value must match the Spring OAuth2 client registration that can obtain a database-access token for Oracle AI Database:

spring:
  datasource:
    type: oracle.ucp.jdbc.PoolDataSource
    url: ${DEEPSEC_JDBC_URL:}
    username: ${DEEPSEC_USERNAME:}
    password: ${DEEPSEC_PASSWORD:}
    driver-class-name: oracle.jdbc.OracleDriver
    oracleucp:
      connection-factory-class-name: oracle.jdbc.datasource.impl.OracleDataSource
      connection-pool-name: ${DEEPSEC_UCP_POOL_NAME:DeepDataSecurityProviderUcpPool}
      initial-pool-size: ${DEEPSEC_UCP_INITIAL_POOL_SIZE:1}
      min-pool-size: ${DEEPSEC_UCP_MIN_POOL_SIZE:1}
      max-pool-size: ${DEEPSEC_POOL_SIZE:4}
      connection-properties:
        "oracle.jdbc.provider.endUserSecurityContext": ojdbc-provider-spring-end-user-security-context
        "oracle.jdbc.provider.endUserSecurityContext.registrationId": entra
  security:
    oauth2:
      client:
        registration:
          entra:
            client-name: entra
            client-id: ${DEEPSEC_ENTRA_CLIENT_ID:}
            client-secret: ${DEEPSEC_ENTRA_CLIENT_SECRET:}
            client-authentication-method: client_secret_post
            authorization-grant-type: ${DEEPSEC_ENTRA_DATABASE_GRANT_TYPE:client_credentials}
            scope: ${DEEPSEC_ENTRA_DATABASE_SCOPE:}
        provider:
          entra:
            authorization-uri: https://login.microsoftonline.com/${DEEPSEC_ENTRA_TENANT_ID:}/oauth2/v2.0/authorize
            token-uri: https://login.microsoftonline.com/${DEEPSEC_ENTRA_TENANT_ID:}/oauth2/v2.0/token

deepsec:
  jwt:
    trusted-issuers: ${DEEPSEC_ENTRA_JWT_TRUSTED_ISSUERS:https://sts.windows.net/${DEEPSEC_ENTRA_TENANT_ID:}/,https://login.microsoftonline.com/${DEEPSEC_ENTRA_TENANT_ID:}/v2.0}
  browser:
    enabled: ${DEEPSEC_BROWSER_ENABLED:true}
    auth-provider: entraid
    tenant-id: ${DEEPSEC_ENTRA_TENANT_ID:}
    client-id: ${DEEPSEC_ENTRA_BROWSER_CLIENT_ID:${DEEPSEC_ENTRA_CLIENT_ID:}}
    scope: ${DEEPSEC_ENTRA_APPLICATION_SCOPE:}

Spring Boot binds the datasource portion to UCP and the OAuth2 portion to Spring Security. Oracle JDBC reads the provider properties when a pooled connection is borrowed, and the provider uses the matching OAuth2 client registration to request the database-access token. The deepsec.jwt values configure trusted bearer-token issuers, while deepsec.browser exposes only the non-secret values needed by the sample UI. Optional dataRoles and endUserContextAttributes properties can be added to the same connection-properties map.

Resource-server security configuration

The provider app keeps /deepsec/query and /deepsec/whoami protected while allowing the browser page and setup endpoints to load. It also trusts both common Entra issuer forms for the configured tenant, which is helpful when one app registration issues v1 access tokens while another flow emits v2 tokens:

http
  .authorizeHttpRequests(authorize -> authorize
      .requestMatchers(
          "/", "/index.html", "/app.js", "/styles.css",
          "/deepsec/browser-config", "/deepsec/health", "/deepsec/policies")
      .permitAll()
      .anyRequest().authenticated())
  .oauth2Client(Customizer.withDefaults())
  .oauth2ResourceServer(oauth2 -> oauth2
      .authenticationManagerResolver(jwtAuthenticationManagerResolver));

return JwtIssuerAuthenticationManagerResolver.fromTrustedIssuers(
    "https://sts.windows.net/" + tenantId + "/",
    "https://login.microsoftonline.com/" + tenantId + "/v2.0");

Service code becomes ordinary JDBC

With the provider enabled, the service code is intentionally boring. It checks out a connection and executes SQL. The provider supplies the end-user security context when JDBC needs it:

try (Connection connection = dataSource.getConnection();
     Statement statement = connection.createStatement()) {
    runSessionInit(statement);
    try (ResultSet resultSet = statement.executeQuery(sql)) {
        // read rows
    }
}

In my demo environment I disable parallel query to avoid a database-side context handler issue with parallel execution:

DEEPSEC_SESSION_INIT_SQL="alter session disable parallel query"

If your database policy package works under parallel query, set that value to empty.

Browser-friendly provider flow

A pure resource-server endpoint still needs an Authorization: Bearer header. To make the demo natural in a browser without adding a separate frontend build, the provider version includes a tiny same-origin page. It uses MSAL to sign in with Entra, obtains an access token for the Spring Boot API scope, and calls the protected endpoint with that bearer token:

const token = await getAccessToken();
const response = await fetch("/deepsec/query", {
  headers: { Authorization: `Bearer ${token}` }
});

The backend exposes only non-secret browser configuration at /deepsec/browser-config: tenant ID, browser client ID, and scope. Client secrets and database passwords stay server-side in .env or a secret manager.

Provider runtime flow

The browser opens http://localhost:18080/.
The page signs the user in with Entra and obtains an access token for api://<SPRING_BOOT_APP_CLIENT_ID>/user_impersonation.
The page calls /deepsec/query with Authorization: Bearer <access-token>.
Spring Security validates the token and creates the authenticated request context.
ojdbc-provider-spring reads that context, obtains the database-access token with the entra OAuth2 client registration, and supplies the EndUserSecurityContext to Oracle JDBC.
Oracle AI Database validates the context, activates mapped data roles, and enforces the same data grants shown earlier.

Running the Demo

The repository now has two runnable implementations. Use the API version when you want to teach the explicit OBO and OracleConnection API calls. Use the provider version when you want to show how a Spring resource-server endpoint can let ojdbc-provider-spring supply the Deep Data Security context.

Run the explicit API version

The API version uses Spring oauth2Login. Opening /deepsec/query in the browser redirects to Entra, then the controller receives an OAuth2AuthorizedClient and the service calls the Oracle JDBC Deep Data Security API directly.

cd security/deepdatasecurity-api-version
cp .env_example .env
vi .env
./build.sh
./run.sh

# Then open:
http://localhost:8080/deepsec/query

# The employee and manager results are shown in the role-change demonstration below.

Run the provider/SPI version

The provider version is browser-first in the sample repo, but the backend remains a bearer-token resource server. Add http://localhost:18080 as a SPA redirect URI on the Entra app registration used by DEEPSEC_ENTRA_BROWSER_CLIENT_ID, then run:

cd security/deepdatasecurity-provider-version
cp .env_example .env
vi .env
./build_and_run.sh

# Then open:
http://localhost:18080/

# Click Sign in, then Run query.
# Expected employee result in the page:
{"query":{"rows":["3:Emma Baker"]}}

For curl or Postman, request an Entra access token for DEEPSEC_ENTRA_APPLICATION_SCOPE and send it in the Authorization header:

curl -H "Authorization: Bearer ${ACCESS_TOKEN}" \
  http://localhost:18080/deepsec/query

There is still no app-side data-role switch in either flow. Oracle AI Database reads the Entra roles claim and activates the mapped DDS roles automatically.

The Nice Part: Changing Roles Without Changing Code

I run the same endpoint twice to make the behavior obvious. There are two ways to demonstrate the change:

Two-user path: sign in as two different Entra users with different app-role assignments and matching employee or manager rows.
One-user path: keep one real demo user, change that user's database/API Enterprise Application role assignment, obtain a fresh token, and map the same Entra username to different sample HR rows.

In the video I use my Paul Parkinson Entra user for the one-user path. In both paths, the user identity and role claims travel together in the security context.

Employee result

First, the signed-in Entra user has the EMPLOYEES app role, and one sample employee row is mapped to that user's ORA_END_USER_CONTEXT.username. The app executes the business SQL and Oracle returns only that employee row:

{"rows":["3:Emma Baker"]}

Manager result

For the one-user path, use this sequence:

Assign that same real Entra user to the MANAGERS app role in the database/API enterprise application.
Restart or otherwise force a fresh login so the token contains the updated role set.
Map that user's username to a sample manager row for the one-user demo.
Run the same GET /deepsec/query endpoint again.

The fresh token now carries MANAGERS instead of, or in addition to, EMPLOYEES. Oracle AI Database sees that role set during context creation, activates the matching DDS data role, and enforces the manager policy. For the one-user demo, mapping the same real Entra username to the sample manager row lets the manager end-user context resolve to the manager employee ID.

The request is still GET /deepsec/query. The endpoint, Java code, connection pool, and SQL statement stay the same. The result changes because Oracle applies the manager data grant, while restricted columns remain protected by the database.

In the sample HR data used in the walkthrough, the manager row has direct reports Emma, Charlie, and Dana. So the same /deepsec/query call moves from a single employee row to the manager's direct-report set:

{"rows":["3:Emma Baker","4:Charlie Davis","5:Dana Lee"]}

Helpful Tips

/deepsec/whoami is a setup/debug aid, not part of the authorization flow. It shows the exact username value Oracle sees in the end-user context. If your sample rows do not already match that value, use it to update one employee row for a quick demo:

http://localhost:8080/deepsec/whoami

UPDATE hr.employees
   SET user_name = '<value returned by /deepsec/whoami>'
 WHERE employee_id = 101;
COMMIT;

Empty rows can be success. If /deepsec/query returns {"rows":[]} with no exception, the DDS policy probably filtered everything. Make sure hr.employees.user_name matches the end-user context username.
Use schema-qualified table names. With an active end-user context, unqualified employees can resolve unexpectedly. Use hr.employees.
Make the database/API token v2. If Oracle reports an invalid database-access token, decode it and check ver=2.0 and an aud value matching the database/API app ID.
Restart and use a fresh browser session after scope changes. The app reads .env at startup, and browsers cache login sessions aggressively.

Productionization Checklist

Store the Entra client secret in a secret manager, not in a local file.
Validate expected issuer, audience, scopes, tenant, and role claims explicitly.
Manage data roles and data grants with versioned database migrations.
Add integration tests that run the same query as employee and manager identities.
Audit end-user contexts and data grant behavior in database monitoring.
Always clear the end-user security context before returning a pooled connection.

References

FAQ

What problem does this pattern solve?

It lets a Java service keep the operational benefits of a pooled application database user while still allowing Oracle AI Database to enforce access for the signed-in end user.

Why are there two tokens?

The user token proves who signed in to the Spring Boot app. The database-access token is addressed to the Oracle AI Database resource and carries the claims Oracle validates for role mapping and data grants.

Do employees and managers need different Java code?

No. The same endpoint, SQL statement, and connection pool are used. Different results come from Entra role claims mapped to Oracle Deep Data Security data roles.

Where should Entra role assignments be made?

Assign users or groups on the database/API Enterprise Application, because that application represents the Oracle AI Database resource for the database-access token.

Does the provider version still require a bearer token?

Yes. The backend endpoint is a resource-server endpoint. The browser sample obtains the token with MSAL and sends it automatically, while curl or Postman must send the Authorization header explicitly.

Which version should I show first?

Show the API version first when the audience needs to see the token exchange and EndUserSecurityContext calls. Show the provider version when the audience is focused on Spring configuration and reducing database-security code in services.

DEV Community: Anya Summers

We just launched a new course on how to Build Adaptive AI Agents

Key Takeaways

Why does your agent keep paying the same bill twice?

Watch it happen on something smaller

Which kind of memory is your agent actually missing?

Frequently Asked Questions

Resources

Build a Controlled Cursor MCP Workflow with Oracle AI Database

Key Takeaways

Why This Architecture Is Useful for Developers

The Two Execution Loops

Reproducing the SQLcl MCP and Cursor CLI Workflow

What a Successful Notebook Run Shows

Simulated Teaching Data, Not Live Cursor Telemetry

Why Put Application Memory Records in Oracle AI Database, Not Just Outputs

Where Oracle AI Agent Memory Fits

Store and Search: What a Realistic Memory Looks Like

Vector Search, Native VECTOR, and Demo Embeddings

Where LangChain Adds Value

How to Move This from Demo to Production

What to Check When the Workflow Fails

Why the Hybrid Model Is Usually the Best Long-Term Design

Conclusion

Frequently Asked Questions

Companion Troubleshooting Appendix

Resources

Hybrid Retrieval for Agent Memory: Vector, Lexical, and Metadata Together

Key takeaways

Why your agent feels slightly off

Vector, lexical, metadata: three retrieval modes, three failure modes

The hybrid retrieval pipeline, stage by stage

The hybrid query in one SQL statement

The polyglot version, for contrast

Reranking: precision after candidate generation

Experiment scope

Integrating the pipeline with LangChain and LangGraph

Evaluating retrieval quality

Beyond hybrid: where agentic retrieval comes in

Where this leaves you

Frequently asked questions

How do I implement hybrid search for AI agent memory?

When should hybrid search be used instead of vector search alone?

How do vector search, lexical search, and metadata filtering work together?

How do you evaluate whether hybrid retrieval improves AI agent memory?

What we learned about letting agents into a production database

Why does an agent need a database at all?

What actually goes wrong when an agent talks to a database?

What if the agent did not write the query at all?

Who is the agent, and what is it allowed to see?

Four ways to connect

Come and talk to us in Amsterdam

References

How to Build a REST API for an AI Application

Key takeaways

What will you design and verify?

What are the best practices for building a REST API for an AI application?

How do you design a thin REST API layer for an AI app?

Which endpoints should a chatbot REST API expose in FastAPI?

Why Oracle AI Database for the production path?

Proof cases: apply the API pattern to a real AI developer job

Job: ship an interactive AI experience without turning the route into the agent

Job: make a retrieval-backed AI API answer from the right evidence

Job: expose enterprise data without bypassing identity and access policy

How do you keep your public API independent of the AI provider?

How do FastAPI /docs and /openapi.json help during development?

Should AI inference be synchronous or asynchronous?

How do I add streaming responses for chat in FastAPI?

How do I build an AI REST API with FastAPI?

Step 1: Define a bounded request schema

Step 2: Submit work and return 202 Accepted

How would I persist AI jobs with Oracle AI Database in production?

Step 3: Persist prediction status in Oracle AI Database

Step 4: Let a worker handle model inference

How do I build a minimal FastAPI chat endpoint using the OpenAI SDK?

How do I connect a REST API to the OpenAI Responses API?

How should you store and load the OpenAI API key securely?

How do I authenticate and secure an AI REST API?

How do you store conversations and enforce user ownership checks?

How do I add user authentication with FastAPI Depends?

How do FastAPI `/docs` and `/openapi.json` help during development?

Step 2: Submit work and return `202 Accepted`

How do I add user authentication with FastAPI `Depends`?

Why put application memory records in Oracle AI Database, not just outputs 