Oracle Read Only Server

Local Python MCP server for Oracle database access using python-oracledb in thin mode only.

This server is designed to:

• run locally on demand through an MCP client
• support multiple named Oracle environments such as DEV1 and UAT3
• connect to Oracle without OCI / Oracle Instant Client
• support read-only SQL queries and schema inspection
• support both tnsnames.ora alias mode and direct host/port/service-name mode

Quick Start

1. Copy oracle-mcp.example.jsonc to oracle-mcp.jsonc and fill in your real environment entries such as DEV1 or UAT3.
2. Start the server with make run-server or your MCP client.
3. In the LLM workflow:
   • call list_environments()
   • call check_database("DEV1")
   • then run queries like run_query("DEV1", "select * from ...")

default_schema is optional. Use it only if your read-only login user is different from the application schema you normally query.

Features

• run_query(environment, sql)
  • environment-specific execution using a moniker parameter
  • execute a single read-only SELECT or WITH ... SELECT query
  • returns columns, rows, row count, and truncation status
• list_environments()
  • list configured environment monikers and connection mode
• check_database(environment)
  • verify whether the selected environment is available
  • performs a real Oracle connection and select 1 from dual
• preview_query(sql)
  • validates SQL with the same read-only guard as run_query
  • returns normalized SQL without executing it
• list_tables(environment, schema?, name_pattern?)
  • list accessible tables
• list_views(environment, schema?, name_pattern?)
  • list accessible views
• search_objects(environment, name_pattern, schema?, object_types?)
  • search tables, views, synonyms, or other allowed object types by SQL LIKE pattern
• describe_table(environment, table_name, schema?)
  • full table/view structure including type, precision/scale, defaults, and comments when available
• list_columns(environment, table_name, schema?)
  • lightweight column listing for quick schema inspection
• get_primary_key(environment, table_name, schema?)
  • list primary key columns and position order
• list_foreign_keys(environment, table_name, schema?)
  • list outbound foreign key relationships and referenced columns
• get_table_sample(environment, table_name, schema?, limit?)
  • return a capped sample of rows from a table or view
• list_schemas(environment)
  • list visible Oracle schemas/users

Security Model

This server rejects non-read-only SQL in application code, but the real security boundary should be the Oracle account itself.

Use a database user that only has read access.

The SQL guard allows only a single statement starting with SELECT or WITH and rejects DML, DDL, PL/SQL, and multi-statement input.

Application-level protection:

• only read-oriented MCP tools are exposed
• run_query() and preview_query() validate SQL before execution
• multi-statement input is rejected
• common write and DDL keywords are rejected outside string literals
• SELECT ... FOR UPDATE is also blocked by the guard

Database-level protection:

• the Oracle user should only have CREATE SESSION and SELECT privileges as needed
• do not use SYSTEM, SYS, or any account with write privileges in normal use
• the Oracle account should not have EXECUTE on dangerous packages such as DBMS_SQL, UTL_FILE, or DBMS_LOB

The MCP server reduces risk, but a read-only Oracle account is what actually guarantees that writes cannot happen.

Requirements

• Python 3.10+
• Oracle database reachable over the network
• No OCI / Oracle Instant Client required

Install

python -m venv .venv
. .venv/bin/activate
pip install -e .[dev]

Configuration

This server now uses a single JSONC configuration file with named environments.

Use:

• oracle-mcp.example.jsonc as the template
• oracle-mcp.jsonc as your real local config file

The real oracle-mcp.jsonc is ignored by git because it contains plain-text credentials.

Default config path:

• oracle-mcp.jsonc in the project root

Override config path with:

• ORACLE_MCP_CONFIG_PATH

Example:

