SankhyaMCP

MCP (Model Context Protocol) server for the Sankhya ERP Oracle database. Exposes safe SQL execution over multiple named Oracle connections to AI agents via the MCP protocol.

Overview

• Dynamic multi-connection Oracle support — define any number of named connections via environment variables

• Read-only tool (execute_query) enforces SELECT/WITH/EXPLAIN only

• Writable tool (execute_unsafe_query) for DML/DDL — blocked on read-only connections

• CSV output for SELECT queries; success message for DML/DDL

• Never raises — all errors returned as "Erro[...]: ..." strings

• Built with FastMCP and managed with uv

Related MCP server: MCP Sankhya Community Search Server

Prerequisites

• Python 3.11+

• uv — install guide

• No Oracle Client required (uses oracledb thin mode)

• Oracle database accessible from the machine running the server

Setup

1. Clone the repository and install dependencies:

uv sync

2. Configure environment variables in .vscode/mcp.json (copy from the template in this repo — the file is git-ignored):

{
  "servers": {
    "sankhya-mcp": {
      "type": "stdio",
      "command": "${workspaceFolder}/.venv/Scripts/python.exe",
      "args": ["${workspaceFolder}/mcp_server.py"],
      "env": {
        "ORACLE_CONNECTIONS": "teste,producao",
        "ORACLE_TESTE_USER": "your_user",
        "ORACLE_TESTE_PASSWORD": "your_password",
        "ORACLE_TESTE_DSN": "host:port/service_name",
        "ORACLE_PRODUCAO_USER": "your_user",
        "ORACLE_PRODUCAO_PASSWORD": "your_password",
        "ORACLE_PRODUCAO_DSN": "host:port/service_name",
        "ORACLE_PRODUCAO_READONLY": "true",
        "ORACLE_DEFAULT_CONNECTION": "teste"
      }
    }
  }
}

3. Open the project in VS Code — the MCP panel will detect the server automatically.

Configuring Connections

Connections are defined entirely via environment variables — no code changes required.

1. Declare connection names

ORACLE_CONNECTIONS=teste,producao,SUP

Comma-separated list of names. If omitted, the server falls back to the built-in defaults (teste and producao).

2. Define credentials for each connection

For each name {NAME} declared above, set:

3. Set the default connection (optional)

ORACLE_DEFAULT_CONNECTION=SUP

If omitted, the first connection in ORACLE_CONNECTIONS is used.

Full example (mcp.json env block)

"env": {
  "ORACLE_CONNECTIONS": "teste,SUP",
  "ORACLE_TESTE_USER": "user_teste",
  "ORACLE_TESTE_PASSWORD": "pass_teste",
  "ORACLE_TESTE_DSN": "host:1521/ORCL",
  "ORACLE_TESTE_SCHEMA": "TESTE",
  "ORACLE_TESTE_DESCRIPTION": "Base de teste",
  "ORACLE_SUP_USER": "user_sup",
  "ORACLE_SUP_PASSWORD": "pass_sup",
  "ORACLE_SUP_DSN": "host:1521/SUPORTE",
  "ORACLE_SUP_SCHEMA": "SUPORTE",
  "ORACLE_SUP_DESCRIPTION": "Base de suporte",
  "ORACLE_SUP_READONLY": "true",
  "ORACLE_DEFAULT_CONNECTION": "teste"
}

Available MCP Tools

list_schemas

Lists all available Oracle connections. No parameters. Returns CSV.

Columns: name, schema, description, readonly, is_default

Example response:

name,schema,description,readonly,is_default
teste,TESTE,Base de teste,False,True
producao,SANKHYA,Base de produção,True,False

execute_query

Executes a read-only SQL query (SELECT, CTEs, EXPLAIN PLAN). DML/DDL is rejected.

Returns CSV (with header row) for SELECT queries. Returns "Erro[...]: ..." on failure.

execute_unsafe_query

Executes any SQL including INSERT, UPDATE, DELETE, DDL, and PL/SQL blocks. Blocked on connections where readonly=True.

Same parameters as execute_query. Requires explicit user confirmation before use.

Returns "Sucesso: Comando executado (sem retorno)." for DML/DDL with no result set, or CSV if the statement returns rows.

Example Queries

Always prefix table names with the connection's schema (or rely on ALTER SESSION SET CURRENT_SCHEMA):

-- List recent sales orders
SELECT NUNOTA, DTNEG, CODPARC, VLRNOTA
FROM TGFCAB
WHERE DTNEG >= SYSDATE - 30
ORDER BY DTNEG DESC

-- Check a partner with bind param
SELECT CODPARC, NOMEPARC, CGC_CPF
FROM TGFPAR
WHERE CODPARC = :codparc

Using the MCP tools:

list_schemas                                        → discover connections
execute_query("SELECT ...", "teste")                → read from test DB
execute_query("SELECT ...", "teste", offset=500)    → paginate results
execute_unsafe_query("INSERT ...", "teste")         → write to test DB

Project Structure

mcp_server.py       # FastMCP entry point
src/
  oracle/
    config.py       # ConnectionConfig dataclass + env loading
    query.py        # execute_query() + _is_safe_query() guard
    schemas.py      # list_schemas_info() + list_schemas_csv()
    tools.py        # register_tools(mcp) — wires MCP tools
    __init__.py     # re-exports
tests/
  test_oracle.py    # pytest unit tests

Notes

• Referencias/ contains reference implementations used during development. It is git-ignored and not part of this project.

• DML/DDL should only target writable connections (readonly=false).

• For large result sets, use offset + max_rows to paginate.