Oracle EBS Modernization: Bridging Legacy Infrastructure to Modern AI Agents

# Oracle EBS Modernization: Bridging Legacy Infrastructure to Modern AI Agents

*How enterprise architects can move from siloed Oracle EBS and MS SQL estates to evaluated, observable AI systems — without a rip-and-replace.*

—

## 1. Introduction: The Problem with Legacy Oracle/MS SQL Siloes

Most large enterprises share the same geography. Oracle E-Business Suite (EBS) holds the financial and operational spine — payables, receivables, order management, inventory — on an on-premises R12 instance patched dutifully for a decade. Beside it sits an MS SQL Server estate: CRM bolt-ons, a logistics warehouse, and a custom reporting layer nobody has dared to touch. Between them, a nightly batch job pushes data one way; nothing flows back.

The result: siloes that are individually reliable and collectively impenetrable. Order data lives in `OE_ORDER_HEADERS_ALL`; invoice data in `AP_INVOICES_ALL`; the customer service team in a CRM that talks to neither. When a customer asks “where is my order, and why hasn’t my invoice been adjusted?”, a human opens four screens in three systems and synthesizes an answer by hand.

That synthesis is exactly what modern AI agents do well — and what legacy ERP architectures make nearly impossible. LLMs need structured, permission-scoped, real-time access to operational data, but ERPs expose SQL, not APIs; authenticate by database roles, not OAuth2; and enforce security inside stored procedures, not at an application gateway. The data is there. The intelligence is there. The bridge is not.

This post walks through the modernization roadmap, the anatomy of a secure Model Context Protocol (MCP) connector for legacy databases, the authorization model that makes it safe, and a case study of a support organization that deflected 70% of its tickets through an ERP-integrated AI assistant.


—

## 2. Modernization Roadmap: From Monolithic Data to AI-Enabled Services

The first thing to unlearn is the instinct to modernize by replacing. A full Oracle EBS-to-SaaS migration is a multi-year program with an eight-figure budget. An AI-enablement engagement delivers value in six to ten weeks against the system you already run — and it funds the eventual platform decision, one deflected ticket at a time.

The roadmap that works is a five-phase strangler pattern, delivered in vertical slices:

**Phase 1 — Inventory and data contracts.** Before any agent touches a database, map what matters: the top 40 tables and views, the PL/SQL packages encapsulating business rules, the batch windows you must not query during, and the columns that are PII or financial-accounting-sensitive. The deliverable is not a document but a data contract per domain (orders, invoices, inventory, accounts) naming the queryable shape, cardinality, and retention rules.

**Phase 2 — Read replicas and change data capture.** AI agents should never run ad-hoc SELECTs against your production OLTP instance during close. Stand up a read replica per domain (Active Data Guard or a logical standby on Oracle; an Always On readable secondary on SQL Server) and stream changes with log-based CDC (GoldenGate or Change Tracking). Agents read operational truth with seconds of lag.

**Phase 3 — The semantic layer.** This is the highest-leverage step: wrap the replica schemas in a typed tool surface. Table names like `RA_CUSTOMER_TRX_ALL` are meaningless to an LLM and dangerous to expose raw. You define tools with names, descriptions, and JSON schemas — `get_order_status`, `lookup_invoice`, `check_return_eligibility` — each backed by a parameterized query written by an engineer, never generated ad hoc by the model.

**Phase 4 — Agent enablement with an eval suite.** Connect the tool surface to an LLM through MCP, then define “correct” before anything touches a customer: a gold set of 200 historical tickets, a latency budget, a per-conversation cost ceiling, and a rule that the model cites the records it used. You ship only when the eval passes.

**Phase 5 — Write-back with human checkpoints.** Read-only deflection is the beachhead; write operations (adjustments, returns, reclassification) follow once audit trails and approval workflows exist, with every write surfacing as a human-verifiable action.

The cardinal rule across all five phases: the agent adapts to the data, never the reverse. Modernization fails when you ask finance to reshape their chart of accounts for a chatbot; it succeeds when you reshape the access layer around the ERP you have.

—

## 3. Technical Bridge: Building Secure MCP Connectors for Legacy Databases

Model Context Protocol has become the de facto contract layer between LLMs and business logic, and it suits legacy databases precisely because it demands nothing from them. An MCP server is just a process: your Oracle EBS instance needs no new API, SDK, or cloud agent — only a connection string, a least-privilege database role, and a well-designed MCP server between the model and the schema.

