# Oracle DB MCP

Read-only MCP server that lets coding AI agents inspect an Oracle Database schema through live metadata.

It connects with `node-oracledb` Thin mode, usually through an Oracle wallet, and exposes schema, source, DDL, synonym, sequence, grant, and optional ORDS metadata as MCP tools.

This server does **not** expose arbitrary SQL execution.

## What This Gives Your Agent

After connecting this MCP server, an AI coding agent can answer questions like:

- What columns, constraints, and indexes does this table have?
- Which tables reference this table?
- Where is this table, column, package, or procedure used?
- What is the DDL for this view or object?
- Which triggers run on this table?
- Which synonyms point to this object?
- Which grants exist for this table?
- Which ORDS handler serves this route?

The agent discovers this information live from Oracle dictionary views instead of relying on stale exported schema files.

## Requirements

- Node.js 18+
- Access to an Oracle Database user
- Oracle wallet files for Thin mode:
  - `tnsnames.ora`
  - `ewallet.pem`
- A dedicated read-only Oracle user is strongly recommended

## Install And Build

Clone the repo, install dependencies, and build:

```bash
git clone https://github.com/your-org/oracle-db-mcp.git
cd oracle-db-mcp
npm install
npm run build
```

Extract your Oracle wallet outside the repo, for example:

```text
C:\secure\oracle-wallets\example
```

or:

```text
/Users/me/secure/oracle-wallets/example
```

Copy `.env.example` to `.env` and fill in your connection settings:

```env
ORACLE_USER=ORACLE_MCP_READONLY
ORACLE_PASSWORD=replace-with-db-password
ORACLE_CONNECT_STRING=your_tns_alias_low
ORACLE_CONFIG_DIR=C:\secure\oracle-wallets\example
ORACLE_WALLET_LOCATION=C:\secure\oracle-wallets\example
ORACLE_WALLET_PASSWORD=
ORACLE_DEFAULT_SCHEMA=APP_SCHEMA
ORACLE_POOL_MIN=1
ORACLE_POOL_MAX=4
ORACLE_POOL_INCREMENT=1
ORACLE_CONNECT_TIMEOUT_SECONDS=20
ORACLE_TRANSPORT_CONNECT_TIMEOUT_MS=10000
ORACLE_QUEUE_TIMEOUT_MS=60000
```

`ORACLE_CONNECT_STRING` should be a TNS alias from `tnsnames.ora`. Leave `ORACLE_WALLET_PASSWORD` blank if your wallet does not have a password.

Verify the connection:

```bash
npm run check:oracle
```

## Connect An AI Agent

Most MCP-compatible coding agents support a stdio server config with `command`, `args`, and optionally `env`.

Use the built server entrypoint:

```text
node /absolute/path/to/oracle-db-mcp/dist/index.js
```

### Option A: Use `.env`

If you created `.env` in the repo root, your MCP client config only needs to start the server.

Windows example:

```json
{
  "mcpServers": {
    "oracle-db": {
      "command": "node",
      "args": [
        "C:\\path\\to\\oracle-db-mcp\\dist\\index.js"
      ]
    }
  }
}
```

macOS/Linux example:

```json
{
  "mcpServers": {
    "oracle-db": {
      "command": "node",
      "args": [
        "/path/to/oracle-db-mcp/dist/index.js"
      ]
    }
  }
}
```

### Option B: Put Env Vars In The MCP Config

Some teams prefer keeping the repo without a `.env` file and passing secrets through the MCP client environment.

```json
{
  "mcpServers": {
    "oracle-db": {
      "command": "node",
      "args": [
        "/path/to/oracle-db-mcp/dist/index.js"
      ],
      "env": {
        "ORACLE_USER": "ORACLE_MCP_READONLY",
        "ORACLE_PASSWORD": "replace-with-db-password",
        "ORACLE_CONNECT_STRING": "your_tns_alias_low",
        "ORACLE_CONFIG_DIR": "/secure/oracle-wallets/example",
        "ORACLE_WALLET_LOCATION": "/secure/oracle-wallets/example",
        "ORACLE_WALLET_PASSWORD": "",
        "ORACLE_DEFAULT_SCHEMA": "APP_SCHEMA"
      }
    }
  }
}
```

The exact file where this JSON goes depends on your AI client. Look for a setting named MCP servers, tools, connectors, or external tools, then add a stdio MCP server using the same shape.

## Verify From Your Agent

Restart your AI client after adding the MCP config, then ask:

```text
Use the oracle-db MCP server to ping Oracle.
```

Then try:

```text
Use oracle-db to list tables in the default schema.
```

or:

