DEV Community: Oracle Developers

Develop A2UI and MCP Apps with Oracle AI Database and the Java MCP Toolkit running in Google Gemini Enterprise App, ChatGPT, and Claude

Anya Summers — Wed, 09 Sep 2026 14:48:33 +0000

Build dynamic agent experiences with trusted native A2UI components or portable, sandboxed MCP Apps while keeping rendering, interaction, and execution boundaries explicit.

Source code for this supply-chain reference application

Key Takeaways

A planner can review a database-calculated recommendation while Oracle AI Database retains final transaction and audit authority.
Oracle AI Database provides governed data services that the Oracle Database MCP Java Toolkit exposes as reusable, bounded operations, helping AI agents query business data safely through MCP.
A2UI is a runtime protocol, not a code generator: an agent describes intent as validated JSON and the host renders approved native components from its own design system.
Rich agent tasks need interfaces beyond chat. A2UI provides safe, consistent composition; MCP Apps provide portable custom web experiences through sandboxed ui:// resources.
Both UI paths can collect explicit human approval while the application and database retain execution authority.

How can an AI agent build a useful interface during a task while keeping data access and execution governed? This runnable supply-chain reference separates each responsibility:

A planner reviews a database-calculated recommendation in a task-specific interface, while Oracle AI Database retains final authority over validation, locking, the transaction, and its audit record.

MCP Apps and A2UI address the frontend version of the vibe-coding risk seen in tools such as Lovable and v0, where arbitrary generated UI code needs boundaries, just as the Oracle Database MCP Java Toolkit addresses backend MCP risk by exposing allowlisted, governed database tools instead of arbitrary server capabilities.

Together, MCP Toolkit for backend tools, A2A for agent-to-agent or host-to-agent communication, and A2UI or MCP Apps for frontend interaction form a practical full-stack pattern for safer agentic AI applications.

Oracle AI Database calculates feasible source-to-target recommendations and remains the authoritative data, transaction, and audit layer.
Oracle Database MCP Java Toolkit exposes reusable, allowlisted business tools instead of unrestricted SQL or database write access.
A2UI lets an agent dynamically build and update an interface by sending a structured JSON recipe that the client securely renders with pre-approved native components.
MCP Apps package a developer-built HTML and JavaScript application as a sandboxed ui:// resource for a compatible conversational application.
AG-UI, when a custom frontend needs it, streams agent events, state, text, and tool lifecycles between the service and that frontend.
A2A connects a conversational application such as Gemini Enterprise to the independently deployed A2UI-producing agent.

The MCP Toolkit approach is well suited to this architecture because the same governed tool contracts can be reused by multiple agents, custom frontends, and MCP-compatible clients.

All Java, JavaScript, TypeScript, SQL, YAML, security notes, and setup instructions are in the project folder on GitHub.

Video walkthrough: Watch the complete A2UI, MCP Apps, Oracle Database MCP Java Toolkit, and Oracle AI Database demonstration on YouTube.

Code and config deep dive: Watch the companion source, configuration, and runtime walkthrough on YouTube.

A2UI version compatibility: the standalone browser adapter emits A2UI v0.9.1 in AG-UI events, while the checked-in Gemini Enterprise adapter emits A2UI v0.8 DataParts over A2A. These are separate host adapters over the same service, not mixed versions on one UI surface. Each host validates the version and catalog it advertises. Gemini Enterprise currently documents both A2UI v0.8 and v0.9 support.

Why Agent-Generated UI Needs a Runtime Contract

Agents increasingly need forms, comparisons, visualizations, and approval controls, but production applications should not execute arbitrary model-generated frontend code. Fixed widgets are safer yet too rigid for task-specific interfaces.

A2UI closes that gap with declarative JSON for components, layout, data bindings, and actions. A host is the user-facing application. In this article, the standalone browser and Gemini Enterprise demonstrate host-rendered A2UI, while Gemini Enterprise, ChatGPT, and Claude demonstrate MCP Apps. An A2UI host's component catalog is the allowlist of native controls it knows how to validate and render. The host preserves its design system and accessibility while rejecting unsupported capabilities.

The result is a UI that can appear and update during a conversation without executing agent-generated frontend code. The host controls rendering, and Oracle AI Database retains authority over the transaction.

Which Protocols Connect Which Responsibility?

The host examples in this article are the standalone browser, Gemini Enterprise, ChatGPT, and Claude. Each supports a different combination of transport and UI capabilities.

AG-UI is not required merely because an application uses A2UI or MCP Apps. AG-UI is an optional event transport for a custom frontend; A2UI is a declarative JSON UI specification; and MCP Apps package a developer-built web application. A design can use any one of them or combine them when their separate responsibilities are needed.

Layer	Primary job	In this application
AG-UI	Optional transport pipeline for tokens, events, tool lifecycles, and shared state between an agent backend and a custom frontend.	Carries run lifecycle, text, MCP tool activity, A2UI messages, state, and errors over SSE.
A2UI	Declarative, streaming JSON recipe that lets an agent dynamically build and update a UI from the host's pre-approved native component catalog.	Defines inventory cards, notes, approval, and cancel controls.
MCP Apps	Developer-built HTML and JavaScript application returned as a `ui://` resource for a host sandbox.	Provides the portable recommendation dashboard for compatible conversational hosts.
MCP	Standardizes discovery and invocation of tools and resources.	Connects the service to five governed Oracle Database operations.
A2A	Standardizes agent discovery, requests, and results.	Carries host requests and A2UI v0.8 DataParts.
Oracle Database MCP Java Toolkit	Expose reusable Oracle Database capabilities.	Runs the exact YAML-defined allowlist of reads and procedure-backed writes.
Oracle AI Database	Remain the trusted data and execution layer.	Computes recommendations and enforces locking, transactions, authorization, and auditing.

GitHub Repos Map

Directory	What it contains
`agent-service/`	Java orchestration, Streamable HTTP MCP client, approval state, AG-UI events, REST endpoints, and A2UI payloads.
`oracle-db-mcp-toolkit/`	Standalone Toolkit startup, TLS setup, YAML tool definitions, contracts, and pinned-runtime support.
`database/`	Sample schema, inventory data, recommendation view, transfer audit, and approval procedure.
`web-client/`	Standalone browser that consumes AG-UI events and renders allowlisted A2UI components.
`gemini-enterprise-a2a/`	A2A endpoint, generated agent card, Java-service adapter, and A2UI v0.8 response builder.
`mcp-app/`	TypeScript MCP server, `ui://` resource, sandboxed dashboard, and host-bridge actions.
`deploy/gcp/`	macOS/Linux deployment scripts and Cloud Build configuration for hosted adapters and MCP Apps.
`scripts/`, `docs/`, and `images/`	Smoke tests, setup guidance, sequence diagrams, and sanitized product captures.

From Stockout Analysis to an Inventory-Transfer Decision

Consider the request: “Show inventory at risk of stockout, explain the demand and safety-stock factors, and let me approve a feasible transfer from another location.” A useful supply-chain AI application must expose more than a prose answer:

the current run status and governed database tool being invoked;
a bounded list of products and target locations at risk;
on-hand, reserved, inbound, forecast, safety-stock, and shortage quantities;
a feasible source location, transfer quantity, transit time, and cost;
an explicit approval boundary with an operator note; and
a transfer ID and auditable completion state.

