Skip to main content
Glama
Oracle ERP MCP Server

Oracle ERP MCP Server

Oracle ERP MCP Server

CI Node TypeScript Oracle License M8ven Score

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: mcp-oraclefusion

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 declare explicit MCP tool hints: readOnlyHint: true, destructiveHint: false, idempotentHint: true, openWorldHint: false.

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.

Inputs: table (required).

list_reportsList curated reportsB
Read-onlyIdempotent

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

Inputs: no named parameters.

list_tablesList ERP tablesB
Read-onlyIdempotent

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

Inputs: filter.

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.

Inputs: sql (required), max_rows.

run_reportRun curated reportA
Read-onlyIdempotent

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

Inputs: name (required), params, max_rows.

Tool Schema Changelog

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

Loading tool schema changelog…

    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
    ResponsivenessOutdated data

    Related MCP Connectors

    Related MCP Servers

    • A
      license
      A
      quality
      D
      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
      28 PyPI
      4
      MIT
    • F
      license
      Not graded
      quality
      D
      maintenance
      Enables read-only exploration of Oracle databases through natural language, providing schema inspection and safe bounded SQL query execution.
      -
    • F
      license
      Not graded
      quality
      D
      maintenance
      Enables AI agents to connect to Oracle databases for schema exploration, PL/SQL source inspection, and read-only SQL queries, with optional write operations when explicitly enabled.
      -