# Oracle Schema Explorer MCP Server

[![License: MIT](https://img.shields.io/badge/License-MIT-blue.svg)](https://opensource.org/licenses/MIT)
[![Python 3.10+](https://img.shields.io/badge/python-3.10+-blue.svg)](https://www.python.org/downloads/)
[![MCP](https://img.shields.io/badge/MCP-Compatible-green.svg)](https://modelcontextprotocol.io/)

A universal, strictly **read-only** Model Context Protocol (MCP) server for inspecting and exploring Oracle database schemas, tables, columns, and foreign-key relationships across **any Oracle Database version** (Oracle 10g, 11g, 12c, 18c, 19c, 21c, and 23ai).

Compatible with **any AI agent or IDE**: Claude Desktop, Cursor, Google Antigravity, VS Code (Cline / Roo Code), Windsurf, and custom LLM agent frameworks.

---

## Key Features

- **Universal Oracle Compatibility**:
  - **Thin Mode** for Oracle Database 12.1 through 23ai (pure Python, zero C-libraries/Instant Client required).
  - **Automatic Fallback** to local SQL*Plus execution for legacy Oracle instances (Oracle 10g, 11g) or environments with older Oracle Client libraries (e.g. 12.1), completely eliminating ODPI-C version mismatch errors (`DPY-3010`, `DPI-1050`).
  - Standard dictionary views (`ALL_TABLES`, `ALL_TAB_COLUMNS`, `ALL_CONSTRAINTS`, `ALL_CONS_COLUMNS`) and universal `ROWNUM` query wrapping.
- **Strictly Read-Only Guarantee**:
  - Enforces safety at the protocol layer, allowing only `SELECT` and `WITH` statements.
  - Automatically rejects data mutation (`INSERT`, `UPDATE`, `DELETE`, `MERGE`) and schema modifications (`DROP`, `ALTER`, `TRUNCATE`, `CREATE`).
- **Deep Relationship Discovery**:
  - Explores **incoming** and **outgoing** foreign keys.
  - Generates full schema Entity-Relationship diagrams formatted in **Mermaid.js**.
- **Zero Hardcoded Credentials**:
  - Works with a blank default configuration. Connection strings can be passed dynamically in chat or configured via environment variables.

---

## Directory Structure

```text
oracle-mcp-server/
├── server.py                 # Core FastMCP / MCPServer implementation
├── run.bat                   # Universal Windows batch launcher
├── run.ps1                   # Universal PowerShell launcher
├── requirements.txt          # Python package dependencies
├── pyproject.toml            # Python package specifications
├── mcp-config-examples.json  # Configuration snippets for all clients
├── LICENSE                   # MIT License
└── README.md                 # Documentation
```

---

## Installation & Prerequisites

1. **Python 3.10+**: Ensure Python is installed.
2. Install dependencies:
   ```bash
   pip install -r requirements.txt
   ```
   *(Or using `uv`: `uv pip install -r requirements.txt`)*

---

## Configuration

The server ships 100% credential-free. You can supply connection strings in two ways:

### Method 1: Dynamic Session Connection (Recommended)
Keep `ORACLE_CONNECTION_STRING` empty. In any AI chat session, simply instruct your agent:
> *"Connect to Oracle DB: `username/password@hostname:1521/service_name`"*

The server's `set_connection` tool validates and activates the session on the fly.

### Method 2: Pre-configured Environment Variable
Set the `ORACLE_CONNECTION_STRING` environment variable in your MCP client's configuration file.

#### Supported Connection Formats:
- **EzConnect**: `username/password@host:port/service_name`
- **URL Syntax**: `oracle://username:password@host:port/service_name`
- **TNS Descriptor**: `user/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=...)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=...)))`

---

## Client Integration Examples

### Claude Desktop
Add to `%APPDATA%\Claude\claude_desktop_config.json` (Windows) or `~/Library/Application Support/Claude/claude_desktop_config.json` (macOS):
```json
{
  "mcpServers": {
    "oracle-schema-explorer": {
      "command": "C:\\path\\to\\oracle-mcp-server\\run.bat",
      "env": {
        "ORACLE_CONNECTION_STRING": ""
      }
    }
  }
}
```

### Cursor
1. Go to **Settings** -> **Features** -> **MCP Servers**.
2. Click **Add New MCP Server**:
   - **Name**: `oracle-schema-explorer`
   - **Type**: `command`
   - **Command**: `C:\path\to\oracle-mcp-server\run.bat`

### VS Code (Cline / Roo Code / Windsurf)
Add to your client's MCP configuration settings:
```json
{
  "mcpServers": {
    "oracle-schema-explorer": {
      "command": "cmd.exe",
      "args": ["/c", "C:\\path\\to\\oracle-mcp-server\\run.bat"]
    }
  }
}
```

### Google Antigravity
Add to `~/.gemini/config/mcp_config.json`:
```json
{
  "mcpServers": {
    "oracle-schema-explorer": {
      "command": "cmd.exe",
      "args": ["/c", "C:/path/to/oracle-mcp-server/run.bat"],
      "env": {
        "ORACLE_CONNECTION_STRING": ""
      }
    }
  }
}
```

---

## Available MCP Tools

| Tool | Parameters | Description |
| :--- | :--- | :--- |
| `set_connection` | `connection_string: str` | Connects or switches database dynamically. Tests connection and returns database banner. |
| `get_connection_info` | None | Returns connection status, current schema/user, database name, and version banner. |
| `list_schemas` | None | Lists all accessible schemas and their table counts (filters out internal Oracle system accounts). |
| `list_tables` | `schema: str = ""` | Lists all tables and views in the schema with approximate row counts. |
| `describe_table` | `table_name: str, schema: str = ""` | Returns detailed column definitions: data types, precision, scale, nullability, primary key indicators, and comments. |
| `get_table_connections` | `table_name: str, schema: str = ""` | Inspects table topology: lists parent tables referenced (**outgoing FKs**) and child tables that reference this table (**incoming FKs**). |
| `get_schema_relationships` | `schema: str = ""` | Generates full Entity-Relationship graph for the schema with a renderable **Mermaid ER diagram**. |
| `execute_readonly_query` | `sql_query: str, max_rows: int = 50` | Executes read-only queries (`SELECT`/`WITH`) safely capped by `ROWNUM`. Blocks mutation operations. |

---

## Security & Safety

- **Read-Only Enforcement**: Query sanitization inspects all queries before execution, blocking all DDL and DML operations.
- **Resource Protection**: Result sets are constrained with automatic pagination (`ROWNUM <= max_rows`, capped at 500) to protect database memory and client context windows.
- **System Schema Filtering**: Internal accounts (`SYS`, `SYSTEM`, `DBSNMP`, `MDSYS`, `CTXSYS`, etc.) are hidden by default to keep context clean and focused on application data.

---

## License

This project is licensed under the [MIT License](LICENSE).