# oracle-mcp

A **read-only** Oracle Database server for the [Model Context Protocol](https://modelcontextprotocol.io).
It lets AI agents (Claude Desktop, Claude Code, Cursor, VS Code agents, OpenAI Agents, …) **safely
inspect** large legacy Oracle schemas — thousands of tables, hundreds of packages, views, synonyms,
triggers, sequences and PL/SQL source — **without ever modifying data**.

It is designed as a standalone module that runs alongside an existing "Engineering MCP" (GitLab /
Redmine / Taiga / ERPNext): one agent, several MCP servers.

> **Safety model in one line:** the server only ever issues `SELECT` and data-dictionary reads, every
> object name is passed as a **bind variable**, free-form SQL is checked by a fail-closed
> [read-only guard](#security-considerations), and the database account itself should be granted
> read-only. Defence in depth, not a single gate.

---

## Table of contents
- [Features](#features)
- [Requirements](#requirements)
- [Installation](#installation)
- [Configuration](#configuration)
- [Wiring it into an agent](#wiring-it-into-an-agent)
- [Architecture](#architecture)
- [Tool reference](#tool-reference)
- [Security considerations](#security-considerations)
- [Examples](#examples)
- [Testing](#testing)
- [Troubleshooting](#troubleshooting)

---

## Features

- **24 focused tools** covering search, describe, DDL, source, dependencies, indexes, constraints,
  triggers, synonyms, statistics, invalid objects and guarded `SELECT` execution.
- **Read-only by construction** — a SQL guard that rejects everything but a single, comment-free
  `SELECT` / `WITH … SELECT`.
- **Bind-variable everywhere** — object names and keywords are never concatenated into SQL.
- **Bounded & safe** — hard row cap (default 1000), per-statement timeout, ResultSet cleanup.
- **Connection pooling** with transparent reconnect (thick mode / Oracle Instant Client).
- **Structured logging** to stderr (timestamp, tool, elapsed, rows, schema, SQL) — never secrets.
- **Typed error taxonomy** — connection / validation / invalid-SQL / permission / not-found /
  timeout / oracle.
- **Strongly typed** (TypeScript strict) and **tested** (48 unit tests for the guard & helpers).

---

## Requirements

- **Node.js ≥ 18**
- **Oracle Instant Client** installed and on the library path (this build uses oracledb **thick mode**).
  - Windows: the Instant Client folder on `PATH`.
  - Linux/macOS: on `LD_LIBRARY_PATH` / `DYLD_LIBRARY_PATH`, or set `ORACLE_CLIENT_LIB_DIR`.
- Network access to the database and a **read-only Oracle account** (see [Security](#security-considerations)).

---

## Installation

```bash
git clone <your-repo>/oracle-mcp.git
cd oracle-mcp
npm install
npm run build          # compiles src/ → dist/
```

Verify without a database:

```bash
npm test               # 48 unit tests (SQL guard, identifiers, formatting)
```

Smoke-test against a real database (read-only):

```bash
ORACLE_USER=... ORACLE_PASSWORD=... ORACLE_CONNECT_STRING=host:port/service \
  npx tsx scripts/integration-check.ts
```

---

## Configuration

Configuration is via environment variables. The server loads a **`.env` file from its own package
directory** automatically (copy `.env.example` → `.env`), so secrets live next to the server and out
of your agent config. Config is validated at startup; the server fails fast with a readable,
**secret-free** message if anything is missing.

### Databases (one or many)

The server can inspect **several Oracle databases at once**. Every tool takes an optional `database`
argument; when omitted it uses the default.

**Single database:**
```dotenv
ORACLE_USER="readonly_user"
ORACLE_PASSWORD="change_me"
ORACLE_CONNECT_STRING="host:port/service"
```

**Multiple databases** — list the names, then supply per-name vars with the prefix `ORACLE_<NAME>_`
(name upper-cased, non-alphanumerics → `_`):
```dotenv
ORACLE_DATABASES=tcil,sbi_eforex,ybl
ORACLE_DEFAULT_DATABASE=tcil
ORACLE_TCIL_USER="…"        ORACLE_TCIL_PASSWORD="…"        ORACLE_TCIL_CONNECT_STRING="host:port/service"
ORACLE_SBI_EFOREX_USER="…"  ORACLE_SBI_EFOREX_PASSWORD="…"  ORACLE_SBI_EFOREX_CONNECT_STRING="host:port/service"
ORACLE_YBL_USER="…"         ORACLE_YBL_PASSWORD="…"         ORACLE_YBL_CONNECT_STRING="host:port/service"
```
Pools are created **lazily** per database — configuring ten costs nothing until they're queried.
Wrap passwords in double quotes so `$`/`#` are taken literally.

> **Connect string tip:** for a PDB use the **service name** form `host:port/service`. The older
> `host:port:SID` form is not Easy Connect — convert it (`…:port/service`) or use a tnsnames alias.

### Shared settings

| Variable | Default | Description |
|---|--:|---|
| `ORACLE_CLIENT_LIB_DIR` | *(from PATH)* | Instant Client dir. If unset, discovered via PATH/LD_LIBRARY_PATH. |
| `ORACLE_TNS_ADMIN` | — | Dir containing `tnsnames.ora`/`sqlnet.ora`, if used. |
| `ORACLE_MAX_ROWS` | `1000` | Hard cap on rows any tool returns (also the max a caller may request). |
| `ORACLE_QUERY_TIMEOUT_MS` | `15000` | Per-statement timeout (thick-mode `callTimeout`). |
| `ORACLE_POOL_MIN` / `_MAX` / `_INCREMENT` | `1` / `4` / `1` | Connection pool sizing (per database). |
| `ORACLE_POOL_TIMEOUT` | `60` | Idle-connection trim (seconds). |
| `ORACLE_DEFAULT_SCHEMA` | — | Default owner for owner-scoped tools when `schema` is omitted. |
| `LOG_LEVEL` | `info` | `error` \| `warn` \| `info` \| `debug` (logs → **stderr**). |

---

## Wiring it into an agent

`oracle-mcp` speaks MCP over **stdio**. Add it next to your Engineering MCP.

**Claude Desktop / Claude Code** (`claude_desktop_config.json` / `.mcp.json`) — no secrets here; the
server reads its own `.env`:

```jsonc
{
  "mcpServers": {
    "engineering": { "command": "node", "args": ["/path/to/mcp-erpnext/src/index.js"] },
    "oracle": {
      "command": "node",
      "args": ["/path/to/oracle-mcp/dist/index.js"],
      "cwd": "/path/to/oracle-mcp"
    }
  }
}
```

Credentials live in `oracle-mcp/.env` (gitignored), not in the agent config. Keeping Oracle in its
**own** server (rather than merging into the JS Engineering MCP) isolates the security-critical
database surface and lets you grant/deploy it independently.

---

## Architecture

```
                        ┌──────────────────────────────────────────────┐
   AI agent  ──stdio──▶ │  index.ts  (McpServer, StdioServerTransport)  │
   (Claude/Cursor/…)    └───────────────┬──────────────────────────────┘
                                        │ registers 24 tools
                        ┌───────────────▼───────────────┐
                        │  tools/oracle/*                │  runSelect · executionPlan · ddl
                        │  (thin handlers, zod schemas)  │  · 20 declarative metadata tools
                        └───────┬───────────────┬────────┘
              guarded SQL       │               │  built SQL + binds
                    ┌───────────▼──────┐   ┌─────▼─────────────────────┐
                    │ validation/      │   │ oracle/client.ts          │
                    │ sqlGuard.ts      │   │  • timeout (callTimeout)  │
                    │ (fail-closed)    │   │  • row cap + truncation   │
                    └──────────────────┘   │  • ResultSet cleanup      │
                                           │  • error → taxonomy       │
                                           └─────┬─────────────────────┘
                                                 │ pooled connection
                                           ┌─────▼───────────────┐
                                           │ oracle/pool.ts       │  thick init · pool · reconnect
                                           └─────┬───────────────┘
                                                 ▼
                                        Oracle DB  (ALL_* dictionary + DBMS_METADATA/DBMS_XPLAN)

  cross-cutting:  config/env.ts (zod-validated)   logging/logger.ts (stderr, redacted)
                  errors.ts (typed taxonomy)       utils/ (identifiers, formatting)
```

**Folder structure**

```
oracle-mcp/
├── src/
│   ├── index.ts               # server bootstrap + graceful shutdown
│   ├── config/env.ts          # env loading & validation (zod)
│   ├── logging/logger.ts      # structured stderr logger (+ SQL redaction)
│   ├── errors.ts              # OracleMcpError + Oracle→taxonomy mapping
│   ├── types/index.ts         # shared types
│   ├── validation/sqlGuard.ts # read-only SQL guard  ◀── security core
│   ├── utils/
│   │   ├── identifiers.ts      # name validation, LIKE-pattern escaping
│   │   └── format.ts           # Markdown tables / code blocks
│   ├── oracle/
│   │   ├── pool.ts             # thick init, pool lifecycle, reconnect
│   │   └── client.ts           # the single query choke-point
│   └── tools/oracle/
│       ├── context.ts          # tool type + registration wrapper
│       ├── runSelect.ts        # oracle_run_select (guarded)
│       ├── executionPlan.ts    # oracle_show_execution_plan
│       ├── ddl.ts              # oracle_get_object_ddl / oracle_get_view
│       ├── metadataTools.ts    # 20 declarative dictionary tools
│       └── index.ts            # catalogue + registerOracleTools()
├── tests/                     # vitest unit tests
├── scripts/integration-check.ts
└── .env.example
```

**Why these choices**
- **Standalone TS package, not merged into the JS Engineering MCP** — isolates a security-sensitive
  surface, allows a strict-typed build and independent deployment/grants.
- **Thick mode** — chosen for this deployment (Instant Client present); enables the widest driver
  feature set. Thin mode would remove the client dependency if ever desired.
- **Declarative metadata tools** — the 20 dictionary tools share one safe shape (fixed SQL + binds +
  format), so adding a tool is a few lines and the security properties are uniform.
- **One `OracleClient` choke-point** — every query flows through it, so timeout, row cap, cleanup,
  error mapping and logging are enforced in exactly one place.

---

## Tool reference

All tools are prefixed `oracle_`. Owner-scoped tools accept an optional `schema`; search tools accept
an optional `limit` (clamped to `ORACLE_MAX_ROWS`). Names may be given as `OBJECT` or `SCHEMA.OBJECT`.

| Tool | Key params | Purpose |
|---|---|---|
| `oracle_run_select` | `sql`, `maxRows?` | Execute a **guarded** read-only SELECT. |
| `oracle_show_execution_plan` | `sql` | EXPLAIN PLAN + DBMS_XPLAN for a SELECT (no data touched). |
| `oracle_list_schemas` | — | List owners/schemas visible to the account. |
| `oracle_list_tables` | `schema?`, `keyword?`, `limit?` | List tables (optionally filtered). |
| `oracle_search_tables` | `keyword` | Tables whose name contains a keyword. |
| `oracle_find_table` | `table_name` | Locate a table across schemas, **including synonyms**. |
| `oracle_describe_table` | `table_name`, `schema?` | Columns + types + nullability + comments. |
| `oracle_search_columns` | `column_name` | Columns whose name contains a keyword (e.g. `RISK`). |
| `oracle_find_column` | `column_name` | Tables having a column (exact matches first). |
| `oracle_get_indexes` | `table_name` | Indexes with columns, uniqueness, type, status. |
| `oracle_get_constraints` | `table_name` | PK/FK/UK/CHECK with columns, ref table, delete rule. |
| `oracle_find_triggers` | `table_name` | Triggers on a table (timing, event, status). |
| `oracle_get_object_ddl` | `object_name`, `object_type?` | Full CREATE DDL via `DBMS_METADATA`. |
| `oracle_get_view` | `view_name` | View DDL + column list. |
| `oracle_get_package_source` | `package_name` | Package **specification** source. |
| `oracle_get_package_body` | `package_name` | Package **body** source. |
| `oracle_search_package` | `package_name` | Find packages by name keyword. |
| `oracle_search_procedure` | `procedure_name` | Find procedures/functions (standalone & packaged). |
| `oracle_search_source` | `keyword`, `object_type?` | **Full-text search of all PL/SQL source** — references & callers. |
| `oracle_find_dependencies` | `object_name`, `direction?` | `used_by` (callers) or `uses` (referenced). |
| `oracle_list_synonyms` | `schema?`, `keyword?`, `target_table?` | Synonyms; `target_table` → "points to". |
| `oracle_get_table_statistics` | `table_name` | Row count, blocks, avg row len, last analyzed. |
| `oracle_list_invalid_objects` | `schema?` | Objects in `INVALID` state. |
| `oracle_describe_object` | `object_name` | What an object is (type/owner/status) from `ALL_OBJECTS`. |

**How common questions map to tools**

| Question | Tool |
|---|---|
| Where is `MFX_GET_MARGIN` defined? | `oracle_search_procedure` → `oracle_describe_object` |
| Show package body | `oracle_get_package_body` |
| Find all procedures calling `MFX_GET_MARGIN` | `oracle_find_dependencies` (`used_by`) or `oracle_search_source` |
| Every reference to `mfx_transaction` | `oracle_search_source` |
| Describe `mfx_entity_master` | `oracle_describe_table` |
| Columns containing "risk" | `oracle_search_columns` |
| Indexes / FKs / triggers on a table | `oracle_get_indexes` / `oracle_get_constraints` / `oracle_find_triggers` |
| Explain this query | `oracle_show_execution_plan` |
| Synonyms pointing to a table | `oracle_list_synonyms` (`target_table`) |
| Invalid objects | `oracle_list_invalid_objects` |

---

## Security considerations

**Layers (defence in depth):**

1. **Read-only account (primary wall).** Grant the connection user only `CREATE SESSION` + `SELECT`
   on the objects (or roles) it must inspect, plus `SELECT_CATALOG_ROLE` for the dictionary. The MCP
   should be *incapable* of writing regardless of any bug above it.
2. **SQL guard (`validation/sqlGuard.ts`)** for the one free-form tool (`oracle_run_select`) — it
   **fails closed** and rejects:
   - anything that is not a lone `SELECT` / `WITH … SELECT`;
   - `INSERT/UPDATE/DELETE/MERGE/…`, all DDL, `GRANT/REVOKE`, `COMMIT/ROLLBACK`;
   - PL/SQL blocks (`BEGIN`/`DECLARE`), `CALL`, `EXECUTE [IMMEDIATE]`, `SELECT … INTO`, `FOR UPDATE`;
   - dangerous packages (`DBMS_SQL`, `DBMS_SCHEDULER`, `DBMS_JOB`, `UTL_FILE`, `UTL_HTTP`, …);
   - **semicolons / multiple statements**, and **all comments/hints** (a classic bypass vector);
   - it analyses a *code-only* projection with string-literal contents blanked, so keywords or
     semicolons hidden inside literals can neither false-trigger nor smuggle a second statement.
3. **Bind variables** for every object name / keyword in the 23 metadata tools — user input is a
   *value*, never SQL text. Identifiers are additionally validated against a strict character set.
4. **Bounds** — hard row cap (`ORACLE_MAX_ROWS`), per-statement `callTimeout`, ResultSet cleanup.
5. **No secret leakage** — passwords are never logged; logs go to **stderr** only (stdout is the MCP
   channel); SQL is length-capped in logs.

**Notes**
- `oracle_show_execution_plan` runs `EXPLAIN PLAN`, which writes to the session-private global
  temporary `PLAN_TABLE`. That is scratch metadata, auto-discarded, and available even to read-only
  accounts — **no production data is read or written**.
- The guard is intentionally strict; prefer a dedicated metadata tool over `oracle_run_select` when
  one exists. A rare false positive (e.g. a column literally named after a non-reserved keyword) can
  be worked around with an alias.

---

## Examples

```text
Agent: "Describe mfx_entity_master."
 → oracle_describe_table { table_name: "MFX_ENTITY_MASTER" }

Agent: "Find every procedure that references mfx_transaction."
 → oracle_search_source { keyword: "mfx_transaction", object_type: "PACKAGE BODY" }

Agent: "Show the body of MFX_GET_MARGIN."
 → oracle_get_package_body { package_name: "MFX_GET_MARGIN" }

Agent: "What foreign keys does mfx_transaction have?"
 → oracle_get_constraints { table_name: "MFX_TRANSACTION" }

Agent: "Explain: SELECT * FROM mfx_transaction WHERE trans_date > SYSDATE - 7"
 → oracle_show_execution_plan { sql: "SELECT * FROM mfx_transaction WHERE trans_date > SYSDATE - 7" }
```

---

## Testing

```bash
npm test            # unit: SQL guard (accept/reject matrix), identifiers, LIKE escaping
npm run typecheck   # tsc --noEmit
npx tsx scripts/integration-check.ts   # live smoke test (needs a DB; read-only)
```

The unit tests deliberately concentrate on the **security guard** — the accept set (SELECT/CTE,
literals containing forbidden words, escaped quotes, near-keyword identifiers) and the reject set
(DML/DDL, semicolons, comments/hints, PL/SQL, dangerous packages, `q'…'`, oversize, non-string).

---

## Troubleshooting

| Symptom | Cause / fix |
|---|---|
| `DPI-1047: Cannot locate a 64-bit Oracle Client library` | Instant Client not found. Install it and put it on `PATH`/`LD_LIBRARY_PATH`, or set `ORACLE_CLIENT_LIB_DIR`. |
| `ORA-12154 / ORA-12541 / ORA-12514` | Bad connect string / no listener / unknown service. Use `host:port/service` (service name, not SID) or a valid tnsnames alias. |
| `ORA-01017: invalid username/password` | Wrong `ORACLE_USER`/`ORACLE_PASSWORD`. |
| `[PERMISSION_DENIED] ORA-01031` or empty dictionary results | The account lacks `SELECT` on the object or `SELECT_CATALOG_ROLE`. Grant read access. |
| `[VALIDATION_FAILURE] Only SELECT … permitted` | The SQL isn't a lone SELECT (or contains a semicolon/comment). Send one clean SELECT. |
| Tool returns rows for several schemas | The object name exists in multiple visible schemas. Pass `schema` (or set `ORACLE_DEFAULT_SCHEMA`) to scope. |
| Agent sees no output but stderr has logs | Correct — logs go to stderr by design; stdout carries the MCP protocol only. |
| Server exits immediately on start | Read the stderr line — config validation prints exactly which env var is wrong (no secrets). |

---

## License

MIT.