plsql-mcp

Parse Oracle PL/SQL with ANTLR4 and expose a structural index — packages, routine signatures, a call graph, and table usage — to AI coding agents over MCP (Model Context Protocol).

Why

PL/SQL codebases are large, procedural, and hard for an AI agent to navigate by text search alone: the meaning lives in cross-references (who calls this procedure, what tables it touches, which package a function belongs to). A real parser turns that into precise, queryable answers, so an agent spends tokens on reasoning instead of grepping.

Instead of returning raw parse trees, the MCP tools return answers: get_signature, find_callers, get_calls, list_routines.

Architecture

grammar/            Oracle PL/SQL grammar (antlr/grammars-v4) + Python base classes
  PlSqlLexer.g4  PlSqlParser.g4  PlSql{Lexer,Parser}Base.py  transformGrammar.py
tools/              vendored antlr-4.13.2-complete.jar (gitignored; see Setup)
scripts/
  generate_parser.sh   regenerate the Python parser from the grammar
src/plsql_mcp/
  parser/           ANTLR-generated code (regenerated, not hand-edited)
  preprocess.py     strip the SQL*Plus command layer + inline conditional compilation
  parse.py          ergonomic wrapper: parse_text / parse_file -> ParseResult
  analysis/
    extract.py      parse tree  -> structural Unit (packages, routines, calls, tables)
    project.py      scan a directory tree -> ProjectIndex (cross-file call graph)
  native.py         calls the fast Java extractor (falls back to Python if absent)
  cache.py          persistent per-file cache; re-parses only changed files
  server.py         FastMCP server exposing the index as agent tools
native/             Java parser+extractor (fast backend)
  src/main/java/plsql/   Extractor.java (port of extract.py), Main.java, Json.java
  build.sh          regenerates the Java parser and builds plsql-extractor.jar
samples/            example PL/SQL
tests/              pytest smoke + extraction tests

Pipeline: grammar → preprocess.py → generated parser → parse.py → extract.py → project.py → server.py.

Preprocessing

Real Oracle sources are deployment scripts, not pure PL/SQL. Before parsing, preprocess.py (on by default) removes the SQL*Plus command layer that the grammar can't parse — SET/PROMPT/WHENEVER/@@/&&substitution, EXEC shorthand — and inlines PL/SQL conditional compilation ($IF/$THEN/$ELSE/$END) so every branch's symbols are captured. It is careful about collisions (UPDATE … SET, EXIT WHEN, EXECUTE IMMEDIATE are left intact) and preserves line numbers.

Parsing backend (performance)

The ANTLR Python runtime parses this 10k-line grammar slowly (~1–20 s/file cold). So parsing + extraction run in a bundled Java tool (native/plsql-extractor.jar, a faithful port of extract.py), invoked once per batch; Python still owns preprocessing and everything above extraction, and the two extractors produce byte-identical output (checked in tests/test_native.py).

Measured on qms-chr (322 files): cold scan 280 s → 12 s; a single-file edit re-parse 20 s → 1.4 s (JVM boot + parse). If Java or the jar is missing, native.py transparently falls back to the pure-Python extractor (slower but identical results). Build the jar with native/build.sh (needs Java + the vendored ANTLR jar).

Caching

Parsing is the expensive step, so the server keeps a persistent, self-healing per-file cache (cache.py), stored under ~/.cache/plsql-mcp/<hash-of-root> (never in your source tree). Each query reconciles the cache with the files on disk via a cheap stat() sweep and re-parses only the files that changed — so edits by you or another agent are picked up automatically, with no manual reindex. New files are added, deleted files dropped. A file whose mtime changed but content did not (e.g. after git checkout) is detected by content hash and not re-parsed. Result: a one-time cold scan, then sub-second updates.

Bump CACHE_VERSION in cache.py after changing extraction logic so old cached units are discarded.

Coverage

Measured against OraOpenSource/logger (scripts/eval_parse.py): 25/28 files parse clean, 106 routines extracted. The core package files (logger.pkb → 69 routines, logger.pks, logger_test.pkb → 35) parse well; the remainder are multi-statement install scripts.

Setup

Requires Python ≥ 3.10 and Java (only for regenerating the parser).

python3 -m venv .venv && source .venv/bin/activate
pip install -e ".[dev]"

# Regenerate the parser (only needed if the grammar changes; jar auto-vendored):
#   the jar is gitignored — download once into tools/ then run:
./scripts/generate_parser.sh

Try it

python -c "from plsql_mcp.analysis.project import scan; \
[print(r.signature) for r in scan('samples').routines]"

pytest -q

Use as an MCP server

Point the server at your PL/SQL source tree with PLSQL_ROOT, then register it with your coding agent. Example Claude Code MCP config:

{
  "mcpServers": {
    "plsql": {
      "command": "/absolute/path/to/plsql-mcp/.venv/bin/plsql-mcp",
      "env": { "PLSQL_ROOT": "/absolute/path/to/your/plsql/sources" }
    }
  }
}

Tools exposed: list_packages, list_routines, get_signature, find_definition, find_references, get_calls (with internal/builtin/external classification and per-call overload resolution by argument count), get_table_usage (per-table read/write + written columns), find_callers, reindex.

Table usage records, per routine, which tables/views are read vs. written (SELECT vs. INSERT/UPDATE/DELETE/MERGE), the columns written (from INSERT column lists, UPDATE SET, MERGE — precise, single-table), and the columns read. Read columns are attributed to tables using a DDL catalog built from CREATE TABLE / ALTER TABLE ... ADD across the tree: a qualified alias.col resolves via the query's alias map; an unqualified column is attributed only when exactly one table in the FROM has that column. Ambiguous or catalog-unknown columns are omitted, not guessed. Attribution runs at project-build time, so it stays correct as table DDL changes.

If a schema's base DDL lives only in the database (e.g. a Flyway repo with just migrations), the source catalog is naturally incomplete — a DB-backed catalog is the roadmap fix.

Overloaded routines (same name, different parameters — e.g. get_avg(...) with 5 vs 6 params) are surfaced as a labeled group; each call site is pinned to the specific overload by argument count when unambiguous. Type-only overloads (same arg count, different types) are reported as candidates rather than guessed.

Roadmap

• SQL*Plus command-layer stripping + conditional-compilation inlining (preprocess.py).
• Package spec parsing (.pks); spec + body merged in the symbol table.
• Static call resolution: classify calls as internal / built-in / external.
• find_definition / find_references tools.
• Persistent, self-healing per-file index cache (auto-picks up edits).
• Overloaded routines surfaced as a group; calls disambiguated by argument count.
• Per-table read/write access with columns written (INSERT/UPDATE/MERGE).
• Read-column attribution via a DDL catalog (CREATE/ALTER TABLE), resolved at project-build time so it stays correct as DDL changes.
• Fast native (Java) parsing backend — ~20× faster than the Python runtime.
• DB-backed catalog (query the data dictionary) for schemas whose base DDL isn't in the source tree (e.g. Flyway repos with only migrations).
• Type-based overload resolution (needs argument-expression typing); spec↔body linking.
• Cursor/variable symbol tables.
• Multi-statement install scripts; triggers, types (.tps/.tpb), dynamic SQL.
• Optional packaging as a reusable Skill in addition to the MCP server.

Credits

Grammar from antlr/grammars-v4 (Oracle PL/SQL).