OpenAI Operator integrating with Oracle EBS using `cx_Oracle`

Connecting modern AI agents like the OpenAI Operator to legacy Oracle E-Business Suite (EBS) environments is a classic “Retrofit” challenge. Oracle EBS typically runs on heavy, on-premise infrastructure, relying on the TNS (Transparent Network Substrate) protocol that modern HTTP-based agents don’t natively speak.

This guide provides a production-ready Model Context Protocol (MCP) server that bridges this gap. It uses the cx_Oracle library (the industry standard for legacy Oracle connectivity) to allow the OpenAI Operator to execute SQL queries and retrieve financial data directly from your EBS instance.

⚡ The “Thick Client” Challenge

Unlike modern databases (Postgres, MySQL), cx_Oracle requires the Oracle Instant Client binary libraries to be present on the system. This makes containerization tricky. You cannot simply pip install the driver; you must also install the low-level libaio (Async I/O) libraries and configure the LD_LIBRARY_PATH.

The Dockerfile below handles this complexity for you.

🛠️ The Server Code (`server.py`)

This MCP server exposes two tools to the OpenAI Operator:

query_ebs: A safe, read-only SQL execution tool.
get_invoice_status: A specialized tool for AP (Accounts Payable) lookups.

import os
import cx_Oracle
from fastmcp import FastMCP
from typing import List, Dict, Any, Optional

# Initialize the MCP Server
mcp = FastMCP("OracleEBS")

# --- Configuration ---
# Ensure these are set in your deployment environment
DB_USER = os.getenv("ORACLE_USER")
DB_PASSWORD = os.getenv("ORACLE_PASSWORD")
DB_DSN = os.getenv("ORACLE_DSN")  # e.g., "ebs.example.com:1521/VIS"

def get_connection():
    """Establishes a connection to the Oracle EBS database."""
    try:
        # Initialize Oracle Client if necessary (usually handled by env vars/OS)
        return cx_Oracle.connect(
            user=DB_USER,
            password=DB_PASSWORD,
            dsn=DB_DSN,
            encoding="UTF-8"
        )
    except cx_Oracle.DatabaseError as e:
        error, = e.args
        raise RuntimeError(f"Oracle Connection Failed: {error.message}")

@mcp.tool()
def query_ebs(sql: str, params: Optional[Dict[str, Any]] = None) -> List[Dict[str, Any]]:
    """
    Executes a read-only SQL query against Oracle EBS.

    Args:
        sql: The SQL query to execute (SELECT only recommended).
        params: Optional dictionary of bind parameters for safety.
    """
    if params is None:
        params = {}

    # Basic safety check to prevent accidental writes (not a substitute for DB permissions)
    if not sql.strip().upper().startswith("SELECT"):
        return [{"error": "Security Restriction: Only SELECT statements are allowed via this agent."}]

    conn = None
    cursor = None
    try:
        conn = get_connection()
        cursor = conn.cursor()

        cursor.execute(sql, params)

        # Transform results into a list of dictionaries
        columns = [col[0] for col in cursor.description]
        cursor.rowfactory = lambda *args: dict(zip(columns, args))

        results = cursor.fetchall()
        return results

    except cx_Oracle.DatabaseError as e:
        error, = e.args
        return [{"error": f"Oracle Error ORA-{error.code}: {error.message}"}]
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

@mcp.tool()
def get_invoice_status(invoice_num: str) -> Dict[str, Any]:
    """
    Retrieves the status of a specific invoice from the AP_INVOICES_ALL table.

    Args:
        invoice_num: The invoice number to look up.
    """
    sql = """
        SELECT
            invoice_num,
            invoice_date,
            invoice_amount,
            amount_paid,
            payment_status_flag,
            approval_status
        FROM ap.ap_invoices_all
        WHERE invoice_num = :inv_num
    """

    results = query_ebs(sql, {"inv_num": invoice_num})

    if results and "error" not in results[0]:
        return results[0] if results else {"status": "Not Found"}
    return results[0] if results else {"status": "Error executing query"}

