OpenAI Operator integrating with Oracle EBS using `cx_Oracle`

This guide provides a blueprint for connecting an OpenAI Operator (or high-level AI Agent) to Oracle E-Business Suite (EBS).

We use the Model Context Protocol (MCP) to standardize the connection. The server allows an AI agent to query inventory levels and check purchase orders directly from the Oracle database using the legacy cx_Oracle driver, while the Agent consumes these tools via a standardized SSE transport.

🚀 Architecture

This integration uses the FastMCP Python framework to serve Oracle EBS data over an SSE (Server-Sent Events) transport. This allows the OpenAI Operator to “subscribe” to the legacy database tools without complex synchronous API management.

Key Components

Server: Python FastMCP running inside Docker.
Driver: cx_Oracle (requires Oracle Instant Client).
Transport: SSE (Server-Sent Events) on Port 8000.
Client: An AI Agent (e.g., CrewAI) configured with the mcps list.

🛠️ Prerequisites

Oracle Instant Client: cx_Oracle requires the native Oracle client libraries. Our Dockerfile below handles this automatically.
Oracle EBS Credentials:
- Host, Port, Service Name (SID).
- Username/Password (e.g., APPS user).
Network Access: Your container must be able to reach the Oracle DB port (usually 1521).

👨‍💻 Server Implementation

1. `server.py`

This file defines the MCP server and the tools available to the Agent.

import os
import cx_Oracle
from fastmcp import FastMCP

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

# --- Configuration ---
# Ensure your container has network access (e.g. via NordLayer)
DB_USER = os.getenv("ORACLE_USER", "APPS")
DB_PASSWORD = os.getenv("ORACLE_PASSWORD", "welcome1")
DB_DSN = os.getenv("ORACLE_DSN", "ebs.example.com:1521/VIS")

def get_connection():
    """Establishes a connection to Oracle EBS using cx_Oracle."""
    try:
        connection = cx_Oracle.connect(
            user=DB_USER,
            password=DB_PASSWORD,
            dsn=DB_DSN,
            encoding="UTF-8"
        )
        return connection
    except cx_Oracle.DatabaseError as e:
        error, = e.args
        raise RuntimeError(f"Oracle Connection Error: {error.code} - {error.message}")

@mcp.tool()
def get_inventory_quantity(item_number: str, organization_code: str) -> str:
    """
    Queries Oracle EBS for on-hand quantity of a specific item in an organization.

    Args:
        item_number: The part number (e.g., 'AS54888').
        organization_code: The warehouse code (e.g., 'M1').
    """
    sql = """
        SELECT
            msi.segment1 as item_number,
            mp.organization_code,
            SUM(moq.transaction_quantity) as total_on_hand
        FROM
            inv.mtl_onhand_quantities_detail moq
            JOIN inv.mtl_system_items_b msi ON moq.inventory_item_id = msi.inventory_item_id
            JOIN inv.mtl_parameters mp ON moq.organization_id = mp.organization_id
        WHERE
            msi.segment1 = :item_num
            AND mp.organization_code = :org_code
        GROUP BY
            msi.segment1, mp.organization_code
    """

    conn = None
    try:
        conn = get_connection()
        cursor = conn.cursor()
        cursor.execute(sql, item_num=item_number, org_code=organization_code)
        row = cursor.fetchone()

        if row:
            return f"Item {item_number} in {organization_code}: {row[2]} units on hand."
        else:
            return f"No inventory found for {item_number} in {organization_code}."

    except Exception as e:
        return f"Error executing query: {str(e)}"
    finally:
        if conn:
            conn.close()

@mcp.tool()
def check_po_status(po_number: str) -> str:
    """
    Checks the authorization status of a Purchase Order in Oracle EBS.
    """
    sql = """
        SELECT
            segment1 as po_number,
            authorization_status,
            type_lookup_code
        FROM
            po.po_headers_all
        WHERE
            segment1 = :po_num
    """

    conn = None
    try:
        conn = get_connection()
        cursor = conn.cursor()
        cursor.execute(sql, po_num=po_number)
        row = cursor.fetchone()

        if row:
            return f"PO {row[0]}: Status is {row[1]} (Type: {row[2]})"
        else:
            return f"PO {po_number} not found."

    except Exception as e:
        return f"Error executing query: {str(e)}"
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    # Binds to 0.0.0.0 to allow external access from the Agent
    mcp.run(transport='sse', host='0.0.0.0', port=8000)