MCP servers expose three primitives — **tools**, **resources**, **prompts** — and for ERP bridging, tools do the heavy lifting. The connector architecture has four layers:

1. **Transport** — streamable HTTP or SSE terminated behind your corporate gateway; never expose the MCP endpoint or the database to the public internet.
2. **Tool definitions** — one typed tool per business operation: a name, a description of when to use it, and a strict JSON schema.
3. **Query execution** — parameterized SQL or packaged-procedure calls with hard timeouts, row-count caps, and cost ceilings.
4. **Audit** — every invocation logged with caller identity, tool, arguments, and the correlation ID tying an agent conversation to a database session.

A tool definition for order status looks like this:

“`json
{
“name”: “get_order_status”,
“description”: “Returns current status, shipment tracking, and delivery ETA for a customer order. Use when a customer asks where their order is or when it will arrive.”,
“inputSchema”: {
“type”: “object”,
“properties”: {
“order_number”: { “type”: “string”, “description”: “The customer-facing order number” }
},
“required”: [“order_number”]
}
}
“`

Behind that tool sits a PL/SQL block or parameterized query against the order-management view — never a dynamically assembled SQL string containing raw model output. The LLM supplies *intent*; the connector supplies the *statement*. That single decision — model proposes, connector disposes — is what keeps prompt injection from becoming SQL injection.

Two details separate production connectors from demos. First, **session-level capability caps**: the connector connects with a role that can only `SELECT` the approved views — never `EXECUTE` arbitrary packages or touch `AP_*` base tables. Second, **runtime governance**: per-tenant or per-conversation rate limits, a maximum result size (an agent needs a summary and a drill-down tool, not 40,000 invoice rows), and a request budget that fails closed.

The same pattern ports to MS SQL Server: ODBC or JDBC connectors, parameterized `sp_executesql`, and views that flatten `Orders`, `OrderLines`, and `Customers` into shapes agents actually query. The bridge is symmetric; only the dialect changes.

—

## 4. Auth & Security: Row-Level Policies and OAuth2 for Legacy Systems

The objection that kills more ERP-AI projects than any technical failure is: *”We cannot give an LLM access to our ERP — it would see every customer’s data.”* The premise is wrong, and the fix is the most important engineering in this post: legacy databases already have a mature mechanism for exactly this problem, sitting unused for twenty years.

**Oracle Virtual Private Database (VPD / FGAC).** Oracle has shipped fine-grained access control since the 8i era: a policy function rewrites every query by appending a predicate derived from session context. Combined with an application context set at connection time, it delivers row-level security with zero application changes. After authenticating the caller, the MCP server sets the session context to the verified customer or employee identifier, and every query the connector issues is transparently scoped:

“`sql
— Set in the connector after OAuth2 token validation
DBMS_SESSION.SET_CONTEXT(‘ERP_CTX’, ‘CUSTOMER_ID’, :customer_id);

— VPD policy function appends: WHERE CUSTOMER_ID = SYS_CONTEXT(‘ERP_CTX’,’CUSTOMER_ID’)
“`

A customer-service agent’s LLM session is therefore *physically incapable* of retrieving another customer’s records — not because the prompt says “don’t,” but because the predicate is enforced by the database kernel. The equivalent on MS SQL Server is Row-Level Security with `SESSION_CONTEXT` and a security policy on the order and account tables. Defense at the data layer, not the prompt layer, is non-negotiable for regulated data.

**OAuth2 on top of a database-era identity.** Legacy ERPs authenticate users, not machines — and certainly not agents. The bridge terminates modern identity at the MCP gateway and translates it into the database session context:

– **Authorization Code + PKCE** for human-in-the-loop sessions, where a support agent is signed in with their corporate IdP (Entra ID, Okta, Ping) and the token’s claims determine which tools and which row scope they get.
– **Client Credentials** for unattended service agents, issued to a named service principal with a narrow `scope` such as `orders:read` or `invoices:read`, never a wildcard.
– **Scope-to-tool mapping** — a token scope maps to an MCP tool group, and a tool group maps to a database role. This three-layer indirection means revoking an OAuth token takes effect immediately even though the database role never changed.

Because the session context is set from *validated* token claims — customer ID from the `sub`, tenant from the `aud` — the database inherits an audit trail it can enforce and report on. Every row touched is attributable to a token, to a person or service principal, to an agent conversation with a correlation ID. That chain lets your compliance officer sleep at night and lets you demonstrate least privilege without a 200-slide deck.

