Content
# 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](https://github.com/jlowin/fastmcp) and managed with [uv](https://docs.astral.sh/uv/)
## Prerequisites
- Python 3.11+
- `uv` — [install guide](https://docs.astral.sh/uv/getting-started/installation/)
- 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:
```bash
uv sync
```
2. Configure environment variables in `.vscode/mcp.json` (copy from the template in this repo — the file is git-ignored):
```json
{
"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)
```json
"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`):
```sql
-- 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.
