Skip to main content
Glama
GokhanHepyetiker

Oracle ERP MCP Server

Oracle ERP MCP Server

CI Node TypeScript Oracle License

A secure, read-only Model Context Protocol server that lets AI assistants (Claude, GitHub Copilot, Cursor, …) explore and report on Oracle-based ERP databases. Users can ask their ERP data business questions in plain language. They don't need to write SQL or file a ticket for a new report.

"Which waste paper supplier caused the highest moisture deductions last quarter?" "Show me the scrap rate per corrugator and tell me which product is the worst." "What is the current stock value of raw materials in warehouse W01?"

The project ships with a dockerised Oracle Database 23ai Free instance pre-loaded with a realistic ERP schema for a paper & corrugated packaging manufacturer: purchasing, weighbridge (truck scale) tickets, inventory, production work orders, and a curated PL/SQL report package.


Features

  • 5 MCP tools: schema discovery, table description, guarded ad-hoc SQL, curated reports.

  • Defence-in-depth security: a least-privilege DB user, READ ONLY transactions and a SQL guard (see Security model).

  • Curated reports: expose existing PL/SQL procedures that return a SYS_REFCURSOR. You add them through a JSON file, with no code changes.

  • LLM-friendly metadata: table and column comments, primary keys and foreign keys help the model write correct joins.

  • Safe output: row limits with truncation flags, query timeouts, and dates formatted without timezone shifts. LOB and BLOB values are handled safely.

  • Audit log: every tool call is logged as JSON to stderr (tool, SQL, row count, duration, error).

  • No Oracle Client needed: uses node-oracledb in Thin mode.

  • Tested: unit tests with an in-memory MCP client, plus integration tests against a real Oracle database in CI.

Related MCP server: Oracle DB MCP Server

Architecture

flowchart LR
    subgraph Client["AI client"]
        LLM["Claude / Copilot / Cursor"]
    end
    subgraph Server["oracle-erp-mcp (Node.js)"]
        Tools["MCP tools"]
        Guard["SQL guard"]
        Reports["Report registry<br/>config/reports.json"]
        Audit["Audit log (stderr)"]
    end
    subgraph DB["Oracle Database"]
        Reader["MCP_READER<br/>(READ grants only)"]
        Schema["ERP_DEMO schema"]
        Pkg["ERP_REPORTS package<br/>(SYS_REFCURSOR)"]
    end
    LLM -- "stdio / JSON-RPC" --> Tools
    Tools --> Guard --> Reader
    Tools --> Reports --> Pkg
    Tools --> Audit
    Reader --> Schema
    Pkg --> Schema

Tools

Tool

Description

list_tables

Tables and views in the configured schema, with business descriptions and approx. row counts.

describe_table

Columns, types, nullability, comments, primary key and foreign keys.

run_query

Executes a single guarded SELECT / WITH statement and returns JSON rows (row-limited).

list_reports

Lists curated reports and their parameters.

run_report

Runs a curated report (PL/SQL procedure returning SYS_REFCURSOR) with validated parameters.

All tools are annotated with readOnlyHint: true.

Security model

An LLM must never be able to change ERP data, so the server applies several independent layers:

#

Layer

What it does

1

Least-privilege user

MCP_READER only has CREATE SESSION, READ on ERP tables and EXECUTE on the report package. READ (unlike SELECT) also forbids SELECT … FOR UPDATE row locks.

2

Read-only transaction

Every call runs inside SET TRANSACTION READ ONLY and is always rolled back.

3

SQL guard

Single statement only; must start with SELECT/WITH. Rejects DML/DDL/PL-SQL keywords, DBMS_*/UTL_* packages, URI types and DB links. Comments, string literals (incl. q'[...]') and quoted identifiers are parsed so they can't be used to hide keywords.

4

Curated report registry

Only procedures listed in config/reports.json can be called; parameters are type-checked and always bound, never concatenated.

5

Resource limits

