Sankhya Community Search Server

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

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:

Variable	Required	Description
ORACLE_{NAME}_USER	yes	Oracle username
ORACLE_{NAME}_PASSWORD	yes	Oracle password
ORACLE_{NAME}_DSN	yes	host:port/service_name
ORACLE_{NAME}_SCHEMA	no	Schema prefix for ALTER SESSION SET CURRENT_SCHEMA (default: {NAME} uppercased)
ORACLE_{NAME}_DESCRIPTION	no	Human-readable description shown in list_schemas
ORACLE_{NAME}_READONLY	no	Set "true" to block DML/DDL on this connection

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.

Parameter	Type	Default	Description
query	str	required	SQL statement (SELECT / WITH / EXPLAIN PLAN)
connection	str	null	Connection name from list_schemas, or null for default
params	dict	null	Bind parameters (:name style)
max_rows	int	500	Maximum rows returned
offset	int	0	Rows to skip before returning results

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.