The agent can decide when to request recommendations and how to explain them. It cannot choose an arbitrary transfer quantity, execute generated SQL, create new UI capabilities, or substitute a different source and target after approval. These enforced boundaries provide application and database guardrails that reduce manipulation, unauthorized operations, and unintended writes.

How the Shared Architecture Connects

A2UI does not mandate a transport. The standalone browser example receives A2UI v0.9.1 inside AG-UI CUSTOM events, while the Gemini Enterprise Apps example receives A2UI v0.8 DataParts over A2A. The MCP Apps use MCP Streamable HTTP and a host bridge instead of either transport used to carry A2UI.

A host adapter translates the shared domain result into the contract supported by a particular host. gemini-enterprise-a2a/main.py adapts the result to A2A/A2UI for Gemini Enterprise; mcp-app/server.ts exposes the MCP App used by ChatGPT, Claude, and Gemini Enterprise; and web-client/app.js consumes AG-UI/A2UI in the standalone browser. These adapters do not duplicate the Oracle query or approval policy.

Do MCP Apps use A2UI? Not automatically. This reference application implements two alternative UI paths over the same governed service: a host-rendered A2UI path and a sandboxed MCP App path. The official A2UI project also defines optional composition patterns for embedding an MCP App as a custom component in an A2UI surface and for bundling an A2UI renderer inside an MCP App. Those patterns require explicit renderer, catalog, bridge, and sandbox integration; using either protocol alone does not activate the other.

Keep the boundaries clear: the Oracle Database MCP Java Toolkit is database-facing. Orchestration and approval policy belong in the agent service; A2UI rendering belongs in the host or web client; rich MCP App content belongs behind the host bridge.

Expose Bounded Supply-Chain Tools with the Oracle Database MCP Java Toolkit

Toolkit supports Deep Data Security: database-enforced row and column authorization.

The Toolkit configuration enables exactly five business operations:

find-stockout-transfer-recommendations returns bounded recommendations above a risk threshold.
get-stockout-transfer-details retrieves one current recommendation by its compound ID.
reserve-inventory-transfer-id obtains the next audit identifier.
approve-inventory-transfer calls the locked, procedure-backed write.
count-inventory-transfers verifies the resulting audit record.

Terminology: “bounded” or “narrow” is descriptive shorthand here, not a formal MCP or Oracle Toolkit tool category. It means a purpose-built, allowlisted operation with constrained inputs and outputs, bind variables, explicit validation, and least-privileged database access. Those properties make behavior safer, more deterministic, and more auditable than unrestricted SQL; they do not make a tool absolutely safe.

The following toolset and bind-variable query are defined in oracle-db-mcp-toolkit/config/tools.yaml:

toolsets:
  supply-chain-exchange:
    - find-stockout-transfer-recommendations
    - get-stockout-transfer-details
    - reserve-inventory-transfer-id
    - approve-inventory-transfer
    - count-inventory-transfers

tools:
  find-stockout-transfer-recommendations:
    dataSource: financial-db
    parameters:
      - name: minimumStockoutRisk
        type: number
        required: true
      - name: maximumRows
        type: integer
        required: true
    statement: >-
      SELECT * FROM (
        SELECT *
          FROM stockout_transfer_recommendation_v
         WHERE stockout_risk_score >= :minimumStockoutRisk
         ORDER BY stockout_risk_score DESC
      ) WHERE ROWNUM <= :maximumRows

McpToolkitSupplyChainGateway connects to the independently running Toolkit through authenticated Streamable HTTP, completes MCP initialization, verifies the server identity, and requires the exact supply-chain-exchange allowlist. Built-in read-query, write-query, table, and administration tools are never enabled.

The following excerpt from agent-service/src/main/java/com/oracle/demo/interactiveai/McpToolkitSupplyChainGateway.java validates input and invokes the named MCP tool rather than accepting generated SQL:

InputValidation.minimumStockoutRisk(minimumStockoutRisk);
InputValidation.maximumRows(maximumRows);

return rows(client.callTool(
    "find-stockout-transfer-recommendations",
    Map.of(
        "minimumStockoutRisk", minimumStockoutRisk,
        "maximumRows", maximumRows)))
    .stream()
    .map(row -> new TransferRecommendation(/* typed columns */))
    .toList();

The same file fails closed if discovery returns anything other than the expected tool names:

Set<String> available = client.listTools();
if (!available.equals(requiredTools)) {
  throw new IllegalStateException(
      "Oracle Database MCP Toolkit tool allowlist mismatch");
}

Choose A2UI Consistency or MCP App Flexibility

UI contract	How it works	Security method	Advantage	Tradeoff
A2UI	The host renders the agent's validated declarative recipe with trusted native components.	The host validates the payload and permits only components, properties, and actions in its approved catalog.	Native appearance, accessibility, predictable behavior, and a host-controlled catalog.	The agent can compose supported controls but cannot invent a new component or arbitrary JavaScript.
MCP Apps	An agent or MCP server returns an interactive HTML/JavaScript resource through a `ui://` pointer; the host renders it in a sandboxed iframe or mobile WebView.	The host isolates the app with a sandbox, CSP, permissions policy, and validated bridge; the MCP server still authenticates and authorizes every tool call.	Custom charts, maps, canvases, and complete mini-applications can travel with the MCP server without being prebuilt into every host frontend.	The sandbox adds overhead, Content Security Policy constraints, and a visual boundary that may not perfectly match the host's native interface.

The practical choice is host-native catalog consistency or developer-supplied presentation flexibility. Both can be secured, using the different controls summarized above, and both retain the same governed recommendation and approval workflow.

The Oracle logo illustrates the distinct controls. The MCP App permits the external image through resourceDomains in mcp-app/server.ts. The A2UI path separately requires the Image component and permits https://www.oracle.com through allowedImageOrigins in web-client/app.js. An A2UI payload does not inherit an MCP App's CSP.

Render Recommendation and Approval Controls with A2UI

agent-service/src/main/java/com/oracle/demo/interactiveai/A2uiPayloads.java creates one A2UI v0.9.1 surface, supplies an adjacency-list component tree, and sends recommendation data separately:

{
  "version": "v0.9.1",
  "createSurface": {
    "surfaceId": "inventory-transfer-review",
    "catalogId": "https://a2ui.org/specification/v0_9_1/catalogs/basic/catalog.json",
    "sendDataModel": true
  }
}

Gemini Enterprise also publishes a gemini_enterprise_composite_catalog.json example that combines standard Material components with Gemini Enterprise components. This application uses the standard catalog IDs advertised in its agent card.

The same class requests an Image from the host's approved Basic Catalog and declares the bounded action names:

Map.of(
    "id", "oracleLogo",
    "component", "Image",
    "url", "https://www.oracle.com/a/ocom/img/oracle-logo.svg",
    "description", "Oracle logo for Oracle AI Database",
    "fit", "contain",
    "variant", "header");

Map.of(
    "id", "confirm",
    "component", "Button",
    "text", "Approve inventory transfer",
    "action", Map.of("event", Map.of(
        "name", "approve_inventory_transfer")))