Row limit (MCP_MAX_ROWS, hard cap 1000) and per-call timeout (MCP_QUERY_TIMEOUT_MS).

6

Audit trail

JSON log line per tool call on stderr.

The integration tests bypass the SQL guard on purpose. They check that the database itself still rejects DELETE, SELECT … FOR UPDATE and DDL.

IMPORTANT

Layer 1 is the real security boundary. In production, always connect with a dedicated read-only user, and preferably against a reporting replica or standby.

Quick start

Prerequisites: Node.js ≥ 18, Docker.

git clone https://github.com/GokhanHepyetiker/oracle-erp-mcp.git
cd oracle-erp-mcp
npm install

# 1. Start Oracle 23ai Free with the demo ERP schema (first start takes a few minutes)
npm run db:up

# 2. Build the server
npm run build

# 3. Try it in the MCP Inspector
#    (set the variables from .env.example in your shell first)
npm run inspector

Use it from VS Code (GitHub Copilot)

This repository already contains .vscode/mcp.json for the demo database. Open the folder in VS Code, run npm run build, and then start the oracle-erp server from the MCP view. Its tools then become available in Copilot Chat (Agent mode).

Use it from Claude Desktop

Add the server to claude_desktop_config.json:

{
  "mcpServers": {
    "oracle-erp": {
      "command": "node",
      "args": ["/absolute/path/to/oracle-erp-mcp/dist/index.js"],
      "env": {
        "ORACLE_USER": "mcp_reader",
        "ORACLE_PASSWORD": "McpReader#2026",
        "ORACLE_CONNECT_STRING": "localhost:1521/FREEPDB1",
        "ORACLE_SCHEMA": "ERP_DEMO"
      }
    }
  }
}

Configuration

Variable

Required

Default

Description

ORACLE_USER

yes

Read-only database user.

ORACLE_PASSWORD

yes

Password of that user.

ORACLE_CONNECT_STRING

yes

Easy Connect string, e.g. host:1521/SERVICE.

ORACLE_SCHEMA

yes

Schema exposed to the assistant (e.g. your ERP schema).

MCP_MAX_ROWS

no

200

Default and maximum rows returned per call (max 1000).

MCP_QUERY_TIMEOUT_MS

no

15000

Round-trip timeout per database call.

MCP_REPORTS_FILE

no

config/reports.json

Path to the curated report registry.

Adding your own reports

Any PL/SQL procedure whose last parameter is an OUT SYS_REFCURSOR can be exposed:

PROCEDURE open_orders (p_supplier_code IN VARCHAR2, p_result OUT SYS_REFCURSOR);
{
  "name": "open_orders",
  "title": "Open purchase orders",
  "description": "Purchase orders that are not fully received yet.",
  "procedure": "ERP.PURCHASING_REPORTS.OPEN_ORDERS",
  "parameters": [
    { "name": "supplier_code", "type": "string", "description": "Optional supplier filter" }
  ]
}

Supported parameter types are string, number and date (passed as YYYY-MM-DD). Grant EXECUTE on the package to the read-only user.

Demo data model

Table

Content

SUPPLIERS

15 domestic and import vendors (waste paper, pulp, chemicals, spare parts).

ITEMS

Raw materials, chemicals, paper reels (testliner/fluting/kraftliner), corrugated boxes, spares.

WAREHOUSES

Raw material yard, reel store, finished goods (2 sites), chemicals & spares.

PURCHASE_ORDERS

420 orders in TRY / EUR / USD with realistic statuses.

PURCHASE_ORDER_LINES

Lines with buyer-specific discount behaviour and received quantities.

WEIGHBRIDGE_TICKETS

900 truck-scale tickets with moisture/contamination deductions (payable_kg).

WORK_ORDERS

320 paper machine (PM1/PM2) and corrugator (CORR1/CORR2) orders with scrap.

STOCK_MOVEMENTS

~3,000 receipts, production issues and receipts, and shipments.

