Semantic Kernel plugins for Oracle EBS integration (.NET)

Semantic Kernel Plugins for Oracle EBS Integration (.NET) via Python MCP

Slug: semantic-kernel-oracle-ebs-dotnet

The “Polyglot” Bridge for Legacy Systems

While your production orchestration might handle business logic in .NET (C#) using Microsoft Semantic Kernel, the data connectivity layer for Oracle EBS often requires the specific handling found in Python’s oracledb ecosystem.

This guide implements the Model Context Protocol (MCP) server in Python. To ensure the server is functioning correctly before integrating it into a .NET application, we provide a standard Python Agent (agent.py) using CrewAI to validate the connection.

🏗️ Architecture

Server (Python): Runs FastMCP. Connects to Oracle EBS using python-oracledb.
Transport: Server Sent Events (SSE) on Port 8000.
Client (Verification): A Python Agent configured with the mcps parameter to consume the tools.

🛠️ Step 1: The Python MCP Server

We use fastmcp to create a lightweight server. This server handles the Oracle connection string, TNS listeners, and query execution.

Prerequisites:

pip install fastmcp oracledb

`server.py`

import os
import oracledb
from fastmcp import FastMCP

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

# Connection Pool (Lazy initialization)
pool = None

def get_connection():
    global pool
    if pool is None:
        # Connect using Thin mode (no Instant Client needed for basic connectivity)
        pool = oracledb.create_pool(
            user=os.getenv("ORACLE_USER", "APPS"),
            password=os.getenv("ORACLE_PASSWORD", "welcome1"),
            dsn=os.getenv("ORACLE_DSN", "localhost:1521/VIS"),
            min=1,
            max=5,
            increment=1
        )
    return pool.acquire()

@mcp.tool()
def search_suppliers(keyword: str) -> str:
    """
    Search for suppliers in Oracle EBS (AP_SUPPLIERS) by name.
    Useful for finding vendor IDs.
    """
    conn = get_connection()
    cursor = conn.cursor()
    try:
        sql = """
            SELECT vendor_id, vendor_name, segment1
            FROM ap_suppliers
            WHERE upper(vendor_name) LIKE upper(:kw)
            AND ROWNUM <= 5
        """
        cursor.execute(sql, kw=f"%{keyword}%")
        rows = cursor.fetchall()

        if not rows:
            return "No suppliers found matching that keyword."

        result = "Found Suppliers:\n"
        for row in rows:
            result += f"- ID: {row[0]}, Name: {row[1]}, Number: {row[2]}\n"
        return result
    except Exception as e:
        return f"Database Error: {str(e)}"
    finally:
        cursor.close()
        conn.close()

@mcp.tool()
def get_invoice_status(invoice_num: str) -> str:
    """
    Check the status and amount of an AP Invoice by Invoice Number.
    """
    conn = get_connection()
    cursor = conn.cursor()
    try:
        sql = """
            SELECT invoice_id, invoice_amount, amount_paid, payment_status_flag
            FROM ap_invoices_all
            WHERE invoice_num = :inv_num
        """
        cursor.execute(sql, inv_num=invoice_num)
        row = cursor.fetchone()

        if not row:
            return f"Invoice {invoice_num} not found."

        status_map = {'Y': 'Paid', 'N': 'Unpaid', 'P': 'Partial'}
        status = status_map.get(row[3], 'Unknown')

        return (f"Invoice Details:\nID: {row[0]}\nTotal: {row[1]}\n"
                f"Paid: {row[2]}\nStatus: {status}")
    except Exception as e:
        return f"Error retrieving invoice: {str(e)}"
    finally:
        cursor.close()
        conn.close()

if __name__ == "__main__":
    # Ensure usage of 0.0.0.0 for Docker container exposure
    mcp.run(transport='sse', host='0.0.0.0', port=8000)

🐳 Step 2: Dockerfile

This Dockerfile ensures the Python environment is self-contained. We use python-oracledb in “Thin” mode.

`Dockerfile`

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

# Set working directory
WORKDIR /app

# Install system dependencies
RUN apt-get update && apt-get install -y --no-install-recommends \
    curl \
    && rm -rf /var/lib/apt/lists/*

# Install Python libraries
RUN pip install --no-cache-dir fastmcp oracledb uvicorn

# Copy the server code
COPY server.py .

# Environment variables
ENV ORACLE_USER=APPS
ENV ORACLE_PASSWORD=welcome1
ENV ORACLE_DSN=host.docker.internal:1521/VIS

# Ensure your container has network access (e.g. via NordLayer)
# This is critical if connecting to an on-prem Oracle DB over VPN.

# Expose the MCP port for Railway/Docker compatibility
EXPOSE 8000

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

🔌 Step 3: Client Verification (`agent.py`)

To verify the MCP server is working correctly before integrating it into your main .NET/C# application, use this Python script. This demonstrates the standard mcps connection pattern required for MCP-compliant agents.

`agent.py`

from crewai import Agent, Task, Crew
import os

# This agent connects to the running Docker container via SSE
# It uses the tools exposed by the Oracle EBS MCP server.

oracle_agent = Agent(
    role='Oracle EBS Specialist',
    goal='Retrieve invoice status and supplier details from Oracle',
    backstory='You are a database specialist who checks legacy Oracle systems.',
    verbose=True,
    # Connect to the MCP Server running on port 8000
    mcps=["http://localhost:8000/sse"]
)

task = Task(
    description="Check the status of invoice #INV-2024-001 and look for suppliers named 'Acme'.",
    expected_output="A summary of the invoice status and a list of matching suppliers.",
    agent=oracle_agent
)

crew = Crew(
    agents=[oracle_agent],
    tasks=[task]
)

if __name__ == "__main__":
    result = crew.kickoff()
    print("\n\n########################")
    print("## Oracle Integration Result ##")
    print("########################\n")
    print(result)

🛡️ Quality Assurance

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

Transparency: This page may contain affiliate links.