{
  "defaults": {
    "fetch_max_rows": 500,
    "connect_timeout": 15,
    "protocol": "tcp"
  },
  "environments": {
    "DEV1": {
      "mode": "tns",
      "tnsnames_path": "C:\\oracle\\network\\admin\\tnsnames.ora",
      "dsn_alias": "DEV1",
      "username": "readonly_user",
      "password": "plain_text_password",
      "default_schema": "APP" // optional
    },
    "UAT3": {
      "mode": "direct",
      "host": "uat3-db.internal",
      "port": 1521,
      "service_name": "UAT3",
      "username": "readonly_user",
      "password": "plain_text_password"
    }
  }
}

TNS environments still use the file path to tnsnames.ora, and the server uses the file's parent directory as Oracle's config_dir for alias resolution.

Schema resolution order for metadata/sample tools is:

1. explicit schema argument passed to the tool
2. default_schema from the selected environment, if present
3. the Oracle username for that environment

Run

oracle-mcp-server

Or during development:

python -m oracle_mcp_server.server

If your config file is not in the project root:

ORACLE_MCP_CONFIG_PATH=C:\path\to\oracle-mcp.jsonc python -m oracle_mcp_server.server

Or use the included Makefile helpers:

make install
make test
make test-integration
make run-server

Makefile Commands

• make install
  • create .venv
  • install the project and dev dependencies
• make test
  • run the default test suite
  • integration tests remain skipped
• make test-integration
  • run the live Oracle integration tests
  • requires a suitable Oracle test environment
• make run-server
  • start the MCP server from the project virtualenv

OpenCode MCP Configuration

OpenCode uses local MCP server definitions under the mcp key in opencode.json or opencode.jsonc.

Example OpenCode config:

{
  "$schema": "https://opencode.ai/config.json",
  "mcp": {
    "oracle_readonly": {
      "type": "local",
      "command": ["/absolute/path/to/oracle-mcp/.venv/bin/oracle-mcp-server"],
      "cwd": "/absolute/path/to/oracle-mcp",
      "enabled": true,
      "environment": {
        "ORACLE_MCP_CONFIG_PATH": "C:\\path\\to\\oracle-mcp.jsonc"
      }
    }
  }
}

Once configured in OpenCode, the MCP tools are available under the oracle_readonly server namespace.

Typical workflow with the LLM:

1. list_environments()
2. check_database("DEV1")
3. run_query("DEV1", "select * from ...")

Generic MCP Client Configuration

Example for a generic client that launches a local stdio server:

{
  "mcpServers": {
    "oracle-readonly": {
      "command": "/absolute/path/to/.venv/bin/oracle-mcp-server",
      "env": {
        "ORACLE_MCP_CONFIG_PATH": "C:\\path\\to\\oracle-mcp.jsonc"
      }
    }
  }
}

Tests

pytest

Or with make:

make test
make test-integration

Default test runs exclude live Oracle integration tests.

Run the live integration suite only when you have a suitable Oracle environment available:

pytest --run-integration -m integration

Integration tests use these environment variables, with Docker-friendly defaults matching the sample Oracle XE setup:

ORACLE_TEST_HOST=127.0.0.1
ORACLE_TEST_PORT=1521
ORACLE_TEST_SERVICE_NAME=XE
ORACLE_TEST_USERNAME=system
ORACLE_TEST_PASSWORD=oracle
ORACLE_TEST_DEFAULT_SCHEMA=SYS
ORACLE_TEST_DSN_ALIAS=XETEST
ORACLE_TEST_CONNECT_TIMEOUT=15
ORACLE_TEST_PROTOCOL=tcp

This lets you keep the tests in the repo but skip them in normal development or CI unless explicitly requested.

Test coverage currently includes:

• JSONC config validation with named environments
• unknown environment rejection
• SQL guard behavior for read-only validation
• connection kwargs for direct and TNS modes
• tnsnames.ora parent-directory resolution, ensuring the server passes the containing directory as config_dir
• environment-specific DB availability checks
• opt-in live integration tests for direct-mode queries, metadata tools, sampling, health checks, and TNS alias resolution

There is also a live smoke-tested tests/fixtures/tnsnames.ora example used during development to validate real TNS alias resolution against Oracle XE.