The data is generated deterministically (seeded DBMS_RANDOM) and contains patterns for the assistant to discover. For example, some suppliers deliver systematically wetter or more contaminated loads, and one corrugator produces more scrap than the other.

Curated reports in ERP_DEMO.ERP_REPORTS: supplier_performance, waste_paper_quality, stock_balance, production_scrap, monthly_purchases.

Example questions

  • "List the tables and explain the data model in two sentences."

  • "Using the waste paper quality report, which suppliers should we talk to about moisture?"

  • "Compare the average discount each buyer achieved in 2026."

  • "Which finished goods have the highest stock value in the Ankara hub?"

  • "Show monthly TRY purchase spend for 2025 and describe the trend."

Development

npm run typecheck          # TypeScript strict mode
npm test                   # unit tests (no database needed)
npm run test:integration   # requires `npm run db:up`
npm run dev                # run from source with tsx
npm run db:down            # stop and remove the demo database
src/
  index.ts       entry point (stdio transport, graceful shutdown)
  server.ts      MCP tool definitions and audit logging
  db.ts          Oracle access layer (pool, read-only transactions, metadata)
  sqlGuard.ts    static SQL validation
  reports.ts     report registry, parameter validation, PL/SQL call builder
  config.ts      environment configuration (zod)
config/reports.json   curated report registry
docker/init/          schema, seed data, report package, read-only user
tests/                unit + integration tests (node:test)

Roadmap

  • MCP resources for schema documentation and prompts for common analyses

  • Optional table/column allow-list and PII masking

  • Streamable HTTP transport with authentication

  • Export results as CSV/Excel

License

MIT © Gökhan Hepyetiker

Available Tools

5 tools
describe_tableDescribe ERP tableA
Read-onlyIdempotent

Show columns, data types, comments, primary key and foreign keys of a table or view. Use it before writing SQL.

ParametersJSON Schema
NameRequiredDescriptionDefault
tableYesTable or view name, e.g. PURCHASE_ORDERS or ERP.ITEMS

TDQS

A3.8/5.0
Behavior3/5

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

Annotations already declare readOnlyHint, idempotentHint, non-destructive and closed-world, so the safety profile is fully covered. The description adds that PK/FK/comment metadata is returned, but says nothing about permissions, visibility of system schemas, or behavior on an unknown table name, so it only modestly exceeds the annotations.

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, zero filler, with the returned metadata front-loaded and the usage cue trailing. Every clause 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?

With no output schema, the description must convey the return shape, and it does by enumerating the returned metadata. It is complete for a single-parameter introspection tool, with only minor gaps such as error behavior for non-existent tables.

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?

Only one parameter and schema description coverage is 100%, so the schema already documents 'table' including the ERP.ITEMS qualified-name form. The description's 'table or view' phrasing mirrors rather than extends the schema, so the baseline 3 applies.

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

Purpose4/5

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

States a concrete verb ('Show') and a precisely enumerated resource: columns, data types, comments, primary key and foreign keys of a table or view. An agent can distinguish this from list_tables or run_query by the metadata it returns, though the description never names those siblings explicitly.

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?

'Use it before writing SQL' gives clear situational guidance that implicitly routes the agent away from run_query when it lacks schema knowledge. It stops short of explicit when-not conditions or naming the alternative tool, so it falls just under a 5.

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

list_reportsList curated reportsB
Read-onlyIdempotent

List curated, pre-approved ERP reports (PL/SQL procedures returning SYS_REFCURSOR) and their parameters.

ParametersJSON Schema
NameRequiredDescriptionDefault

No parameters

TDQS

B3.4/5.0
Behavior3/5

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

Annotations already declare readOnlyHint, idempotentHint, non-destructive, and closed-world, so the safety profile is covered. The description adds useful domain context (these are curated, pre-approved PL/SQL procedures returning SYS_REFCURSOR), but says nothing about result volume, pagination, or catalog size.

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?

A single front-loaded sentence that identifies the resource and its nature with no filler. Nothing is redundant or buried.

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?