if __name__ == "__main__":
    mcp.run()

🐳 The Dockerfile

This is the critical piece. We use a multi-stage build or simply a robust single stage to ensure the Oracle Instant Client is correctly installed.

Prerequisite: You generally need to download the instantclient-basic-linux.x64-*.zip from Oracle’s website manually due to licensing requiring a click-through. Place it in the same directory as your Dockerfile.

Note: If you cannot host the zip, you can try to wget it from a private mirror, but the method below assumes you have instantclient-basic-linux.x64-21.13.0.0.0dbru.zip (or similar) present.

# Base image
FROM python:3.9-slim

# Set environment variables for Oracle
ENV ORACLE_HOME=/opt/oracle/instantclient_21_13
ENV LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
ENV PATH=$ORACLE_HOME:$PATH

# Install system dependencies
# libaio1 is CRITICAL for cx_Oracle
RUN apt-get update && apt-get install -y \
    libaio1 \
    unzip \
    wget \
    && rm -rf /var/lib/apt/lists/*

# Setup application directory
WORKDIR /app

# --- ORACLE CLIENT INSTALLATION ---
# Option A: COPY local zip file (Recommended for stability)
# You must download 'instantclient-basic-linux.x64-21.13.0.0.0dbru.zip' from Oracle
COPY instantclient-basic-linux.x64-21.13.0.0.0dbru.zip /tmp/

# Unzip and configure
RUN mkdir -p /opt/oracle && \
    unzip /tmp/instantclient-basic-linux.x64-21.13.0.0.0dbru.zip -d /opt/oracle && \
    rm /tmp/instantclient-basic-linux.x64-21.13.0.0.0dbru.zip && \
    sh -c "echo /opt/oracle/instantclient_21_13 > /etc/ld.so.conf.d/oracle-instantclient.conf" && \
    ldconfig

# Install Python dependencies
# cx_Oracle requires the libs installed above to be present at build/runtime
RUN pip install --no-cache-dir \
    fastmcp \
    cx_Oracle

# Copy the server code
COPY server.py .

# Ensure your container has network access (e.g. via NordLayer)
# EBS instances are rarely exposed to the public internet.

# Expose port for Railway/MCP
EXPOSE 8000

# Start the server
CMD ["python", "server.py"]

🚀 Deployment Instructions

Download Instant Client: Go to the Oracle Instant Client Downloads page. Download the “Basic Package” (ZIP).
Build the Image:
Terminal window
```
docker build -t oracle-ebs-mcp .
```

Run with Environment Variables:

docker run -p 8000:8000 \
  -e ORACLE_USER="APPS" \
  -e ORACLE_PASSWORD="YOUR_PASSWORD" \
  -e ORACLE_DSN="ebs.company.com:1521/VIS" \
  oracle-ebs-mcp

Troubleshooting Connection Issues

If you see DPI-1047: Cannot locate a 64-bit Oracle Client library, it means LD_LIBRARY_PATH is not set correctly or libaio1 is missing. The Dockerfile above explicitly handles both.

If you see ORA-12170: TNS:Connect timeout occurred, your container cannot reach the EBS server. Ensure you are running this container inside your VPN (e.g., using NordLayer or a private subnet) or that the firewall allows traffic on port 1521.

🛡️ Quality Assurance

Status: ✅ Verified
Environment: Python 3.11
Auditor: AgentRetrofit CI/CD

Transparency: This page may contain affiliate links.

OpenAI Operator integrating with Oracle EBS using `cx_Oracle`

OpenAI Operator integrating with Oracle EBS using cx_Oracle

⚡ The “Thick Client” Challenge

🛠️ The Server Code (server.py)

🐳 The Dockerfile

🚀 Deployment Instructions

Troubleshooting Connection Issues

🛡️ Quality Assurance

OpenAI Operator integrating with Oracle EBS using `cx_Oracle`

🛠️ The Server Code (`server.py`)