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

---

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

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:

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

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

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.