With no output schema, the description carries the burden of explaining what comes back. It does state that reports and their parameters are returned, but gives no sense of the return shape, pagination, or how the listed parameters should be used when calling run_report.

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 tool takes zero parameters, so the baseline is 4. The mention of 'their parameters' correctly signals that parameter metadata is part of the returned payload rather than something the caller supplies.

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

Purpose4/5

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

States a specific verb (List) and resource (curated, pre-approved ERP reports) and adds the technical nature of those reports (PL/SQL procedures returning SYS_REFCURSOR), which is more than a tautology. It implicitly contrasts with run_report, but never names any sibling to differentiate explicitly.

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

Usage Guidelines2/5

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

No when-to-use guidance, no prerequisites, and no alternatives are named despite run_report being an obvious next step after discovery. The intended workflow (discover then run) is left entirely to inference.

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

list_tablesList ERP tablesB
Read-onlyIdempotent

List tables and views in the ERP schema with their business descriptions.

ParametersJSON Schema
NameRequiredDescriptionDefault
filterNoCase-insensitive substring of the table name

TDQS

B3.4/5.0
Behavior3/5

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

Annotations already declare readOnlyHint, idempotentHint, destructiveHint=false and openWorldHint=false, so the safety profile is fully covered by structured data. The description adds only that results include business descriptions; it says nothing about result size, pagination, or how many tables are typically returned.

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?

One sentence, front-loaded with the verb and resource, with zero filler. Nothing is repeated from the title or schema.

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?

With no output schema, the description usefully signals the return content ('tables and views... with their business descriptions'), and annotations carry the safety profile. It is nearly complete for a simple zero-required-param listing tool, lacking only a note on result scale.

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 coverage is 100% and the single 'filter' parameter is fully documented in the schema as a case-insensitive substring match. The description adds no syntax or format detail beyond that, so the baseline 3 applies.

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

Purpose4/5

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

The description names a specific verb and resource ('List tables and views in the ERP schema') plus a scope detail ('with their business descriptions'), so the agent knows exactly what is returned. It does not explicitly distinguish itself from siblings like describe_table or list_reports, which is the only gap.

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

Usage Guidelines2/5

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

There is no guidance on when to use this versus describe_table (for column-level detail) or run_query, and no mention of prerequisites or when-not-to-use. Usage is only implied by the word 'List'.

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

run_queryRun read-only SQLA
Read-onlyIdempotent

Execute a single Oracle SELECT/WITH statement and return at most 200 rows as JSON. Data-changing statements, PL/SQL, DB links and DBMS_/UTL_ packages are rejected.

ParametersJSON Schema
NameRequiredDescriptionDefault
sqlYesA single Oracle SQL SELECT or WITH statement
max_rowsNoMaximum rows to return (default and upper limit: 200)

TDQS

A4.3/5.0
Behavior4/5

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

Annotations already declare readOnly/idempotent/non-destructive, so safety is largely covered. The description adds genuinely non-duplicative behavior: a hard 200-row return cap, JSON output, and an enumerated rejection list (DDL/DML, PL/SQL, DB links, DBMS_/UTL_) that the annotations do not convey. It stops short of covering errors, timeouts, or multi-statement handling.

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, zero filler, with the core action and result limit front-loaded before the constraint list. Every clause carries information an agent can act on.

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

Completeness5/5

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

For a two-parameter read-only query tool with no output schema, the description covers invocation scope, the dangerous/rejected cases, the return format, and the size ceiling. Nothing essential to calling it correctly is missing.

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%, so both parameters are already documented in the schema. The description's restatement of the 200-row maximum and the single-statement constraint adds no syntax or format meaning beyond those schema descriptions, so the baseline 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?

States a specific verb (Execute) plus the exact resource class (a single Oracle SELECT/WITH statement) and the result contract (at most 200 rows as JSON). This is clearly distinguishable from siblings like run_report, list_tables, and describe_table, which are report/catalog oriented rather than free-form 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?

