oracle-mcp

A read-only Oracle Database server for the Model Context Protocol. 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, and the database account itself should be granted read-only. Defence in depth, not a single gate.

Table of contents

• Features

• Requirements

• Installation

• Configuration

• Wiring it into an agent

• Architecture

• Tool reference

• Security considerations

• Examples

• Testing

• Troubleshooting

Related MCP server: safe-sql-mcp

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).

Installation

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

Verify without a database:

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

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

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:

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 → _):

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

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:

{
  "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.

How common questions map to tools

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

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

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

License

MIT.