web-client/app.js accepts only the expected version, catalog, surface, component types, and HTTPS image origins. Generated values enter the DOM through textContent; no A2UI payload can provide executable JavaScript:

const allowedComponents = new Set([
  "Column", "Row", "List", "Card", "Text", "Image", "Button", "TextField"
]);
const allowedImageOrigins = new Set(["https://www.oracle.com"]);

if (!allowedComponents.has(component.component)) {
  throw new Error(`A2UI component not allowed: ${component.component}`);
}

const url = new URL(component.url);
if (url.protocol !== "https:" || !allowedImageOrigins.has(url.origin)) {
  throw new Error(`A2UI image origin not allowed: ${url.origin}`);
}

name.textContent = `${recommendation.sku} · ${recommendation.productName}`;

The application does not present generic, application-authored follow-up choices. Each selectable card is a concrete recommendation produced by STOCKOUT_TRANSFER_RECOMMENDATION_V, and the only write action is to approve that exact transfer with an operator note or cancel it.

The A2A Agent Explicitly Advertises A2UI Support

The adapter publishes a dynamically generated agent card at GET /.well-known/agent-card.json. Its A2A protocol version and A2UI extension are separate declarations, so a host can discover both the communication contract and the supported UI capability before invoking the agent.

build_agent_card() in gemini-enterprise-a2a/main.py generates a card equivalent to this JSON representation:

{
  "capabilities": {
    "streaming": true,
    "extensions": [
      {
        "uri": "https://a2ui.org/a2a-extension/a2ui/v0.8",
        "required": false,
        "params": {
          "supportedCatalogIds": [
            "https://a2ui.org/specification/v0_8/standard_catalog_definition.json"
          ]
        }
      }
    ]
  },
  "protocolVersion": "0.3.0"
}

protocolVersion identifies A2A v0.3. The extension URI advertises A2UI v0.8, and supportedCatalogIds identifies the standard component catalog the agent can produce. Because required is false, the extension is optional for the interaction. Inspect a local card with curl -s http://127.0.0.1:3002/.well-known/agent-card.json.

Who Decides Whether to Return A2UI?

Advertising A2UI support does not require every response to contain a UI. An agent can choose text, A2UI, or both according to the request, workflow state, host capabilities, and product policy. The host must still validate every A2UI message against the advertised version and catalog.

This application makes that choice deterministically rather than asking an LLM. In SupplyChainExecutor.execute() in gemini-enterprise-a2a/main.py, a normal request builds the review while a returned userAction follows the action path:

action = _extract_user_action(context)
if action:
    parts = await _handle_action(action, access_profile)
else:
    # Retrieve recommendations and return the A2UI review.

A normal request returns text plus an A2UI review surface. An approve or cancel userAction returns text plus a smaller A2UI result surface. A host that does not render the optional extension can still use the text part, and an error path returns text only.

Map Oracle Database Results into Gemini Enterprise A2UI