Gives explicit when-not guidance: data-changing statements, PL/SQL, DB links and DBMS_/UTL_ packages are rejected, which tells the agent when this tool will fail. It does not, however, say when to prefer run_report over run_query or mention any other alternative, so the routing guidance is one-sided.

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

run_reportRun curated reportA
Read-onlyIdempotent

Run a curated report by name. Use list_reports to see available reports and parameters.

ParametersJSON Schema
NameRequiredDescriptionDefault
nameYesReport name from list_reports
paramsNoReport parameters, e.g. {"date_from": "2026-01-01"}. Dates use YYYY-MM-DD.
max_rowsNo

TDQS

A3.5/5.0
Behavior2/5

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

Annotations already declare readOnlyHint, idempotentHint, destructiveHint=false and a closed world, so the safety profile is fully covered by structured data. The description adds no behavioral context of its own: no note on cost/latency of running a report, no indication of what happens if the name is unknown, and no mention of result size behavior despite the max_rows cap.

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, zero filler, with the action stated first and the discovery prerequisite second. Nothing could be removed without losing information.

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?

For a simple read-only tool with no output schema, the description covers the core invocation path, but the undocumented max_rows parameter and the absence of any hint about the returned result shape leave gaps. It is adequate rather than 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 coverage is 67%: name and params are documented in the schema, but max_rows has no description anywhere. The description adds one genuinely useful pointer — that report parameters are report-specific and discoverable via list_reports — but it does not explain max_rows or the params format beyond what the schema already gives.

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

Purpose4/5

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

States a specific verb and resource ('Run a curated report by name'), which is precise enough to separate it from the ad-hoc run_query sibling. It does not explicitly contrast itself with run_query, so the agent must infer that 'curated' means pre-defined rather than arbitrary 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?

Explicitly directs the agent to list_reports as the discovery step for both report names and their parameters, which is the key prerequisite for calling this tool. It gives no exclusion guidance (e.g. when to prefer run_query instead), but the positive workflow 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. 5 tool updatesv0.1.0
    • First observeddescribe_table
    • First observedlist_reports
    • First observedlist_tables
    • First observedrun_query
    • First observedrun_report

TDQS

A4/5.0

Scored across 5 tools

Disambiguation5/5

Each tool has a clearly distinct purpose: listing vs running curated reports, listing vs describing tables, and a separate ad-hoc query tool. There is no overlap between the discovery tools and the execution tools.

Naming Consistency5/5

All five tools follow a consistent verb_noun snake_case pattern (list_reports, run_report, list_tables, describe_table, run_query). The convention is predictable throughout.

Tool Count5/5

Five tools is well-scoped for a read-only ERP query/reporting interface, with each tool earning its place across discovery and execution. No redundant or trivial tools.

Completeness4/5

The surface covers the full read-only lifecycle: discover reports and tables, inspect schema, and execute both curated and ad-hoc queries. Minor gaps like pagination beyond the 200-row cap or schema search are workable but slightly limiting.

Maintenance

ActivityMaintained
ResponsivenessNo issues

Related MCP Connectors

Related MCP Servers

  • A
    license
    A
    quality
    D
    maintenance
    Provides flexible access to Oracle databases for AI assistants like Claude, supporting SQL queries across multiple schemas with comprehensive database introspection capabilities.
    6
    82 npm
    11
    MIT
  • A
    license
    A
    quality
    C
    maintenance
    Enables AI tools to interact with Oracle databases through query execution, schema browsing, stored procedure calls, and transaction management. Supports multiple database connections with safety features like read-only mode and dangerous query detection.
    16
    MIT
  • A
    license
    B
    quality
    D
    maintenance
    Read-only access to Oracle Fusion Cloud ERP data via natural language queries, with support for accounts payable, procurement, general ledger, and more.
    30
    3
    MIT
  • F
    license
    Not graded
    quality
    B
    maintenance
    Enables read-only exploration of Oracle databases through natural language, providing schema inspection and safe bounded SQL query execution.
    -