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.

Tools

| Tool | Input | Behavior | | --- | --- | --- | | list_tables | {owner?: string, like?: string} | Lists table names only, optionally filtered with a bound SQL LIKE pattern. | | describe_table | {tableName: string, owner?: string} | Returns columns for exactly one table. | | run_sql | {sql: string, binds?: object, allowWrite?: boolean} | Executes SQL. Writes require both the server --allow-write flag and allowWrite: true. |

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

Configure Oracle with environment variables:

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

The demo and standalone server also accept persistent CLI configuration:

ask-oracle \
  --oracle-user myuser \
  --oracle-password mypassword \
  --oracle-server "host:1521/service_name"

The same --oracle-user, --oracle-password, and --oracle-server options are accepted by ask-oracle-mcp. Every supplied Oracle CLI value is saved for later runs. A password supplied on the command line can be visible in shell history and process listings; prefer ORACLE_PASSWORD where that exposure is unacceptable.

Run the Server

Run the published package directly from an MCP host or terminal:

npx -y ask-oracle-mcp@latest

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 or CLI options 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.

When installed from npm, run the interactive client with:

npx -y --package ask-oracle-mcp@latest ask-oracle

On the first interactive run, the client opens a guided setup that:

1. Lists pi-ai providers and their authentication status.
2. Runs the selected provider's API-key or OAuth login flow when needed.
3. Searches the provider's model catalog and saves the selection.

Run setup again at any time with:

# npm package
npx -y --package ask-oracle-mcp@latest ask-oracle --configure

# development checkout
pnpm run demo -- --configure

Persistent data defaults to ${XDG_CONFIG_HOME:-~/.config}/ask-oracle-mcp:

• auth.json stores pi-ai-compatible LLM credentials.
• llm-config.json stores the selected provider and model.
• oracle-config.json stores the Oracle user and connect string.
• oracle-auth.json stores the Oracle password.

Credential and configuration files are written with mode 0600. Set ASK_ORACLE_CONFIG_DIR to override the application directory. LLM_AUTH_FILE and LLM_CONFIG_FILE continue to override the individual LLM paths. The client never writes credentials to llm-config.json.

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

ollama pull llama3.1
ollama serve

Then run the built demo and select Ollama. Setup lists models installed by the configured Ollama server:

# 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 automation and non-interactive execution, configuration can still be supplied explicitly. CLI values override environment variables, which override saved configuration:

1. Provider: --llm, LLM_PROVIDER, saved provider, interactive selection, then the non-interactive ollama fallback.
2. Model: --model, LLM_MODEL, OLLAMA_MODEL/OPENAI_MODEL, matching saved model, then interactive selection.
3. Oracle: --oracle-user/--oracle-password/--oracle-server, ORACLE_USER/ORACLE_PASSWORD/ORACLE_SERVER, then saved Oracle configuration.

--configure starts a fresh interactive selection and ignores environment and saved provider/model defaults. Combine it with --llm or --model to preselect either value explicitly.

For example, OpenAI with an environment API key:

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

pnpm run demo

Or select both values through CLI options:

pnpm run demo -- --llm openai --model gpt-4o-mini

OAuth login and token refresh are handled directly by the guided setup through pi-ai. API-key environment variables, saved credentials, ambient cloud credentials, and provider-specific endpoints continue to be resolved by the selected 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

| Command | Purpose | | --- | --- | | pnpm run dev | Rebuild with Vite in watch mode. | | pnpm run build | Build dist/server.js and dist/client-example.js. | | pnpm run start | Run the built MCP server. | | pnpm run demo | Run the built Ollama demo host. | | pnpm run typecheck | Run strict TypeScript checks without emitting files. | | pnpm run lint | Run oxlint. | | pnpm run format | Format files with oxfmt. | | pnpm run format:check | Check formatting without changing files. | | pnpm run test | Run unit tests with Vitest and coverage. | | pnpm run integration-test | Run the integration-test command. | | pnpm run ci | Run typecheck, lint, formatting, build, unit tests, and integration tests. |

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.