```text
Use oracle-db to describe the CUSTOMERS table and show its constraints.
```

If the server is connected correctly, your agent should call tools such as `ping_oracle`, `list_tables`, and `describe_table`.

## Available Tools

Connection and schema:

- `ping_oracle`: verifies the wallet connection with `SELECT ... FROM DUAL`.
- `list_tables`: lists table metadata from `ALL_TABLES`.
- `describe_table`: lists column metadata from `ALL_TAB_COLUMNS`.
- `get_table_summary`: returns table metadata and counts.
- `get_table_constraints`: returns primary key, foreign key, unique, and check constraints.
- `get_table_indexes`: returns indexes and indexed columns.
- `get_table_comments`: returns table and column comments.
- `get_table_relationships`: returns declared FK links where a table is a child or parent.
- `get_schema_relationships`: returns declared FK links touching a schema.

Objects, source, and DDL:

- `search_objects`: searches `ALL_OBJECTS` by name, type, status, and owner.
- `get_object_metadata`: returns metadata for one object from `ALL_OBJECTS`.
- `search_source`: searches `ALL_SOURCE` with capped context snippets.
- `get_object_source`: returns full ordered source lines for a PL/SQL object.
- `get_table_triggers`: returns triggers on a table with source previews.
- `find_object_dependencies`: returns objects referenced by a database object.
- `find_object_dependents`: returns objects that reference a database object.
- `list_views`: lists view metadata from `ALL_VIEWS`.
- `get_view_definition`: returns view metadata plus DDL from `DBMS_METADATA.GET_DDL`.
- `get_object_ddl`: returns object DDL from `DBMS_METADATA.GET_DDL`.

Synonyms, sequences, and grants:

- `list_synonyms`: lists synonyms from `ALL_SYNONYMS`.
- `resolve_synonym`: resolves one synonym, preferring the configured schema before `PUBLIC`.
- `list_sequences`: lists sequence metadata from `ALL_SEQUENCES`.
- `get_sequence`: returns one sequence from `ALL_SEQUENCES`.
- `get_object_grants`: returns grants on one object from `ALL_TAB_PRIVS`.
- `get_grants_to_user`: returns grants where `GRANTEE` matches a user or role.

ORDS:

- `list_ords_modules`: lists current-schema ORDS modules.
- `get_ords_module`: returns one ORDS module with templates, handlers, and parameters.
- `search_ords_handlers`: searches ORDS handlers by module, URI, method, source type, or source text.
- `get_ords_handler`: returns one ORDS handler with parameters and full source.

ORDS tools auto-detect whether `USER_ORDS_*` metadata views are available. If not, they return `available: false` with a clear reason instead of failing the MCP server.

Ad hoc queries:

- `execute_sql`: runs a single read-only `SELECT` (or `WITH ... SELECT`) statement and returns rows.

## Security Notes

- Use a dedicated read-only Oracle user.
- Keep `.env` and wallet files out of git.
- Grant only the dictionary/object privileges needed for your agent’s use case.
- DDL extraction depends on Oracle privileges. If `DBMS_METADATA.GET_DDL` cannot read an object, the tool returns a structured `error` object.
- `execute_sql` is guardrailed but not a substitute for DB-level privileges: it rejects multi-statement input, non-`SELECT`/`WITH` statements, DML/DDL/transaction-control keywords, and `SELECT ... FOR UPDATE`; it then runs the query under `SET TRANSACTION READ ONLY` and caps results at 200 rows by default (hard cap 1000). Note that `SET TRANSACTION READ ONLY` only backstops DML (`INSERT`/`UPDATE`/`DELETE`/`MERGE`) — Oracle DDL statements auto-commit and run outside transaction scope, so DDL protection relies entirely on the keyword/statement-shape validation. A PL/SQL function called from within a `SELECT` could still perform writes via an autonomous transaction. Because of this, granting the underlying DB user genuinely read-only privileges (no DDL/DML/EXECUTE on write-capable procedures) is required, not optional — the tool is defense in depth, not a privilege boundary.

## Troubleshooting

Run the smoke test first:

```bash
npm run check:oracle
```

Common issues:

- Missing `tnsnames.ora` or `ewallet.pem`: check `ORACLE_CONFIG_DIR` and `ORACLE_WALLET_LOCATION`.
- TNS alias not found: verify `ORACLE_CONNECT_STRING` exists in `tnsnames.ora`.
- Agent cannot see tools: restart the AI client after changing MCP config.
- DDL returns an error: the Oracle user may not have permission to read that object through `DBMS_METADATA`.
- ORDS tools return `available: false`: the database/user does not expose the `USER_ORDS_*` metadata views.