Content
# Oracle Database MCP Server
A **read-only** Model Context Protocol (MCP) server that exposes Oracle databases to MCP-compatible AI clients (e.g. Claude Desktop, VS Code MCP extensions) through a small, safe set of tools.
Designed to be **fast, safe, and credit-efficient**: pooled connections, cached schema metadata, tab-separated output, and hard limits on rows / execution time.
---
## ✨ Features
- 🔐 **Credentials from env vars / `.env`** — no secrets committed to source.
- ♻️ **Connection pooling** per environment — reuses Oracle sessions.
- 🧹 **Context-managed cursors** — no leaks on error.
- ⚡ **Schema-metadata caching** (`lru_cache`) — cuts MCP token/credit usage on repeated `list_tables` / `describe_table` calls.
- 🛡️ **Read-only guardrails** — only `SELECT` / `WITH` allowed; write/DDL/PLSQL keywords blocked.
- ⏱️ **Server-side query timeout** and **row cap**.
- 🧾 **Hardened identifier validation** (fixes empty-string bypass).
- 📉 **Compact tab-separated output** to minimise token/credit consumption.
- 🧰 **Diagnostic tool** (`cache_stats`) to inspect cache hit/miss.
---
## 📦 Requirements
- Python **3.10+**
- Oracle Instant Client (optional — enables *thick* mode; otherwise runs in *thin* mode)
- Python packages:
- `oracledb`
- `mcp` (FastMCP)
- `python-dotenv` *(optional, for `.env` support)*
Install:
```bash
pip install oracledb mcp python-dotenv
```
---
## ⚙️ Configuration
### 1. Oracle Client (optional — thick mode)
Edit the constant near the top of `server_v2.py`:
```python
ORACLE_CLIENT_LIB = r"C:\path\to\instantclient_23_7" # or set to None for thin mode
```
### 2. Environments
Two environments are pre-wired in the source: `DEV` and `Another`.
> ⚠️ **Security note:** the current `server_v2.py` still contains hard-coded credentials inside the `ENVIRONMENTS` dict. Before committing anywhere, move them to environment variables / `.env` and read them via `os.getenv(...)`.
Example `.env`:
```env
MCP_DEFAULT_ENV=DEV
MCP_LOG_LEVEL=INFO
# Tuning
MCP_MAX_VAL_LEN=80
MCP_MAX_ROWS_CAP=500
MCP_QUERY_TIMEOUT_MS=30000
MCP_POOL_MIN=1
MCP_POOL_MAX=4
# (Recommended) Move DB creds here instead of hard-coding:
# DEV_USER=...
# DEV_PASSWORD=...
# DEV_DSN=...
```
### 3. Environment variables reference
| Variable | Default | Purpose |
|---|---|---|
| `MCP_DEFAULT_ENV` | `DEV` | Environment selected at startup (`DEV` or `Another`). |
| `MCP_LOG_LEVEL` | `INFO` | Python logging level. |
| `MCP_MAX_VAL_LEN` | `80` | Max characters per cell in output (truncated with `…`). |
| `MCP_MAX_ROWS_CAP` | `500` | Hard server-side row cap for any query. |
| `MCP_QUERY_TIMEOUT_MS` | `30000` | Query timeout in ms. |
| `MCP_POOL_MIN` | `1` | Min connections in pool. |
| `MCP_POOL_MAX` | `4` | Max connections in pool. |
---
## ▶️ Running
The server communicates over **stdio** (standard for MCP).
```bash
python server_v2.py
```
You should see startup logs such as `Oracle client initialised (thick mode): ...`. The process then waits for MCP messages on stdin/stdout.
---
## 🔌 Wiring into an MCP client
Example `mcp.json` entry (Claude Desktop / VS Code MCP):
```json
{
"mcpServers": {
"oracle-db": {
"command": "python",
"args": ["C:\\path\\to\\server_v2.py"],
"env": {
"MCP_DEFAULT_ENV": "DEV"
}
}
}
}
```
---
## 🧰 Tools exposed
All tools return **compact tab-separated text** to keep token usage low.
| Tool | Description |
|---|---|
| `get_current_environment()` | Show the currently active DB environment. |
| `switch_environment(env)` | Switch between `DEV` and `Another`. |
| `list_tables(search="", owner="")` | List tables, optional name filter / owner. **Cached.** |
| `describe_table(table_name, owner="")` | Show columns, PK, FKs of a table. **Cached.** |
| `get_distinct_values(table_name, column_name, top_n=20, owner="")` | Distinct values + counts for a column. |
| `run_query(sql, max_rows=20)` | Execute a read-only `SELECT` / `WITH` query. |
| `cache_stats()` | Diagnostic: cache hit/miss counters. |
### Safety rules for `run_query`
- Must start with `SELECT` or `WITH`.
- Blocks: `INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, TRUNCATE, MERGE, GRANT, REVOKE, EXEC, EXECUTE, CALL, BEGIN, DECLARE, COMMIT, ROLLBACK, SAVEPOINT`.
- `max_rows` is clamped to `MCP_MAX_ROWS_CAP`.
- Enforces `MCP_QUERY_TIMEOUT_MS`.
---
## 📖 Usage examples
Below are practical examples of how to interact with the server. Prompts are what you would type in your MCP client (e.g. Claude Desktop, VS Code); the *Tool call* rows show what the client actually sends.
### 1. Check the active environment
**Prompt:**
> Which Oracle environment am I connected to right now?
**Tool call:**
```json
{ "tool": "get_current_environment", "arguments": {} }
```
---
### 2. Switch environments
**Prompt:**
> Switch to DEV.
**Tool call:**
```json
{ "tool": "switch_environment", "arguments": { "env": "DEV" } }
```
---
### 3. Discover tables
**Prompt:**
> List all tables that contain "CUSTOMER" in the name.
**Tool call:**
```json
{ "tool": "list_tables", "arguments": { "search": "CUSTOMER" } }
```
> 💡 Repeat calls with the same args are served from cache — call `cache_stats` to verify.
---
### 4. Describe a table
**Prompt:**
> Describe the CUSTOMER table.
**Tool call:**
```json
{ "tool": "describe_table", "arguments": { "table_name": "CUSTOMER" } }
```
---
### 5. Explore column values
**Prompt:**
> What are the top 5 STATUS values in the CUSTOMER table and how often do they appear?
**Tool call:**
```json
{
"tool": "get_distinct_values",
"arguments": {
"table_name": "CUSTOMER",
"column_name": "STATUS",
"top_n": 5
}
}
```
---
### 6. Run a read-only query
**Prompt:**
> How many customers signed up per month in 2026 so far? Give me the top 10.
**Tool call:**
```json
{
"tool": "run_query",
"arguments": {
"sql": "SELECT TO_CHAR(CREATED_AT,'YYYY-MM') AS MONTH, COUNT(*) AS SIGNUPS FROM CUSTOMER WHERE CREATED_AT >= DATE '2026-01-01' GROUP BY TO_CHAR(CREATED_AT,'YYYY-MM') ORDER BY MONTH",
"max_rows": 10
}
}
```
---
### 7. Query with a CTE (`WITH` is allowed)
**Prompt:**
> Show me the 5 customers with the most orders.
**Tool call:**
```json
{
"tool": "run_query",
"arguments": {
"sql": "WITH order_counts AS (SELECT CUSTOMER_ID, COUNT(*) AS N FROM ORDERS GROUP BY CUSTOMER_ID) SELECT c.CUSTOMER_ID, c.FIRST_NAME, c.LAST_NAME, oc.N AS ORDER_COUNT FROM order_counts oc JOIN CUSTOMER c ON c.CUSTOMER_ID = oc.CUSTOMER_ID ORDER BY oc.N DESC FETCH FIRST 5 ROWS ONLY",
"max_rows": 5
}
}
```
---
### 8. What happens when a write query is attempted
**Prompt:**
> Delete inactive customers.
**Tool call:**
```json
{
"tool": "run_query",
"arguments": { "sql": "DELETE FROM CUSTOMER WHERE STATUS = 'INACTIVE'" }
}
```
**Sample output:**
```
ERROR: Only read-only SELECT / WITH statements are allowed.
Blocked keyword detected: DELETE
```
---
### 9. Inspect cache effectiveness
**Prompt:**
> How well is the metadata cache performing?
**Tool call:**
```json
{ "tool": "cache_stats", "arguments": {} }
```
**Sample output:**
```
list_tables → hits=14 misses=3 size=3/128
describe_table → hits=42 misses=8 size=8/256
```
> High `hits` vs `misses` = big savings in Oracle round-trips *and* MCP tokens/credits.
---
### 10. Full conversational example
A realistic multi-turn flow inside an MCP client:
> **You:** I'm on DEV — which tables have "ORDER" in the name?
> **Assistant** *(calls `list_tables(search="ORDER")`)* → returns 4 tables.
>
> **You:** Describe `ORDERS`.
> **Assistant** *(calls `describe_table(table_name="ORDERS")`)* → returns columns + PK/FK.
>
> **You:** What are the possible values of `ORDER_STATUS`?
> **Assistant** *(calls `get_distinct_values(table_name="ORDERS", column_name="ORDER_STATUS")`)*.
>
> **You:** Show me the last 20 SHIPPED orders from this month.
> **Assistant** *(calls `run_query` with a `SELECT ... WHERE ORDER_STATUS='SHIPPED' AND ORDER_DATE >= TRUNC(SYSDATE,'MM') ORDER BY ORDER_DATE DESC FETCH FIRST 20 ROWS ONLY`)*.
Because `list_tables` and `describe_table` are cached, follow-up questions about the same schema cost almost nothing on subsequent turns.
---
## 💡 Credit / token optimisation
This server is tuned to minimise AI-side credit usage:
- **Cached metadata** — repeated `list_tables` / `describe_table` calls don't re-hit Oracle *and* return quickly to the model.
- **Tab-separated, truncated cells** — dense output = fewer tokens than JSON.
- **Hard row cap** — prevents accidental huge result sets.
- **Compact env / describe output** — no verbose prose.
To inspect effectiveness during a session, call `cache_stats`.
To reset caches (e.g. after schema changes), restart the server or call the internal `_clear_metadata_cache()` (not exposed as a tool by default).
---
## 🧪 Local testing
Quick sanity check without an MCP client:
```bash
python -c "import oracledb; print(oracledb.version)"
python server_v2.py # then send MCP JSON-RPC messages over stdin
```
For end-to-end testing, register the server in your MCP client's `mcp.json` and call tools from the client UI.
---
## 🗂️ Project layout
```
.
├── server_v2.py # MCP server (this project)
└── README.md
```
---
