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

> Source: <https://dev.to/java_freepascal_dev/teaching-an-ai-to-read-a-relational-database-including-oracle-apex-4c5p>
> Published: 2026-07-22 07:49:26+00:00

A few months back I started building a tool to solve a problem that kept

annoying me: when you want an AI assistant to help with an old database, you end up as a human clipboard — copying schema, pasting stored procedures, describing foreign keys by hand. The AI never actually *sees* the database.

So I built **Legacy SQL Architect MCP** — an [MCP](https://modelcontextprotocol.io) 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`

An overview of an application: pages, regions with SQL, LOVs, validations, processes — plus the **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`

The APEX counterpart to `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`

Reads `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`

A static anti-pattern audit — no runtime data needed:

`COUNT(*)`

("row N of Z")

"Audit app 102 for configuration anti-patterns."

### `apex_sql_runtime_stats`

Correlates the app to its real runtime cost by reading `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`

Runs `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](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.