2. `Dockerfile`

Because cx_Oracle requires binary dependencies, we must install the Oracle Instant Client libraries in the container.

# Use a slim Python base
FROM python:3.11-slim

# Install system dependencies required for Oracle Instant Client
RUN apt-get update && apt-get install -y \
    libaio1 \
    wget \
    unzip \
    && rm -rf /var/lib/apt/lists/*

# Setup Oracle Instant Client (Basic Lite)
# Adjust version if necessary for your specific Oracle DB version
WORKDIR /opt/oracle
RUN wget https://download.oracle.com/otn_software/linux/instantclient/2113000/instantclient-basiclite-linux.x64-21.13.0.0.0.zip \
    && unzip instantclient-basiclite-linux.x64-21.13.0.0.0.zip \
    && rm instantclient-basiclite-linux.x64-21.13.0.0.0.zip \
    && cd instantclient* \
    && rm -f *jdbc* *occi* *mysql* *jar* *uid* *readme* \
    && echo /opt/oracle/instantclient_21_13 > /etc/ld.so.conf.d/oracle-instantclient.conf \
    && ldconfig

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

# Application Setup
WORKDIR /app
COPY server.py .

# Install Python dependencies
RUN pip install fastmcp cx_Oracle

# EXPOSE 8000 for Railway compatibility
EXPOSE 8000

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

🔌 Client Connectivity

The Agent connects to the Dockerized MCP server using the standard mcps configuration pattern. Below is an example using the CrewAI framework to instantiate the Operator.

Agent Configuration (CrewAI Example)

from crewai import Agent, Task, Crew

# 1. Define the Oracle Operator
# We connect to the Docker container via the SSE endpoint
oracle_operator = Agent(
    role="Oracle EBS Supply Chain Analyst",
    goal="Monitor inventory and purchase order status in the legacy Oracle ERP",
    backstory="You are an expert in Oracle E-Business Suite R12. You have direct database access.",
    # The 'mcps' list defines the connection to the FastMCP server
    mcps=["http://localhost:8000/sse"],
    verbose=True,
    allow_delegation=False
)

# 2. Define the Task
check_inventory_task = Task(
    description="Check the stock level for item 'AS54888' in warehouse 'M1'. Also check the status of PO '10045'.",
    expected_output="A summary of the item quantity and the PO status.",
    agent=oracle_operator
)

# 3. Execute
crew = Crew(
    agents=[oracle_operator],
    tasks=[check_inventory_task],
    verbose=True
)

result = crew.kickoff()
print("Final Report:\n", result)

Explanation

mcps=["http://localhost:8000/sse"]: This tells the Agent to automatically discover tools (get_inventory_quantity, check_po_status) exposed by your Docker container.
Auto-Discovery: The Agent performs a handshake, retrieves the tool definitions, and injects them into its context window.
Execution: When the Agent needs to check inventory, it generates the JSON payload, sends it to the Docker container, and the Python server.py executes the SQL against Oracle EBS.

🔍 Troubleshooting `cx_Oracle`

DPI-1047: Cannot locate a 64-bit Oracle Client library:
- Cause: The Docker container is missing libaio1 or the LD_LIBRARY_PATH is incorrect.
- Fix: Ensure the RUN commands in the Dockerfile that install libaio1 and run ldconfig executed successfully.
ORA-12170: TNS:Connect timeout occurred:
- Cause: The Docker container cannot reach the Oracle EBS host.
- Fix: Check if your EBS is behind a VPN. If so, you may need to run the container with --network host (local dev) or configure a VPN sidecar (production).
Encoding Issues:
- Legacy EBS systems often use US7ASCII. If you see garbled text, change encoding="UTF-8" in server.py to the encoding matching your NLS_LANG setting.

🛡️ 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

🚀 Architecture

Key Components

🛠️ Prerequisites

👨‍💻 Server Implementation

1. server.py

2. Dockerfile

🔌 Client Connectivity

Agent Configuration (CrewAI Example)

Explanation

🔍 Troubleshooting cx_Oracle

🛡️ Quality Assurance

OpenAI Operator integrating with Oracle EBS using `cx_Oracle`

1. `server.py`

2. `Dockerfile`

🔍 Troubleshooting `cx_Oracle`