ask-oracle-mcp

MCP server for Oracle DB: schema introspection and SQL execution over stdio.

The server has no LLM dependency. Tool selection, natural-language-to-SQL translation, and answer composition belong to the MCP host. The bundled demo uses pi-ai to run the same bounded tool-calling loop with Ollama or a configured hosted provider.

Architecture

client-example.ts (transport and terminal I/O)
        |
        v
agent.ts -- bounded LLM/MCP tool-calling loop
        |
        v
llm.ts (pi-ai providers, models, and authentication)
        | MCP over stdio
        v
server.ts -- list_tables (compact discovery)
          |- describe_table (one table's columns)
          `- run_sql (execution, read-only by default)
        |
        v
db.ts (oracledb thin-mode pool)

The model receives tool definitions directly from MCP listTools(). It decides when to call list_tables, describe_table, and run_sql, then uses the tool results to produce a plain-language answer. It never receives an automatic whole-schema dump.

Related MCP server: neosql-mcp

Tools

Requirements

• Node.js 24 or newer

• pnpm 11

• Access to an Oracle database

• Ollama or credentials for a pi-ai provider for the optional demo CLI

Oracle Database access uses node-oracledb thin mode, so Oracle Instant Client is not required.

Setup

pnpm install
pnpm run build

Set all required Oracle environment variables:

export ORACLE_USER=myuser
export ORACLE_PASSWORD=mypassword
export ORACLE_SERVER="host:1521/service_name"

Run the Server

Build and start the standalone stdio server:

pnpm run build
pnpm start

An MCP host can equivalently launch node /absolute/path/to/ask-oracle-mcp/dist/server.js and provide the three Oracle environment variables to that process.

The server writes one readiness banner and errors to stderr while reserving stdout for the MCP protocol. Detailed lifecycle tracing is opt-in through DEBUG.

The server is strictly read-only by default. To make writes available to trusted MCP callers, opt in explicitly:

pnpm start -- --allow-write

Run the Demo

The bundled demo lets the model call MCP tools until it has a final answer. The loop is capped at 8 model turns and 16 total tool calls. Every tool response is limited to 20,000 characters before it is added to model context, preventing schema or query results from exhausting the context window. Generated SQL is stripped of markdown fences and trailing semicolons before being sent to node-oracledb.

Provider selection uses --llm <provider>, then LLM_PROVIDER, and defaults to ollama. Set the model with LLM_MODEL. Existing Ollama setups may continue using OLLAMA_MODEL, and OpenAI setups may use OPENAI_MODEL.

For local inference, install and start Ollama with a suitable tool-calling model:

ollama pull llama3.1
ollama serve

Then configure the model and run the built demo:

export OLLAMA_MODEL=llama3.1
# Optional; defaults to http://localhost:11434
export OLLAMA_HOST=http://localhost:11434

pnpm run build
pnpm run demo

No API key is required. Data stays between the local Ollama process and Oracle.

For a hosted provider, select any provider in pi-ai's catalog and configure its documented environment credentials. For example, OpenAI with an API key:

export LLM_PROVIDER=openai
export LLM_MODEL=gpt-4o-mini
export OPENAI_API_KEY=your-api-key

pnpm run demo

The CLI option overrides LLM_PROVIDER, for example pnpm run demo -- --llm anthropic. Run pnpm exec pi-ai list to list OAuth-capable providers. OAuth login and automatic token refresh use pi-ai's CLI-compatible auth.json credential file:

pnpm exec pi-ai login openai-codex
export LLM_PROVIDER=openai-codex
export LLM_MODEL=gpt-5.2-codex
pnpm run demo

auth.json is gitignored and contains secrets. Set LLM_AUTH_FILE to use a different path. API-key environment variables, OAuth credentials, ambient cloud credentials, and provider-specific endpoints are resolved by the selected pi-ai provider.

The demo uses an Inquirer prompt and accepts multiple questions in one session. Enter /exit or /quit, press Ctrl-C, or close input to disconnect cleanly. A failed question is reported without ending the session. Expected configuration and connection failures are shown without stack traces and explain which environment variable, login, local service, or model needs attention.

The demo is read-only unless write capability is explicitly enabled for both the agent and its spawned server:

pnpm run demo -- --allow-write

Only use this flag with a trusted model and trusted prompts. The model must still request allowWrite: true for a non-read-only statement.

Debug Tracing

Normal operation shows only the server readiness banner, the client mode/exit banner, prompts, answers, and errors. Enable detailed traces with the debug namespaces:

# Server lifecycle only
DEBUG=ask-oracle-mcp:server pnpm start

# Client, agent turns/tool calls, LLM requests, and spawned server
DEBUG=ask-oracle-mcp:* pnpm run demo

Available namespaces are ask-oracle-mcp:server, ask-oracle-mcp:client, ask-oracle-mcp:client:agent, and ask-oracle-mcp:client:llm. Traces omit credentials, connection details, questions, SQL text, and result content.

Development

Safety Notes

• run_sql rejects non-SELECT and non-WITH statements unless the server was launched with --allow-write and the caller explicitly supplies allowWrite: true. This guard is not a complete SQL-injection defense.

• Use bind parameters for user-supplied literal values.

• Schema discovery scopes introspection to one owner and one detailed table response at a time. Do not reintroduce a whole-schema metadata tool.

• Write statements commit automatically when they affect rows. Only trusted hosts should enable writes.