Teaching an AI to read a relational database — including Oracle APEX

A few months back I started building a tool to solve a problem that kept sees the database. So I built Legacy SQL Architect MCP — an MCP server that connects Claude (or any MCP-compatible client) directly to your PostgreSQL, SQL Server, or Oracle database, in strict read-only mode. The AI inspects the real thing instead of guessing from what you paste. ## The core idea Once connected, the assistant can use a set of tools to explore the database on its own: | Tool | What it does | inspect_schema | Tables, columns, types, primary & foreign keys | data_sampler | Sample rows, with automatic masking of sensitive columns | get_procedure_source | Full source of stored procedures and functions | query_plan_expert | Execution plan analysis, full-scan detection | dependency_graph | FK chains, trigger → procedure call chains, view deps | generate_mermaid_erd | An ER diagram in Mermaid | generate_documentation | Complete Markdown docs for the whole schema | find_impact | Everything that depends on a given table | generate_java_dao | Ready-to-compile Java Entity + Repository classes | So you can say things like: "Inspect the schema, sample the orders table, read the procedures that touch it, and tell me what would break if I rename order_status." …and the assistant actually goes and looks, then answers with references to real objects. Read-only by design — it physically cannot INSERT, UPDATE, or DELETE. ## The new part: Oracle APEX This is the release I want to talk about, and I'll be honest about how it came to be. I'm not an APEX developer. I've never shipped a production APEX app. But APEX performance problems are notoriously hard to see, and it turns out Oracle exposes a lot about an APEX application through its own dictionary views — APEX_APPLICATIONS,APEX_APPLICATION_PAGE_REGIONS,APEX_APPLICATION_LOVS, APEX_WORKSPACE_ACTIVITY_LOG, APEX_DEBUG_MESSAGES, and friends. The answer to "why is this page slow?" is often sitting right there. So I studied those views and turned them into six tools that hand that insight to the AI in a form it can reason about. Every one was verified against a live APEX 26.1 instance before shipping. ### inspect_apex_performance slowest pages by real user activity, and a list of recommendations (slow pages, SELECT * regions, LOVs with no WHERE clause, unconditional processes, …). "Run inspect_apex_performance on app 102 and show me the 5 slowest pages and why." ### get_apex_source get_procedure_source: it extracts the embedded code across 12 component types (regions, processes, computations, validations, items, branches, dynamic actions, LOVs, authorizations, …). It introspects each view's columns first, so it tolerates dictionary drift between APEX versions. "Get the source of every SQL region on page 3 and summarize what they query." ### inspect_apex_debug APEX_DEBUG_MESSAGES, reconstructs page views by grouping on the page-view id, finds the slowest ones, and drills into the individual steps — so you can see where inside a page render the time actually went. "Which page views in the last 2 days were slowest, and what step dominated?" ### apex_config_audit pagination schemes that force a COUNT(*) ("row N of Z") reports with unbounded / very high max-rows frequently-used LOVs with no caching pages with too many server-side dynamic actions pages crowded with many SQL regions "Audit app 102 for configuration anti-patterns." ### apex_sql_runtime_stats V$SQL for the app's parsing schema — buffer gets, elapsed time, executions — and flags the hot SQL. "What's the most expensive SQL this app has actually run? Order by buffer gets per execution." ### apex_explain_batch EXPLAIN PLAN across an app's SQL regions, SQL LOVs, and Exists-type validations at once, then flags the red lines: TABLE ACCESS FULL, MERGE JOIN CARTESIAN, INDEX FULL SCAN. (It handles APEX &SUBSTITUTION. tokens by binding them, so the plans actually parse.) "Explain-plan all the SQL in app 102 and list anything doing a full scan or a Cartesian join." ## None of this is magic It's all sitting in Oracle's own dictionary. What the tool does is connect the dots and hand them to the AI in a form it can reason about — grounded in your actual application, not a generic "best practices" lecture. ## An honest ask Because I'm not an APEX expert, I'd genuinely value review from people who are. Am I reading the right views? Is there a metric you always check first that I'm ignoring? Did I get something wrong? You know this platform far better than I do. It's open source and free (Apache 2.0), with native installers for Windows and Linux (deb + rpm) that bundle their own Java runtime — no JDK needed. GitHub: https://github.com/tabforgeai/legacy-sql-architect-mcp If you try it against a real APEX app, I'd love to hear what it found — or where it fell short.