How EXPLAIN PLAN Catches AI-Generated SQL Before It Runs

An engineer running AI agents against a 2.3-million-line Oracle system found their most common failure wasn't bad logic — it was SQL referencing tables and columns that don't exist. The fix was a decades-old Oracle command that never executes anything: EXPLAIN PLAN.

The failure mode: names that almost exist

A language model doesn't know your schema — it knows what schemas usually look like. On a 20-year-old database with thousands of tables, that produces plausible-but-wrong names: a column called POLICY_STATUS when the real one is STATUS_CD, a table named CUSTOMERS when it's been CUSTOMER since 1998. These errors pass a human read-through because the naming convention matches — they only surface when the script actually runs, and in this case, fix scripts run at more than 20 customer installations.

Why "just run it" isn't an option

The scripts in question are UPDATEs and DELETEs against production-like data, so executing them to check is off the table — even wrapped in a rollback, that fires triggers, takes locks, and burns sequence numbers. EXPLAIN PLAN solves this because it parses a statement against the live data dictionary, checks privileges, and builds an execution plan — without changing a single row, firing a trigger, or holding a lock.

What it catches, in milliseconds

If the model invented a table, EXPLAIN PLAN returns ORA-00942: table or view does not exist. If it invented a column, it's ORA-00904: invalid identifier. In the described workflow, every generated script runs through this check before a human ever sees it, and the error goes straight back to the model with the original task — most name-level failures disappear in one retry. The same gate also throws off an execution plan as a side effect, which is how the engineer caught a full table scan on a 41-million-row table before it ran.

What it does not catch

A green result from EXPLAIN PLAN isn't a correct script — a statement with valid names and wrong logic (deleting the wrong rows, say) parses just fine. It also only checks single SQL statements, not PL/SQL packages or procedures, and it validates against whichever schema you point it at, so a customer installation running an older schema version can still disagree.

What to watch

The same pattern works outside Oracle — PostgreSQL supports it via PREPARE or plain EXPLAIN, and SQL Server has SET PARSEONLY ON. Any team running LLM-generated SQL against a real schema already has the tool to catch invented names for free; it just isn't wired into the agent loop yet.