gemini-enterprise-a2a/main.py calls the Java service and receives the database-backed recommendation dictionaries:review = await asyncio.to_thread( _post_agent, "/api/reviews", { "minimumStockoutRisk": minimum_risk, "maximumRows": maximum_rows, "accessProfile": access_profile, }, )
gemini-enterprise-a2a/a2ui_payloads.py maps those values into approved A2UI components:components.extend([ _component( card_id, "Card", {"child": content_id}, ), # Additional approved components are omitted. _text( f"route-{index}", f"{recommendation['sourceLocationCode']} to " f"{recommendation['targetLocationCode']}: " f"{recommendation['recommendedTransferQuantity']} units", ), ])
gemini-enterprise-a2a/main.py packages the messages as A2UI DataParts in the A2A response:parts = [ Part(root=TextPart( text=f"Showing {len(review['recommendations'])} recommendations." )), *(create_a2ui_part(message) for message in messages), ]

Optionally Stream A2UI and Agent State with AG-UI

The standalone frontend uses AG-UI because it needs incremental run state and tool activity as well as A2UI. The following excerpt from agent-service/src/main/java/com/oracle/demo/interactiveai/AguiRunService.java emits AG-UI events over Server-Sent Events and carries each A2UI envelope in a CUSTOM event:

send(output, Map.of(
    "type", "TOOL_CALL_START",
    "toolCallId", toolCallId,
    "toolCallName", "find-stockout-transfer-recommendations"));

send(output, Map.of(
    "type", "STATE_SNAPSHOT",
    "snapshot", Map.of("status", "AWAITING_APPROVAL")));

send(output, Map.of(
    "type", "CUSTOM",
    "name", "a2ui.message",
    "value", envelope));

The frontend can render progress before the full recommendation set arrives and correlate arguments and results with one toolCallId. The standalone web-client/app.js validates allowlisted A2UI state and renders this exchange's HTML rather than acting as a general-purpose A2UI renderer.

Add a Supply-Chain Dashboard with MCP Apps

The web application at http://127.0.0.1:8080 demonstrates AG-UI and A2UI. The separate mcp-app/ package is a real MCP App demonstration that can run in the official MCP Apps basic host without a third-party account.

The following abridged model-visible dashboard tool is defined in mcp-app/server.ts:

registerAppTool(server, "show-inventory-transfer-dashboard", {
  title: "Show inventory transfer dashboard",
  inputSchema: {
    minimumStockoutRisk: z.number().min(0).max(100),
    maximumRows: z.number().int().min(1).max(50)
  },
  _meta: {
    ui: { resourceUri: "ui://oracle-supply-chain/inventory-exchange-v2" }
  }
}, async ({ minimumStockoutRisk, maximumRows }) => {
  const review =
      await loadGovernedReview(minimumStockoutRisk, maximumRows);
  return {
    structuredContent: {
      recommendations: review.recommendations,
      source: "oracle-db-mcp-java-toolkit"
    },
    _meta: { approvalId: review.approvalId }
  };
});

registerAppTool(server, "approve-inventory-transfer", {
  inputSchema: {
    approvalId: z.string().uuid(),
    recommendationId: z.string(),
    approvalNotes: z.string().min(10).max(500)
  },
  _meta: { ui: { visibility: ["app"] } }
}, approveExactReviewedTransfer);

The same file registers the HTML as an MCP App resource and supplies the sandbox policy:

registerAppResource(
  server,
  resourceUri,
  resourceUri,
  { mimeType: RESOURCE_MIME_TYPE },
  async () => ({
    contents: [{
      uri: resourceUri,
      mimeType: RESOURCE_MIME_TYPE,
      text: await readFile(
        path.join(import.meta.dirname, "dist", "mcp-app.html"),
        "utf8"
      ),
      _meta: {
        ui: {
          csp: {
            connectDomains: [],
            resourceDomains: ["https://www.oracle.com"]
          }
        }
      }
    }]
  })
);

The model-visible tool calls /api/reviews on the Java service. The service invokes the Toolkit's allowlisted read tool and binds a short-lived approval handle to the exact returned rows. The handle travels in widget-only result metadata rather than model-visible content. Selecting a card calls updateModelContext; approving or canceling calls an app-only tool that the model cannot invoke. The iframe receives neither wallet files nor database credentials.

The following abridged excerpt from mcp-app/src/mcp-app.ts shows the iframe requesting its app-only approval tool through the host bridge:

const result = await app.callServerTool({
  name: "approve-inventory-transfer",
  arguments: {
    approvalId,
    recommendationId: selectedRecommendation.recommendationId,
    approvalNotes: notes
  }
});

const payload = result.structuredContent as { transferId?: number };
void app.updateModelContext({
  content: [{
    type: "text",
    text: `The user approved transfer ${payload.transferId}.`
  }]
});

What Does “Sandboxed Iframe” Mean for an MCP App?

In a compatible host that implements the MCP Apps security model, developer-built HTML and JavaScript is loaded as a ui:// resource in an isolated iframe rather than inserted into the host page. The intended boundary prevents the app from reading or modifying the host DOM, cookies, or local storage; navigating the parent page; or executing code in the host context.

Those protections depend on the host correctly implementing and enforcing separate origins, iframe sandbox flags, Content Security Policy, Permissions Policy, and a validated JSON-RPC postMessage bridge. MCP Apps metadata requests network, resource, and device permissions; it does not independently enforce the browser boundary. Review the specific host's implementation and current documentation before relying on a capability or restriction.

The sandbox provides browser isolation, not application authorization. The MCP server must still authenticate the actor, validate every tool input, enforce authorization, and protect downstream transactions.

The following metadata from mcp-app/server.ts keeps network requests disabled, permits the Oracle logo as a static image from one official origin, and keeps approval callable by the app rather than the model. connectDomains governs fetch, XHR, and WebSocket destinations; resourceDomains governs images, scripts, styles, fonts, and media:

_meta: {
  ui: {
    csp: {
      connectDomains: [],
      resourceDomains: ["https://www.oracle.com"]
    }
  }
}

// Approval tool metadata
_meta: { ui: { visibility: ["app"] } }

See the MCP Apps overview and the stable MCP Apps specification for normative sandbox and bridge behavior.

Bind Human Approval to One Exact Transfer

When the read completes, the agent service issues a short-lived approval ID bound to the actor and an immutable map of every returned recommendation. The browser posts only the approval ID, recommendation ID, and notes. It cannot replace the product, source, target, or quantity.

The write path reserves a transfer ID from a sequence and then calls an input-only procedure. The following excerpt from database/setup.sql shows how Oracle locks the source and target positions, recomputes surplus and shortage, and rejects a quantity greater than the current safe amount:

v_safe_transfer_qty := LEAST(v_source_surplus, v_target_shortage);

IF p_transfer_qty > v_safe_transfer_qty THEN
  RAISE_APPLICATION_ERROR(
    -20007,
    'Recommendation is stale; current safe transfer quantity is ' ||
    TO_CHAR(v_safe_transfer_qty));
END IF;

INSERT INTO inventory_transfers (...);
UPDATE inventory_positions
   SET reserved_qty = reserved_qty + p_transfer_qty
 WHERE product_id = p_product_id
   AND location_id = p_source_location_id;

The insert and inventory reservation are one tool statement and one transaction. Canceling consumes the pending approval without invoking the write. Reuse, an actor change, an unknown recommendation, invalid notes, or stale inventory causes rejection.

Auditable AI agent answers need more than explanatory prose: the UI preserves the Toolkit source label and database-calculated inputs, while an approved action returns a database transfer ID and durable audit state.

Run the Application Locally End to End

This local reference run shows how to test a database-backed AI application locally before deployment. It validates the shared database, standalone Toolkit, Java service, AG-UI stream, A2UI renderer, and MCP App before the following sections connect those same components to external conversational hosts. The browser places the database-calculated recommendation and approval controls beside the event stream so tool calling and state synchronization remain visible without an oversized full-page screenshot.

cd a2ui_mcpapps_mcptoolkit
cp .env.example .env
# Set DB_URL, TNS_ADMIN, DB_USERNAME, and DB_PASSWORD.

cd agent-service
./setup-database.sh
./test.sh

# Terminal 1: standalone Toolkit
cd ../oracle-db-mcp-toolkit
./run.sh full

# Terminal 2: Java service and browser
cd ../agent-service
./run.sh

# Open http://127.0.0.1:8080

Create Oracle AI Database 26ai. Use a cloud service or a local installation by following the Oracle AI Database 26ai quick start.
Prepare the database. Run agent-service/setup-database.sh to create the sample inventory objects, recommendation view, transfer sequence, and approval procedure.
Start the Toolkit. In one terminal, run oracle-db-mcp-toolkit/run.sh full. It exposes the allowlisted tools at an authenticated TLS MCP endpoint.
Start the application. In another terminal, run agent-service/run.sh. Its HTTP MCP client connects to the Toolkit, verifies its identity and exact allowlist, and exposes the API and web client.
Open the inventory exchange. Browse to http://127.0.0.1:8080.
Run the analysis. Set minimum stockout risk to 50, maximum rows to 10, and submit.
Retrieve governed recommendations. The Toolkit binds the inputs and calls find-stockout-transfer-recommendations.
Inspect the streamed result. AG-UI shows lifecycle and tool activity while A2UI renders the recommendation cards and approval controls.
Approve or cancel. Select one returned recommendation, enter notes, and choose the explicit action.
Commit through MCP. Oracle locks and revalidates both positions, writes the audit row, and reserves source inventory atomically.
Run the smoke test. From another terminal, run scripts/smoke-test.sh.
Open the MCP App locally. Run mcp-app/run.sh and mcp-app/run-basic-host.sh, open http://127.0.0.1:8082, and invoke show-inventory-transfer-dashboard.

Full Setup and Runtime Walkthroughs for A2UI and MCP Apps in ChatGPT, Claude, and Gemini Enterprise

The following walkthroughs show the portable MCP App in ChatGPT, Claude, and Gemini Enterprise, followed by native A2UI over A2A in Gemini Enterprise. These products demonstrate the two UI approaches without defining or limiting which capable hosts can implement them.

Run the MCP App in ChatGPT

Author verification: The capability and setup claims in this section were checked against OpenAI's official ChatGPT UI, authentication, and connection and testing documentation on August 20, 2026. Product interfaces and terminology can change.

ChatGPT can discover the bounded MCP tool, call it with validated limits, load the ui:// resource, and render the Oracle Database MCP Java Toolkit result inline.

OpenAI's current flow calls the host integration a plugin backed by an MCP connection. The server exposes the component as a ui:// resource with media type text/html;profile=mcp-app, while its public Streamable HTTP endpoint normally ends in /mcp. This repository already implements that resource, tool metadata, host bridge, read-only dashboard tool, and app-only approval and rejection tools.

ChatGPT plugin / MCP connection
  |  public HTTPS Streamable HTTP: /mcp
  v
TypeScript MCP App server
  |  ui:// resource + app-only action tools
  v
Java approval service -> Oracle DB MCP Toolkit -> Oracle AI Database

Secure deployment and setup

Authentication boundary: keep the service private by default. Use synthetic data for any explicitly authorized anonymous read-only demonstration, and require OAuth 2.1 with issuer, audience, expiry, and scope validation before exposing customer-specific data or write actions.

Set the required environment variables documented at the top of deploy/gcp/deploy-chatgpt-mcp.sh, then run that script from macOS or Linux to build and deploy the private read-only service. It reads the wallet and password from Secret Manager.
With a Google identity token, verify /health, tools/list, resources/read, and a read-only tools/call. Confirm that anonymous access still returns 403.
Choose the access gate. Keep MCP_WRITES_ENABLED=false for a synthetic-data rendering demonstration. For durable use or any approval action, configure an established OAuth 2.1 provider.
In ChatGPT, open Settings > Security and login and enable Developer mode. Workspace policy can control whether this setting is available.
Open Plugins, select the plus button, name the plugin Supply-Chain Inventory Exchange, choose the configured authentication method, and enter the deployment URL ending in /mcp.
Acknowledge the custom-MCP warning, create and connect the plugin, then inspect its metadata. Discovery must show only show-inventory-transfer-dashboard with minimumStockoutRisk and maximumRows inputs.
Start a new conversation, add Supply-Chain Inventory Exchange from the tools menu, and ask: “Show the inventory transfer dashboard for products with a minimum stockout risk of 70, limited to 3 recommendations.”
Verify that ChatGPT calls the tool with minimumStockoutRisk=70 and maximumRows=3, renders the embedded component, and labels the result as live data from the Toolkit.
Refresh the plugin connection whenever tool or resource metadata changes.

Run the MCP App in Claude

Author verification: The capability and setup claims in this section were checked against Anthropic's official remote MCP connector and interactive connector documentation on August 20, 2026. Product interfaces and terminology can change.

Claude can discover the custom connector, call its bounded interactive tool, receive the Oracle Database MCP Java Toolkit result, and render the same ui:// MCP App. Because Claude connects from Anthropic's cloud, it needs a securely exposed remote endpoint that meets the host's network and authentication requirements.

Claude interactive connector
  |  public remote MCP endpoint: /mcp
  v
Same TypeScript MCP App server
  |  ui:// resource + one bounded read-only tool
  v
Java approval service -> Oracle DB MCP Toolkit -> Oracle AI Database

Setup and invocation steps

Deploy the same Streamable HTTP MCP App service used by ChatGPT with an appropriate authentication boundary.
In Claude, open Settings > Connectors, select Add > Add custom connector, provide a descriptive name, and enter the remote URL ending in /mcp.
Open the connector detail page. Confirm that it is connected and exposes exactly one interactive tool, Show inventory transfer dashboard. Choose whether Claude may invoke it automatically, ask each time, or never.
Start a new chat, open the plus menu, enable Supply-Chain Inventory Exchange, and allow connector search if Claude presents that separate discovery opt-in.
Ask: Show the inventory transfer dashboard for products with a minimum stockout risk of 70, limited to 3 recommendations.
Verify the model-visible tool result and inline widget both report the same Toolkit-backed row: WATER-SENSE, PHX-DC to SEA-FC, 42 units, and stockout risk 74.6.
Require OAuth 2.1 before exposing customer-specific data or registering the approval and rejection tools.

Run the MCP App in Gemini Enterprise

Author verification: The capability and setup claims in this section were checked against Google Cloud's official custom MCP server data store documentation on August 20, 2026. Product interfaces and terminology can change.

Gemini Enterprise can load the same ui:// dashboard through a Custom MCP Server data store. This is separate from its native A2UI/A2A agent: the MCP path discovers a tool and loads sandboxed web content, while the A2UI path receives declarative component messages and renders native controls.

Gemini Enterprise
  |  private Custom MCP Server connector
  |  Streamable HTTP + OAuth
  v
TypeScript MCP App server
  |  ui:// inventory dashboard
  v
Java service -> Oracle DB MCP Toolkit -> Oracle AI Database

Private connector setup

Set the required environment variables documented at the top of deploy/gcp/deploy-gemini-enterprise-mcp.sh, then run that script from macOS or Linux. It keeps Cloud Run private, disables MCP writes, and grants invocation only to the Discovery Engine service agent.
Create a Google OAuth web client with the authorized redirect URI https://vertexaisearch.cloud.google.com/oauth-redirect. Keep the client secret outside Git, screenshots, logs, and model context.
Create a Custom MCP Server data store. Enter the private service URL ending in /mcp, authorization URL https://accounts.google.com/o/oauth2/auth, token URL https://oauth2.googleapis.com/token, and authorization parameter &access_type=offline.
Request openid email profile https://www.googleapis.com/auth/cloud-platform, enable PKCE, enable HTTP Basic authentication, select Login, and complete the Google authorization flow.
Name the connector Oracle Supply-Chain MCP App, connect it to the target Gemini Enterprise application, and wait until its status is Active.
Open Actions, select Reload custom actions, enable show-inventory-transfer-dashboard, and verify that no broader SQL or write tool is model-visible.
In the Gemini Enterprise application, enable the connector and ask: Show the inventory transfer dashboard for products with a minimum stockout risk of 70, limited to 3 recommendations.
Confirm that the tool result and sandboxed widget agree on the governed recommendation and display the read-only boundary. The native A2UI agent is not involved in this path.

Run Native A2UI in Gemini Enterprise

Author verification: The capability and setup claims in this section were checked against Google Cloud's official A2UI registration and Cloud Run tutorial documentation on August 20, 2026. Product interfaces and terminology can change.

Gemini Enterprise is the A2UI-capable host used in this reference application. Its custom-agent path uses A2A as the conversation transport and A2UI as declarative UI cargo. This implementation targets A2A v0.3 and A2UI v0.8, while the standalone browser consumes A2UI v0.9.1 envelopes. This is one implementation example, not a coupling between A2UI and Gemini Enterprise.

Separate A2A Transport from A2UI Presentation

In this repository, gemini-enterprise-a2a/main.py co-locates the A2A endpoint, request executor, Java API client, and deterministic A2UI v0.8 builder in one process. That is a compact implementation choice, not a protocol requirement. A2A carries messages, tasks, artifacts, and DataParts between Gemini Enterprise and the remote agent; A2UI is the declarative presentation placed in those DataParts.

The agent card shown in the A2UI implementation section advertises the A2UI v0.8 extension and standard catalog that this co-located builder produces. A decoupled implementation would advertise the same external contract.

Follow the Native A2A/A2UI Path

Gemini Enterprise
  |  A2A v0.3 JSON-RPC
  |  A2UI v0.8 DataParts
  v
gemini-enterprise-a2a
  |  POST /api/reviews, /api/approve, /api/reject
  v
Java agent service
  |  Oracle Database MCP Java Toolkit
  v
Oracle AI Database

The adapter emits deterministic beginRendering, surfaceUpdate, and dataModelUpdate messages containing native cards, approval notes, and one button per exact recommendation. Gemini Enterprise resolves the button context into a v0.8 userAction; the adapter forwards only the approval handle, recommendation ID, and notes. The Java service recovers the immutable recommendation and Oracle revalidates it under locks. Every host-specific adapter shares this governed workflow and transaction without sharing a UI protocol.

The following excerpt from gemini-enterprise-a2a/a2ui_payloads.py returns the ordered A2UI v0.8 messages that the host renders:

return [
    {
        "beginRendering": {
            "surfaceId": surface_id,
            "root": "root",
        }
    },
    {
        "surfaceUpdate": {
            "surfaceId": surface_id,
            "components": components,
        }
    },
    {
        "dataModelUpdate": {
            "surfaceId": surface_id,
            "contents": [{
                "key": "approvalNotes",
                "valueString": (
                    "Approve the database-recommended transfer "
                    "to reduce stockout exposure."
                ),
            }],
        }
    },
]

Deploy the Gemini adapter on Google Cloud

Gemini Enterprise requires a reachable HTTPS A2A endpoint; it does not require the agent to run on a Compute Engine VM. Cloud Run supplies managed HTTPS and can reach a private Oracle AI Database endpoint through Direct VPC egress when required.

Gemini Enterprise: Inventory System
  |  managed HTTPS
  v
Cloud Run: A2A/A2UI adapter
  |  IAM-authenticated adapter :8080
  |  loopback Java service :8081
  |  authenticated Streamable HTTP
  v
Oracle Database MCP Java Toolkit service
  v
Oracle AI Database private service

The Cloud Run image contains the adapter, Java service, and pinned Toolkit but no secret. Secret Manager mounts the Oracle wallet and injects the dedicated database password at runtime. The entrypoint expands the wallet into ephemeral storage, starts Java only on loopback, waits for its health check, and then exposes the A2A adapter on Cloud Run's assigned port.

The checked-in deploy/gcp scripts build the image with Cloud Build, use a dedicated runtime service account, limit Cloud Run to one instance while approval handles remain in memory, set the final service URL in the agent card, and register that card through the official Discovery Engine assistants/default_assistant/agents REST resource. The deployed endpoint rejects anonymous calls and grants roles/run.invoker only to Gemini Enterprise's Discovery Engine service agent. Google's Cloud Run guidance recommends this IAM model for internal clients such as Gemini Enterprise; an OAuth client is needed only when the agent must access Google resources on behalf of the end user.

The private database endpoint is reached through Cloud Run Direct VPC egress. A VM in the VPC remains a valid alternative, but the protocol and A2UI implementation do not change. Reject anonymous discovery and grant invocation only to the intended Gemini Enterprise service identity.

Register, share, and invoke the custom agent

Register the deployed agent card with the Gemini Enterprise application in the same project and location, then share the custom agent using the per-agent Agent User role. Assign the required Gemini Enterprise license to each user; licensing is an account prerequisite, not part of the application architecture.

In the Gemini Enterprise web app, the shared agent appears under From your organization. The prompt Show inventory transfers with a minimum stockout risk of 70, limited to 3 recommendations invokes the agent over authenticated A2A streaming. Oracle returned the one row in the sample data that met that threshold: a 42-unit WATER-SENSE transfer from PHX-DC to SEA-FC with a stockout-risk score of 74.6. Gemini Enterprise rendered the returned A2UI DataParts as a native card, approval-notes field, exact-transfer approval button, and cancel-without-writing button. The host never loaded the MCP App iframe.

Security and Governance Checklist

Use a least-privileged database principal with only the required view and procedure grants.
Run the Toolkit as a separate Streamable HTTP service with TLS and bearer authentication or OAuth 2.0.
Enable only purpose-built tools. Do not use -Dtools=* or unrestricted write-query.
Validate and bind every user value; enforce score, row-count, text-length, and timeout limits.
Bind approval to actor, recommendation data, expiry, and a single use; add durable idempotency for production.
Revalidate current inventory under locks at the final database boundary.
Log tool, actor, timestamp, recommendation, approval, and status without logging secrets.
Allowlist A2UI catalogs and components; treat MCP App content as untrusted and apply sandbox, CSP, and permission controls.
Keep wallets, passwords, tokens, and client secrets out of the browser, model context, and repository.

Production Considerations

This reference application uses a transparent single-transfer heuristic. It does not replace supply-network optimization, order management, transportation planning, or warehouse execution. Approval state is stored in process, so production deployments need durable, shared approval and idempotency storage. Remote MCP write actions also require actor-bound OAuth authorization.

Negotiate A2UI versions and component catalogs explicitly, validate every payload at the host boundary, and test compatibility before upgrading an agent or host. Use TLS, least-privileged service identities, bounded location and value authority, and concurrent-transfer tests while preserving the database transaction as the final execution boundary.

References

Host product names identify documented examples tested for this article; they do not imply partnership, endorsement, or certification. Host-specific capability and setup claims link to the vendors' canonical documentation below.

Frequently Asked Questions

Does the MCP App replace the web client?

No. The custom web client demonstrates AG-UI and A2UI. The separate MCP App renders the same Toolkit-backed recommendations inside a compatible conversational host.

Are A2UI and MCP Apps tied to specific host products?

No. They are capability-based UI contracts. This project demonstrates Gemini Enterprise as an A2A/A2UI host and as an MCP Apps host, alongside ChatGPT and Claude MCP App examples. The governed service, Toolkit tools, recommendation IDs, and Oracle transaction boundary remain independent of the host.

Why not let the agent generate SQL or choose any transfer?

A bounded recommendation view and stored procedure make the allowed data, feasibility formula, privileges, locks, transaction, and audit result explicit. Unrestricted SQL or client-selected quantities would expand the attack surface and make execution less deterministic.

Where is approval enforced?

The agent service binds approval to the authenticated actor and exact returned recommendation. The MCP tool accepts only validated inputs, and Oracle AI Database locks and rechecks current stock before it commits the transfer.

Building an agent harness that survives production

Anya Summers — Wed, 09 Sep 2026 14:36:13 +0000

Diagram showing the model proposing, the harness authorising, and the environment applying changes.

Put an agent on your refunds queue. It reads the ticket, finds the order, checks the policy, issues the refund. The demo goes beautifully.

Six weeks later it closes 140 tickets in an afternoon and reports every one resolved. Forty-one of them never reached the payments API. The model wrote “refund issued” because that is what the end of a refund conversation looks like.

The agent was not confused about the work. Researchers replayed 16,758 coding-agent runs: in two of the systems tested, 60 to 69 per cent of the runs that ended in a wrong answer had already found and edited the right code first (Coherence Collapse). Competence was not the missing ingredient. Everything around it was.

Think about the sharpest graduate you ever hired. Now imagine handing them production credentials on day one: no induction, no runbook, nobody reviewing the work, and “done” taken at face value. You would not blame the graduate.

Key takeaways

A framework gives you construction materials. A harness is what you built with them: real credentials, real limits, and a real definition of done.
Two jobs never become the model’s, however good it gets. Holding the credential is one. Confirming that the work actually happened is the other.
Prompt-level defenses against injection move the numbers, then adaptive attacks move them back. Enforcement outside the model is what holds.
Grade every part of your harness on three questions: is there an owner, is there a mechanism, is there a failing test. A row with three no answers is a production risk to investigate first.

What is an agent harness?

An agent harness is the configured layer around a model that decides what it sees, what it may do, where those actions land, what survives a restart, and what counts as proof the work is finished.

That is a working convention rather than a settled standard. The closest formal treatment is a 2026 preprint proposing harness engineering as the runtime substrate between a model and its environment (AI Harness Engineering), demonstrated on one narrow software-engineering task. Useful rather than authoritative.

Concretely, on that refunds queue: read access to the ticket queue and the orders table and nothing else, one payments credential that can only issue refunds under £200, a hard stop at ten tool calls, a run record in the database that survives a restart, and a check that reads the payments ledger back before the ticket is allowed to close. That set of choices is the harness. None of it is the model.

The model proposes, the harness authorises, the environment acts, and a verifier decides whether any of it counted. Your graduate’s induction.

Would a bigger model make all this unnecessary?

The obvious objection is that all of this is temporary. Models keep improving and context windows keep growing, so surely a big enough model makes the surrounding structure redundant, and today’s harness is tomorrow’s dead weight. It is a fair question, and the honest answer is: some of it, yes. Start with what has been measured, because the same model scores very differently depending on the wrapper.

Bar charts show higher task success when the model is surrounded by a purpose-built interface or full adapter.

Each pair holds the model fixed and changes only what surrounds it. In a peer-reviewed ablation over 300 SWE-bench Lite tasks, GPT-4 Turbo solved 18.0 per cent with a purpose-built interface to the codebase and 11.0 per cent driving a plain shell (SWE-agent). In a 2026 preprint, a minimal and a full adapter on the same GLM 5.1 backbone scored 19.1 and 73.4 per cent (Claw-SWE-Bench). No weights changed, and the score did. A third study, over 5,194 trajectories on 106 sandboxed tasks at shared budgets, found a 23.8-point gap between its best and worst configurable harnesses (Harness-Bench). Each bundles several mechanisms per adapter, so none says which part did the work. Together they establish that the wrapper is a variable, not a detail.

The counter-evidence is real. A long-context Gemini 2.5 Pro, given a fully observable environment and deliberately simple scaffolding, reached 50.8 per cent on SWE-bench Verified (Putting It All into Context), and vendors report the same from the inside: context resets that stopped a weaker model finishing early became overhead on a stronger one (Scaling Managed Agents).

The split is the useful part. Scaffolding that compensates for weak reasoning should shrink as models improve. Controls that carry accountability should not: no gain in reasoning turns a model’s confidence into permission to touch the ledger.

Two things never become the model’s job. The first is identity: a model holds no credential, it produces text that something else decides to act on, so “who is allowed to do this” has to be answered by a component with an identity of its own. A scoped, short-lived credential held by the process, not a role the model asserts about itself in a prompt. The second is the result. A model can report an outcome that never happened, because a plausible ending to a refund conversation is exactly what it was trained to produce. The forty-one unrefunded tickets were not a lie, they were writing. Neither failure is a reasoning error a larger model grows out of.

How is a harness different from a framework?

A framework gives you construction materials. A harness is what you built with them, bound to real credentials, real limits and a real definition of done. Teams conflate the two, and the cost is mistaking a successful pip install for production readiness.

Layer	What it is	What it owns	What it cannot decide
Model	The learned inference component that maps context to proposed actions (Toolformer)	Semantic judgement, tool selection, interpretation	Whether it holds the credential, or whether the call was allowed
Framework	Reusable developer abstractions: graphs, messages, tool adapters, hooks, as in LangGraph, AutoGen and Semantic Kernel (Agentic AI Frameworks)	Construction primitives and wiring	The deployed application’s permissions, recovery rules or acceptance tests
Harness	The configured operational envelope binding a model to one task under real credentials, real limits and a real definition of done (AI Harness Engineering)	Authority, containment, continuity, proof	What the model is semantically capable of
Runtime	The machinery that instantiates and advances that envelope for one run (Scaling Managed Agents)	Turn scheduling, dispatch, limits, event records	The policy it is enforcing
Environment	The filesystem, browser, database, network and services where actions take effect (SWE-agent)	Authoritative state	Anything about intent

Five-layer architecture diagram highlighting the harness as the layer that decides authority.

The comparison that matters most in a design review is the harness row against the framework row, and Oracle’s own LangChain work shows the line clearly. langchain-oracledb connects 26ai to LangChain as a vector store, and langgraph-oracledb gives a graph an OracleSaver for checkpoints and an OracleStore for cross-thread memory, so run state lands in a database with backup, auditing and access control rather than a file nobody is watching (langchain-oracle).

That is worth having, and it is still framework. The integration hands the agent capabilities: it can reach the vectors, it can persist a checkpoint. What it does not hand anyone is permission or proof. Nothing in it decides whether this particular update was authorised, whether a half-finished external write can be retried, or what counts as evidence the task is done. Capabilities come from the framework. Permission and proof are configuration, and the configuration is the harness.

Which failure does each part of the harness absorb?

Read this table against your own system. Each row is a part, what it is for, and the failure it catches. If a row has no owner in your architecture, that failure is landing in production now.

Part	What it is for	The failure it absorbs
Instructions and context	Ranking authority: which text governs, and which is merely data the agent read (AgentDojo)	Retrieved text acquiring authority because it happened to arrive later in the window
Tools	Typed schemas, argument validation and call-time policy at the point uncertain judgement crosses into consequence (Model Context Protocol)	A well-formed call the caller was never authorised to make
Environment and permissions	Isolation, scoped identity and short-lived credentials, so containment survives model error (NIST agent identity concept paper)	Ambient authority, where any instruction reaching the model activates every credential in the process
Runtime state	Durable task status, checkpoints, approvals and external-operation identifiers outside the context window (Effective Harnesses for Long-Running Agents)	Losing work to a restart, or mistaking unfinished state for completion after compaction
Orchestration	Legal transitions, retry budgets and deterministic gates before execution (Reason Less, Verify More)	Repetition standing in for diagnosis, and loops that never terminate
Verification	Reading authoritative state back and grading it against the task contract, which is why τ-bench scores final database state and reports retail reliability below 25 per cent at pass^8 (τ-bench)	The refunds agent above: a completion claim with nothing behind it

Three rows have unusually clean evidence, so cash them out.

Orchestration first. Researchers put a deterministic gate before each action: ordinary code that reads the proposed call and refuses it if it breaks policy, no model involved. On the policy-permissive airline domain of τ²-bench, success rose from 29.6 to 42.0 per cent; where the tools already enforced policy it changed almost nothing (Reason Less, Verify More). The gate pays where nothing else was checking.

Verification carries the bleakest number here. Across 105 realistic multi-file coding tasks, the strongest pairing tested produced work both correct and secure 15.2 per cent of the time, and explicit security instructions did not significantly close the gap (SecureAgentBench). Telling the graduate to be careful is not reviewing their pull request.

Then resources, which almost nobody counts as part of the harness. Varying only CPU and memory on Terminal-Bench 2.0, with model, harness and tasks held constant, moved scores by six points and dropped infrastructure failures from 5.8 to 0.5 per cent (Quantifying Infrastructure Noise). Your resource ceilings are part of your agent’s measured capability. That makes them a harness setting rather than a separate infrastructure ticket. A memory ceiling is a configuration choice that changes what the agent can finish, exactly like a retry budget or a tool timeout, and when it bites, the failure arrives dressed as a reasoning error: a process killed mid-edit reads identically to an agent that gave up. Set it in the same review where you set the limits, and hold it constant whenever you ablate anything else.

What does a harness look like in real Oracle code?

It looks like a repository you can clone, not a diagram. The Enterprise Data Agent Harness workshop builds a memory-aware enterprise data agent one layer at a time, around an orchestration loop of some 300 lines of Python. Almost everything else is database primitives.

The tool surface is the SQLcl MCP Server. MCP is the Model Context Protocol, the open standard for how an agent’s host application connects to servers that expose tools, resources and prompts rather than the model reaching data directly, and Oracle’s implementations sit at oracle.com/mcp. The scratchpad is DBFS, the Database File System, which lets the database present ordinary files and directories: the agent’s working files then live under the same access control, backup and audit as the data itself. Oracle Data Access Controls scope what each user’s agent can reach.

The oracle-data-migration-harness app lands on a problem customers actually turn up with: getting data out of somebody else’s platform without breaking what was built on it. It moves a RAG corpus from MongoDB into Oracle AI Database 26ai so the vectors keep working and JSON Relational Duality opens up SQL aggregation.

What makes it a harness rather than a migration script is where the Oracle expertise lives. Not in a system prompt, but in a set of Oracle’s own published migration skills the agent retrieves and composes per job: sample, translate the schema, transfer, verify, reconcile. Move that into the prompt and you have deleted a layer.

Underneath both, 26ai holds dense and sparse vectors beside relational and JSON data (AI Vector Search), row-level predicates through Virtual Private Database (DBMS_RLS) and unified auditing (auditing). Oracle AI Agent Memory installs with pip install oracleagentmemory (notebook).

Both of those, and a good deal more, live in the oracle-ai-developer-hub repository. If you want harness code rather than harness prose, start there.

What holds when the prompt does not?

Some of the text your agent reads was written by somebody who wants it to do something else: an email it is triaging, a comment in the code it is fixing, a page it fetched. That is prompt injection: instructions arriving through a channel you were treating as data. Prompt-level defenses reduce it measurably. What they do not hand you is a number that stays still once the attacker adapts.

Chart showing how attack success can increase when attackers adapt to existing defences.

Undefended, a ReAct-prompted GPT-4 followed indirect attacks in 24 per cent of 1,054 cases (InjecAgent). Hardening moves that a long way, through instruction-hierarchy training (Improving Instruction Hierarchy in Frontier LLMs) and, in one browser stack combining training, classifiers and red-teaming, down to roughly 1 per cent residual success at 100 attempts per environment, which the vendor still calls meaningful (Mitigating the Risk of Prompt Injections in Browser Use).

Then the ground moves. AutoDojo re-optimised its attacks against a filter that had scored zero under static testing and recovered 28 per cent success, rising to 64 per cent where fetched content helped decide the action (AutoDojo). A defence measured once is not a defence measured under pressure.

The control that is least dependent on wording is enforcement outside the model. CaMeL derives control flow from the trusted user request, treats retrieved material as data, and attaches capabilities to values before any tool runs. It completed 77 per cent of AgentDojo tasks, compared with 84 per cent for the undefended agent (CaMeL): seven points of utility for a structural property rather than a better average. Scoping tools helps and is not sufficient: in 17 per cent of AgentDojo’s cases the tools the legitimate task needed were already enough for the attack (AgentDojo).

How do you grade your own harness?

Go back to the table of parts and ask three questions of every row.

Is there an owner: a named person or team accountable when that row fails, rather than a box on a diagram. Is there a mechanism: something in the code or the platform that enforces the row, rather than a prompt that requests it. Is there a failing test: something in your pipeline or monitoring that turns red when the mechanism stops working.

Resist turning that into a score. No published work weights these rows against each other, so a number would carry an authority the evidence behind it does not have. The questions are diagnostic, not a benchmark, and what they are good for is finding rows where all three answers are no.

If asking cold feels too abstract, work backwards from your last bad incident. The agent said it was done and it was not: that is verification, and the fix is reading authoritative state back rather than grading the narration. It ran a command nobody would have approved: that is environment and permissions, where a scoped identity does what prompt wording cannot. It lost three hours at a restart and resumed from a summary missing a constraint: that is runtime state, and a compacted summary is an aid to inference rather than a checkpoint.

Then ask the question that costs money: which rows have no owner, no mechanism and no failing test? Treat those rows as the highest-priority production risks, not as roadmap gaps.

The final word: where this is going

Three things look reasonably safe to say, and one does not.

Harness effects are becoming measurable. “Our wrapper is better” was folklore until recently; there are now benchmarks that hold the model fixed, equalise budgets and evaluators, and vary only the wrapper (Harness-Bench, Claw-SWE-Bench). Configuration is becoming something you report rather than something left out of the table.

The compensating half of the scaffold will thin. The long-context result and the vendor account of resets becoming overhead both point that way (Putting It All into Context, Scaling Managed Agents). Code written to help a weaker model cope has a shelf life, so label it that way while you write it.

The accountability half is becoming an identity problem rather than a prompting one. NIST NCCoE has an open concept paper on identity and authorisation for AI agents (NIST NCCoE), and CaMeL shows enforcement that is structural rather than statistical (CaMeL). One plausible direction is for scoped, short-lived agent identity to become platform infrastructure, similar to service accounts.

What nobody can tell you yet is which part of your harness carries the weight: no published study varies every part independently across several models at equal budgets (Harness-Bench). That ablation is yours.

Your graduate got good because somebody scoped their access, wrote down what “finished” meant, and read the work before it shipped. Nobody made them smarter first.

FAQ

Is an agent harness the same thing as an agent framework? No. A framework supplies reusable abstractions such as graphs, messages and tool adapters (Agentic AI Frameworks). A harness is the configured deployment: which credentials, which limits, which recovery rules, which acceptance tests. You can adopt a framework and still have no harness.

Does the Model Context Protocol give me a harness? It gives you an interoperability boundary. MCP standardises host, client and server roles for tools, resources and prompts, and deliberately leaves model use and context management to the host application (Model Context Protocol). Oracle’s MCP servers are listed at oracle.com/mcp. None of them will decide whether a particular refund is justified by the current task.

Will better models make the harness unnecessary? Partly. Compensatory scaffolding does become redundant, and there is evidence a long-context model with a fully observable environment can compete with scaffolded systems (Putting It All into Context). Containment and independent verification are not compensations for weak reasoning, so they stay.

Can I just tell the model not to follow instructions in tool output? That reduces the rate rather than removing it. Instruction-hierarchy training measurably helps (Improving Instruction Hierarchy in Frontier LLMs), and adaptive attacks still recovered 28 per cent success against a filter that scored zero on static tests (AutoDojo). Enforcement outside the model is what holds regardless of how the attack is worded.

Where does Oracle AI Database 26ai fit? As the governed data, memory, tool and audit plane underneath a harness, not as the harness itself. Select AI Agent covers task context, the Autonomous AI Database MCP Server covers permissioned database operations, and Oracle AI Agent Memory covers durable memory and context across runs (Select AI Agent). Run history and checkpoints are a different layer again: those belong to your harness and runtime, not to the memory store. Repository execution, sandboxing and trajectory verification stay yours.

How do I know which harness component caused a regression? Version the whole configuration and ablate one thing at a time. No published factorial study varies every part across multiple models at equalised budgets (Harness-Bench), so you will be doing this on your own system rather than reading it off a leaderboard.

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.