Skip to main content
Glama
doberkofler

ask-oracle-mcp

by doberkofler
OverviewSchemaSourceRelated ServersDiscussionsAdmin
TypeScript
Local

ask-oracle-mcp

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

npm version License: MIT CI Coverage Status

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

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.

Available Tools

3 tools
describe_tableDescribe one Oracle tableA

Returns column names, data types, and nullability for exactly one table. Call this only for tables relevant to the question.

ParametersJSON Schema
NameRequiredDescriptionDefault
ownerNoSchema owner. Defaults to the connected user.
tableNameYesExact table name, for example "EMPLOYEES".

TDQS

A4/5.0
Behavior3/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

No annotations are present, so the description carries the full burden. It clearly discloses what the tool returns (column names, data types, nullability), but it does not explicitly state that it is a read-only operation or mention any side-effect-free behavior. This is adequate but not rich.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

Two sentences, no filler. The key return information is front-loaded, and the usage instruction is placed immediately after. Every sentence earns its place.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness4/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

For a simple two-parameter tool, the description covers what the tool returns, its single-table scope, and when to use it. The input schema covers parameter semantics. Slightly more explicit differentiation from sibling tools would make it fully complete.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters3/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

Schema description coverage is 100%, with both owner and tableName already documented in the input schema. The description adds no parameter-level detail beyond what the schema provides, so the baseline score of 3 applies.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description states a specific verb ('Returns') and a specific resource ('column names, data types, and nullability for exactly one table'). The 'exactly one table' qualifier differentiates it from list_tables, and the metadata focus differentiates it from run_sql.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines4/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description explicitly says to call this only for tables relevant to the question, giving clear usage context. It does not explicitly mention alternatives like list_tables or run_sql, but the scope is reasonably clear.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

list_tablesList Oracle tablesA

Lists table names for the connected schema or an explicit OWNER, optionally filtered by a SQL LIKE pattern. Call this before describe_table; it does not return columns.

ParametersJSON Schema
NameRequiredDescriptionDefault
likeNoOptional SQL LIKE pattern, for example "CUST%".
ownerNoSchema owner to inspect. Defaults to the connected user.

TDQS

A4/5.0
Behavior3/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

With no annotations, the description carries the full burden. It clarifies that only table names are returned and not columns, and hints at read-only behavior, but it doesn't describe output format, sorting, pagination, or error/limits behavior.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

Two short sentences front-load the core purpose and then provide a targeted usage note. There is no wasted wording; every sentence earns its place.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness4/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

For a simple, optional-parameter enumeration tool with no output schema, this description covers the what, the scope, the filter mechanism, and the key distinction from describe_table. It could add return-format details or permission context, but the core invocation is adequately complete.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters3/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

Schema description coverage is 100% and both parameters are already documented clearly in the schema. The description paraphrases those meanings ('explicit OWNER', 'optionally filtered by a SQL LIKE pattern') without adding new details beyond the schema.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

Description states a specific verb and resource: 'Lists table names for the connected schema or an explicit OWNER'. It also differentiates itself from describe_table by noting 'it does not return columns'.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines4/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

Gives explicit usage direction: 'Call this before describe_table', making the workflow intentions clear. It does not address when to prefer list_tables over run_sql, but does provide useful context for the primary sibling.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

run_sqlRun SQL statementA

Executes read-only SELECT/WITH SQL. This server was not started with --allow-write, so DML/DDL is disabled.

ParametersJSON Schema
NameRequiredDescriptionDefault
sqlYesThe SQL statement to execute without a trailing semicolon.
bindsNoNamed bind parameters, e.g. {"id": 42}.
allowWriteNoRequests non-read-only execution when the server was started with --allow-write.

TDQS

A4.1/5.0
Behavior3/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

Discloses the key safety trait—read-only execution with DML/DDL disabled—which is important since no annotations are provided. However, it is silent on return shape, large-result behavior, error handling, and what happens if allowWrite=true is passed, leaving meaningful behavioral gaps.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

Two tight sentences with no filler; the first front-loads the core purpose and the second provides essential environment context about write support.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness3/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

Covers the core operation and read-only constraint, and the schema handles parameter details. But with no output schema and no annotations, the lack of any return/result description or allowWrite-behavior note leaves the definition somewhat incomplete for a SQL execution tool.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters4/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

The schema already fully describes the sql, binds, and allowWrite parameters at 100% coverage. The description adds value by restricting sql to SELECT/WITH statements, which the schema does not explicitly state.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

States a specific action ('Executes') and resource ('read-only SELECT/WITH SQL'), immediately ruling out DML/DDL. This makes it easy to distinguish from sibling metadata tools like list_tables and describe_table.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines4/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

Clearly frames the tool as read-only and explains that the server was not started with --allow-write, so write attempts are not valid. It doesn't explicitly name the sibling tools as alternatives, but the intended context for running ad hoc read queries is clear.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

Tool Schema Changelog

Recent tool additions, removals, and schema changes observed during successful MCP inspections.

  1. 3 tool updatesv0.1.0
    • First observeddescribe_table
    • First observedlist_tables
    • First observedrun_sql
Deploy Server
One endpoint for every MCP toolRayrun runs every MCP server behind one URL and holds the credentials. Each call is decided per client, tool, and argument, then recorded.ray.runAd

TDQS

A4.3/5.0

Scored across 3 tools

Disambiguation5/5

Each tool has a clearly distinct purpose: listing tables, describing a table's schema, and executing read-only SQL. There is no overlap between these operations, so an agent can confidently select the right tool.

Naming Consistency5/5

All tool names follow a consistent verb_noun snake_case pattern: list_tables, describe_table, run_sql. The naming is predictable and matches the tool's action and target.

Tool Count5/5

Three tools is an appropriate scope for a read-only database exploration server. Each tool covers a necessary step in the workflow without unnecessary bloat.

Completeness5/5

The tool set fully covers the domain of read-only Oracle database exploration: discover tables, inspect schemas, and run queries. No obvious missing operations are needed for the stated purpose.

Maintenance

ActivityMaintained
ResponsivenessNo issues

Resources

Related MCP Connectors

Related MCP Servers

  • MCP Oracle Server

    YrsonHTM
    A
    license
    B
    quality
    D
    maintenance
    Enables interaction with Oracle databases through MCP by executing SELECT queries, describing table structures, and listing available tables with secure, read-only access.
    3
    7 npm
    2
    MIT

  • neosql-mcp

    unvus
    A
    license
    B
    quality
    B
    maintenance
    Enables MCP hosts to use NeoSQL Desktop tools for database management, including querying, table operations, and code generation, through a local stdio MCP server.
    10
    47 npm
    1
    Apache 2.0

  • Oracle DB MCP

    muazam99
    F
    license
    Not graded
    quality
    C
    maintenance
    Read-only MCP server that lets coding AI agents inspect Oracle Database schema through live metadata.
    -