homemade-mcp-oracle-database-10g-claude

Read-only utility for querying an Oracle Database 10g database from an MCP server and/or directly from the terminal via a Claude Code skill. See the original requirement in requeriments.txt.

Why this exists

Oracle 10g went out of extended support more than a decade ago. Modern Oracle drivers (python-oracledb, node-oracledb) use a "thin" mode by default that only speaks the Oracle network protocol from 12c onward. To talk to a 10g you need to force "thick" mode, relying on a locally installed Oracle Instant Client. Most generic Oracle MCPs don't expose that option, hence this custom utility.

Confirmed in this project: an Oracle Instant Client 19.x in thick mode does connect to an Oracle Database 10g Release 10.2.0.3.0 (there was no need to downgrade to Instant Client 11.2 as anticipated).

Related MCP server: MCP Oracle Server

Architecture

src/
  config.py           Carga y valida .env (conexion, Instant Client, politica)
  db.py                Conexion Oracle en modo thick (oracledb.init_oracle_client)
  sql_guard.py         Guard de solo-lectura: solo permite SELECT / WITH
  audit_log.py         Logging de auditoria (logs/query_audit.log)
  query_runner.py      Ejecuta SQL con limite de filas (MAX_ROWS), timeout y auditoria
  schema_inspector.py  Consultas de catalogo (list_tables / describe_table), SQL 10g-safe
  mcp_server.py        Servidor MCP (tools: run_query_tool, list_tables, describe_table)

scripts/
  test_connection.py   Prueba minima de conectividad (Fase 0)
  query.py             CLI de consultas (Fase 1) usado tambien por la skill

.claude/skills/oracle10g-query/SKILL.md   Skill de Claude Code (usa scripts/query.py)

Both the CLI and the MCP server reuse the same core (config -> db -> sql_guard/query_runner), so the security policy and limits are applied once, in a single place.

Security model (defense in depth)

1. At the database level: the configured Oracle user already has read-only permissions granted by the DBA. This is the primary and real barrier.

2. At the software level (src/sql_guard.py): before sending any "free" SQL (the one that arrives via run_query/run_query_tool), it is validated to be a single SELECT or WITH ... SELECT statement. Any INSERT/UPDATE/DELETE/MERGE, DDL, GRANT/REVOKE, PL/SQL (BEGIN, EXEC, ...) or stacked statements with ; are rejected. Catalog queries (list_tables/describe_table) don't go through this guard because their SQL is fixed and written by this same utility; only their parameters (schema/table_name) are passed as bind variables.

3. Execution limits: MAX_ROWS limits rows returned (via fetchmany, the result indicates truncated: true if applicable) and QUERY_TIMEOUT_SECONDS cuts the query off if it hangs (cursor.callTimeout).

4. Auditing: every execution (successful, with an Oracle error, or rejected by the guard) is logged in logs/query_audit.log with user, SQL, and result.

Installation

python -m venv .venv
.venv\Scripts\python -m pip install -r requirements.txt
copy .env.example .env

Edit .env with your real data (see comments in .env.example): host/port/service_name or SID, username and password of the read-only user, and the path to your Oracle Instant Client (ORACLE_INSTANT_CLIENT_DIR). Never upload .env to a repository.

Usage

1. Test connectivity

.venv\Scripts\python scripts\test_connection.py

2. Query CLI

.venv\Scripts\python scripts\query.py "SELECT * FROM alguna_tabla WHERE ROWNUM <= 10" --format json
.venv\Scripts\python scripts\query.py --file consulta.sql --format table

3. MCP server

Run it manually (stdio):

.venv\Scripts\python -m src.mcp_server

To register it in an MCP client (Claude Desktop, Claude Code, etc.), see mcp-config.example.json — adjust the paths to your machine and add that block to the client's mcpServers config.

Exposed tools:

• run_query_tool(sql) — runs a SELECT (read-only guard applies).

• list_tables(schema?) — lists tables from the catalog (all_tables).

• describe_table(table_name, schema?) — columns of a table (all_tab_columns).

4. Claude Code skill

See .claude/skills/oracle10g-query/SKILL.md. It activates when you ask Claude Code to query or verify data in this database; internally it uses the same scripts/query.py.

Oracle 10g quirk to keep in mind

Oracle 10g doesn't support FETCH FIRST n ROWS ONLY / OFFSET (12c+ syntax). To limit rows in a query use ROWNUM:

SELECT * FROM (
  SELECT col1, col2 FROM tabla ORDER BY col1
) WHERE ROWNUM <= 20