Network hygiene completes the picture: gateway and database replicas live in a private subnet with no public ingress, TLS 1.2+ in transit, and mTLS to the model provider where self-hosted. You are not adding AI to your ERP; you are adding a well-governed API tier that happens to speak MCP.

—

## 5. Case Study: Deflecting 70% of Support Tickets Through ERP-Integrated AI

A North American industrial distributor ran financials on Oracle EBS R12 and its customer portal on MS SQL Server. Support volume had grown with the customer base; headcount had not. Sixty percent of tickets were tier-1 repeats — order status, invoice copies, payment schedules, return eligibility — each requiring an agent to log into EBS Forms or the portal, run three lookups, and paste the answer into a ticket. Average handle time was eleven minutes, and the backlog compounded quarterly.

The team followed the roadmap in vertical slices: week one mapped the order-to-cash surfaces and stood up read replicas with CDC; weeks two through four delivered seven MCP tools — `get_order_status`, `lookup_invoice`, `get_payment_schedule`, `check_return_eligibility`, `verify_account`, `get_shipping_updates`, `escalate_to_human` — each parameterized, row-scoped by VPD, and capped at a 3-second timeout; weeks five and six wired the tools to a chat assistant behind the portal’s existing OAuth2 login, seeded the eval suite with 200 historical tickets, and began shadow-testing.

The results after the first full quarter:

– **70% of eligible support tickets deflected.** Order-status, invoice, and payment questions resolved inside the chat with cited record numbers — no ticket created, no human touched it.
– **Account security preserved.** The VPD predicate meant the assistant could only read the authenticated customer’s own rows; adversarial cross-account attempts in the eval suite all failed at the database layer.
– **CSAT flat to improved.** Answers came in seconds, not hours. When confidence fell below threshold — a disputed charge, a damaged shipment, a question spanning two systems — the assistant handed off to a human with the context trail attached, so escalation cost seconds, not a re-explanation.
– **Human agents shifted to judgment work.** The same headcount now handled disputes, credit holds, and account reviews — with the assistant pre-fetching the relevant records into the agent’s console.

What mattered most to the CFO was not the deflection rate but that the entire capability shipped in under ten weeks against an untouched Oracle EBS instance — no change to the patch cadence, no migration risk. The roadmap’s first phase paid for itself before the second began.

—

## 6. The Future: AI-First ERP Environments

Once the bridge exists, the destination changes. An AI-first ERP is not one where a chatbot answers “where is my order?” — that is table stakes — but one where operational ERP data becomes a substrate every workflow runs on:

– **Order-to-cash agents** that reconcile payments against open invoices overnight and surface only the exceptions needing human decisions.
– **Procure-to-pay agents** that three-way-match purchase orders, receipts, and invoices, flag policy violations, and draft journal entries for review.
– **Supply-chain agents** that read inventory, lead times, and forecasts from the same tables your planners use, proposing replenishment with projected margin impact.
– **Finance copilots** that answer “why is gross margin down in the Southwest?” by traversing the ledger, cost distribution, and order history — every number traced to a source document.

Each case follows the same pattern as this post: a typed tool surface, database-inherited row-level security, OAuth2 at the edge, an eval suite, and human checkpoints on anything that writes. The ERP stops being a system of record you interrogate through forms and becomes a system of action your agents participate in — with the audit trail to prove it.

MCP is accelerating this shift the way ODBC accelerated client-server computing: it is a boring, universal contract that lets every model talk to every system the same way. The enterprises that win the next decade will not be the ones that replace their ERPs fastest, but the ones that bridge them first, measure the deflection, and reinvest the savings into the AI-first roadmap.

—

## Your ERP Already Has the Data. Let’s Build the Bridge.

OnlineCloud is a US-based engineering studio that ships MCP-native, agentic AI systems into Oracle EBS, MS SQL Server, and the tools your teams use every day — with row-level security, audit logging, and eval suites built in, not bolted on. Senior engineers only, no offshore handoffs, and a working vertical slice in your environment within six to ten weeks.

**Talk to an OnlineCloud architect about your ERP modernization roadmap** — email [contact@onlinecloud.dev](mailto:contact@onlinecloud.dev) or visit [onlinecloud.dev](https://www.onlinecloud.dev/). Free initial consultation, response within 24 hours.