# MCP-Oracle

[Español](README.es.md) | **English**

[MCP](https://modelcontextprotocol.io) (Model Context Protocol) server that connects an AI agent to an Oracle database.

It exposes tools to explore the schema, read PL/SQL source, and run SQL. It starts in **read-only** mode; writes and stored-procedure calls are enabled with `ORACLE_ALLOW_WRITE=true`.

It uses the official Python SDK (`mcp`) and [python-oracledb](https://python-oracledb.readthedocs.io/). If it finds an Instant Client (or the `lib/` directory of an `ORACLE_HOME`), it enables **thick** mode; otherwise it uses **thin** mode. Thick mode is required for older instances (10g/11g) whose password verifier is not supported in thin mode.

This project is **not** the official Oracle MCP suite ([oracle/mcp](https://github.com/oracle/mcp)). That repository targets Oracle Cloud and other Oracle products. This server talks directly to **your** database listener.

## Tools

| Tool | Description | Write |
| --- | --- | --- |
| `list_tables` | Lists visible tables. Optional `schema`. | No |
| `describe_table` | Columns, types, nullability, comments, and PK. | No |
| `list_constraints` | PK, FK, and unique constraints for a table. | No |
| `get_object_source` | Source of a PACKAGE, PROCEDURE, FUNCTION, TRIGGER, or VIEW. | No |
| `run_query` | Runs `SELECT` / `WITH`. Rejects DML/DDL. Truncates to `ORACLE_MAX_ROWS`. | No |
| `execute_dml` | `INSERT` / `UPDATE` / `DELETE` / `MERGE` + commit. | Yes |
| `call_procedure` | Calls a stored PL/SQL procedure + commit. | Yes |

`run_query` and `execute_dml` accept bind variables (`:name`) so you do not concatenate values into SQL.

## Requirements

- Python 3.9+
- Access to an Oracle instance (user, password, host, port, and `service_name`)
- For Oracle 10g/11g (or when thin mode fails): [Oracle Instant Client](https://www.oracle.com/database/technologies/instant-client.html) or the `lib/` directory of an `ORACLE_HOME`

## Local use vs production

This README covers **local use**: Cursor or Claude Desktop start `server.py` on your machine over stdio (it does not open an HTTP port).

If the MCP should keep running on a server and clients connect over the network:

- [Linux (RedHat)](docs/DEPLOY_LINUX.md)
- [Windows Server](docs/DEPLOY_WINDOWS.md)

## Installation

```bash
python3 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt
```

Copy the environment template and fill in your values:

```bash
cp .env.example .env
```

The server **does not** load `.env` **automatically**. Those variables must be passed in the MCP client configuration (Cursor, Claude Desktop, and so on). The `.env` file is a local reference; do not commit it.

## Environment variables

| Variable | Required | Default | Description |
| --- | --- | --- | --- |
| `ORACLE_USER` | Yes | — | Database user |
| `ORACLE_PASSWORD` | Yes | — | Password |
| `ORACLE_HOST` | If `ORACLE_DSN` is unset | `127.0.0.1` or `localhost` | Listener host |
| `ORACLE_PORT` | If `ORACLE_DSN` is unset | `1521` | Listener port |
| `ORACLE_SERVICE_NAME` | If `ORACLE_DSN` is unset | — | Service name (e.g. `ORCLPDB1`) |
| `ORACLE_DSN` | No | — | If set, replaces host/port/service_name. Useful for a TNS alias or Easy Connect Plus |
| `ORACLE_ALLOW_WRITE` | No | `false` | `true` enables `execute_dml` and `call_procedure` |
| `ORACLE_MAX_ROWS` | No | `200` | Maximum rows returned by `run_query` |
| `ORACLE_CLIENT_LIB_DIR` | No | `~/oracle/instantclient` | Instant Client folder or `lib/` of an `ORACLE_HOME`. If the directory exists, thick mode is enabled |

## Thick vs thin mode

On startup the server checks `ORACLE_CLIENT_LIB_DIR` (or `~/oracle/instantclient`). If that folder exists, it calls `oracledb.init_oracle_client()` and uses thick mode.

Examples:

```bash
# Instant Client (zip / RPM)
ORACLE_CLIENT_LIB_DIR=/Users/your_user/oracle/instantclient

# ORACLE_HOME 19c
ORACLE_CLIENT_LIB_DIR=/apps/oracle/product/19c/lib
```

If there is no native client, it runs in thin mode (Oracle 12.1+ in most cases).

## Using with Cursor

In Cursor, add the server to the MCP configuration (for example `~/.cursor/mcp.json` or the project configuration):

```json
{
  "mcpServers": {
    "oracle-mcp": {
      "command": "/Users/your_user/Development/oracle-mcp/.venv/bin/python",
      "args": ["/Users/your_user/Development/oracle-mcp/server.py"],
      "env": {
        "ORACLE_USER": "my_user",
        "ORACLE_PASSWORD": "my_password",
        "ORACLE_HOST": "127.0.0.1",
        "ORACLE_PORT": "1521",
        "ORACLE_SERVICE_NAME": "ORCLPDB1",
        "ORACLE_ALLOW_WRITE": "false",
        "ORACLE_MAX_ROWS": "200"
      }
    }
  }
}
```

Use the absolute path to the `.venv` Python so the client finds `mcp` and `oracledb`.

Restart Cursor (or reload MCP servers) and confirm that `oracle-mcp` appears in the tools list.

## Using with Claude Desktop

In `claude_desktop_config.json`:

```json
{
  "mcpServers": {
    "oracle-mcp": {
      "command": "/Users/your_user/Development/oracle-mcp/.venv/bin/python",
      "args": ["/Users/your_user/Development/oracle-mcp/server.py"],
      "env": {
        "ORACLE_USER": "my_user",
        "ORACLE_PASSWORD": "my_password",
        "ORACLE_HOST": "127.0.0.1",
        "ORACLE_PORT": "1521",
        "ORACLE_SERVICE_NAME": "ORCLPDB1",
        "ORACLE_ALLOW_WRITE": "false",
        "ORACLE_MAX_ROWS": "200"
      }
    }
  }
}
```

## Running by hand

Locally the server speaks MCP over **stdio** (it does not open an HTTP port). On a production server the transport is HTTP; see [Local use vs production](#local-use-vs-production).

```bash
source .venv/bin/activate
export ORACLE_USER=my_user
export ORACLE_PASSWORD=my_password
export ORACLE_HOST=127.0.0.1
export ORACLE_PORT=1521
export ORACLE_SERVICE_NAME=ORCLPDB1
python server.py
```

Without an MCP client attached to stdin/stdout you will not see an interactive prompt; that is expected.

## Security

- By default `ORACLE_ALLOW_WRITE=false`: `execute_dml` and `call_procedure` fail with `PermissionError`.
- `run_query` rejects statements that start with `INSERT`, `UPDATE`, `DELETE`, `MERGE`, `DROP`, `ALTER`, `TRUNCATE`, `CREATE`, `GRANT`, or `REVOKE`.
- `execute_dml` only accepts `INSERT`, `UPDATE`, `DELETE`, or `MERGE`.
- Prefer bind variables (`:id`) instead of interpolating values into SQL.
- Connect with a least-privilege user when you only need to explore the schema.
- Do not commit `.env` or credentials in a versioned `mcp.json`.

## Dependencies

- [mcp](https://pypi.org/project/mcp/) ≥ 1.2.0
- [oracledb](https://pypi.org/project/oracledb/) ≥ 2.4.0