Oracle MCP Utility

This project exposes Oracle-backed query and business-object utilities through three entry points:

• mcp_server.py: stdio MCP server for local MCP clients

• mcp_http_server.py: HTTP/SSE MCP server for remote MCP clients such as Cline

• http_server.py: REST wrapper for Postman and curl

It is designed to reuse the same underlying tool implementations across MCP and REST transports, with configuration-driven SQL mappings and business-object metadata.

What This Project Does

The service connects to Oracle using python-oracledb and supports:

• Executing registered SQL statements by key

• Executing ad hoc SQL with optional bind parameters

• Returning business-object data such as joint venture invoicing partner details

• Exposing discovery metadata for supported business units and business objects

• Serving the same capabilities over stdio MCP, streamable HTTP, SSE, and REST

Project Structure

• mcp_server.py: Core FastMCP server and tool definitions

• mcp_http_server.py: MCP-over-HTTP or SSE transport entrypoint

• http_server.py: REST API wrapper around the same MCP tool functions

• db_oracle.py: Oracle connection and query execution helper

• db_config.json: Default Oracle connection settings

• requirements.txt: Python dependencies

• config/registry.json: Combined registry for tools, business units, business objects, and SQL mappings

• config/sql_registry.json: Friendly SQL key to file-path mapping

• config/business_units.json: Business unit configuration

• config/business_objects.json: Business object metadata

• sql/: SQL files used by the registered tools

• payloads/: Sample request payloads

• scripts/setup_sse_local.sh: Linux VM setup and service management script

• logs/: Runtime log files

Main Tools

The core MCP server exposes these tools:

• executeSql: Execute a SQL file registered in config/sql_registry.json

• oracle_query: Execute arbitrary SQL with optional bind parameters and connection overrides

• getDetails: Resolve configured business objects such as jointVentureInvoicingPartner

• getDistribution: Return rows from the distributions query

• jointVentureInvoicingPartners: Convenience partner query endpoint

• getCapabilities: Return discovery metadata for supported business objects and business units

Requirements

• Python 3.10 or newer recommended

• Network access to the target Oracle database

• Optional Linux VM for long-running HTTP or SSE deployment

• Optional systemd if you want to run the service as a managed Linux service

Install dependencies in a virtual environment:

python3 -m venv venv
source venv/bin/activate
pip install --upgrade pip
pip install -r requirements.txt

For Windows:

python -m venv venv
.\venv\Scripts\Activate.ps1
pip install --upgrade pip
pip install -r requirements.txt

Configuration

The default Oracle connection is read from db_config.json.

Expected shape:

{
  "username": "fusion",
  "password": "fusion",
  "dsn": "(DESCRIPTION=...)",
  "tns_admin": ""
}

Notes:

• Per-request dsn, username, password, and tns_admin values can override the defaults.

• python-oracledb runs in thin mode by default, which is Linux-friendly and does not require Oracle Instant Client.

• If you use a TNS alias, set tns_admin so Oracle Net can resolve it.

• Keep real credentials out of source control in shared environments.

Running the Servers

1. Stdio MCP server

Use this when your MCP client launches the process directly.

python3 mcp_server.py

2. HTTP or SSE MCP server

Use this for remote MCP clients such as Cline.

Streamable HTTP:

python3 mcp_http_server.py --host 0.0.0.0 --port 8000

Typical endpoint:

http://<server-ip>:8000/mcp

SSE mode:

python3 mcp_http_server.py --host 0.0.0.0 --port 8000 --sse

Typical endpoint:

http://<server-ip>:8000/sse

3. REST wrapper for Postman or curl

python3 http_server.py --host 0.0.0.0 --port 5001

REST Endpoints

The HTTP wrapper and MCP HTTP entrypoint expose helper REST endpoints such as:

• GET /health on http_server.py

• GET /tools

• GET /capabilities

• POST /oracle_query

• POST /executeSql

• POST /getDetails

• GET or POST /getDistribution

• POST /jointVentureInvoicingPartners

Example Requests

Discover capabilities first:

curl -s http://localhost:8000/capabilities

Execute a registered SQL query:

curl -X POST http://localhost:8000/executeSql \
  -H "Content-Type: application/json" \
  -d '{
    "sqlKey": "vendors_by_bu",
    "params": {
      "business_unit": "HEPP Petroleum USA BU1"
    }
  }'

Run an ad hoc SQL query:

curl -X POST http://localhost:5001/oracle_query \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "select * from fusion.jv_distributions where rownum <= 1",
    "params": {}
  }'

Query a business object:

curl -X POST http://localhost:8000/getDetails \
  -H "Content-Type: application/json" \
  -d '{
    "BusinessObject": "jointVentureInvoicingPartner",
    "BusinessUnit": "HEPP Petroleum USA BU1"
  }'

Linux Setup Script

For Linux VM deployment, use:

chmod +x scripts/setup_sse_local.sh
scripts/setup_sse_local.sh install

The script can:

• Create a virtual environment

• Install requirements

• Start the MCP server in SSE or HTTP mode

• Optionally start the REST wrapper

• Run services with systemd or nohup

• Show status, stop, and uninstall services

Common commands:

scripts/setup_sse_local.sh install
scripts/setup_sse_local.sh start
scripts/setup_sse_local.sh status
scripts/setup_sse_local.sh stop
scripts/setup_sse_local.sh uninstall

SQL and Metadata Configuration

The project uses configuration files to avoid hardcoding query mappings:

• config/registry.json: consolidated source for tools, SQL paths, business units, and business objects

• config/sql_registry.json: alternate SQL key registry

• sql/vendors_by_bu.sql and sql/accounts_by_bu.sql: partner lookup queries

• sql/distributions.sql: distribution query

The getCapabilities tool is a good first call for clients that need to discover valid business units and supported business objects dynamically.

Logging

• mcp_server.py writes a log file into the system temp directory as fa_utility_mcp_server.log

• http_server.py and mcp_http_server.py log request activity to standard output

• logs/ is used by the Linux setup script for service logs

Development Notes

• mcp_server.py uses FastMCP and falls back between import paths to support different package layouts.

• http_server.py and mcp_http_server.py import tool functions directly from mcp_server.py so behavior stays consistent.

• The project is structured so new SQL files and business objects can be added with minimal code changes.

Suggested Next Improvements

• Move secrets from db_config.json into environment variables or a secrets manager

• Add automated tests for each endpoint and business-object path

• Add request and response schema validation

• Add a sample .env.example file

• Add deployment notes for reverse proxy and TLS termination if this is exposed beyond a private network