DEV Community

Cover image for EXPLAIN PLAN as a Lint for LLM-Generated SQL
Nunc
Nunc

Posted on • Originally published at hellonunc.com

EXPLAIN PLAN as a Lint for LLM-Generated SQL

#oracle #database #ai #sql

My AI agents write Oracle SQL all day: fix scripts, diagnostics, one-off reports for a 2.3M-line legacy system. Their most common failure isn't bad logic. It is SQL that references a table or column that almost exists. Oracle has had the fix for decades, it costs one statement per query, and it 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. So on a 20-year-old database with thousands of tables, it produces names that are plausible instead of real: POLICY_STATUS when the column is STATUS_CD, CUSTOMERS when the table has been CUSTOMER (singular) since 1998, a join through a link table that was dropped two versions ago.

Comparison of what the model wrote versus what the schema has: POLICY_STATUS versus STATUS_CD (plausible name, wrong name), CUSTOMERS versus CUSTOMER (singular since 1998), and a join through POLICY_CUST_LINK, a table dropped two versions ago; a human reviewer skims this and it reads fine because the naming convention matches

These are the worst kind of errors, because they look right. A human reviewer skims the script, the naming convention matches, everything reads fine. The error only surfaces when the script runs, and in my case fix scripts run at more than 20 customer installations. That is exactly the place where you do not want to discover an invented column.

Why you cannot just run it to check

The obvious test, execute it and see, is not available. These scripts are UPDATEs and DELETEs against production-like data. Wrapping everything in a transaction and rolling back sort of works, but it fires triggers, takes locks, burns sequence numbers and takes time on big tables.

I wanted a check that touches nothing and still uses the real schema. It already exists.

EXPLAIN PLAN parses without executing 

EXPLAIN PLAN FOR
UPDATE policy
   SET status_cd = 'ACTIVE'
 WHERE policy_id = :b1;
Enter fullscreen mode Exit fullscreen mode

Oracle takes the statement through the full parse: it resolves every table and column against the live data dictionary, checks your privileges, builds an execution plan and writes it to PLAN_TABLE. What it never does is execute. No rows change, no triggers fire, no locks are held. It is safe to run for an UPDATE, a DELETE, a MERGE.

If the model invented a table, you get an answer in milliseconds:

ORA-00942: table or view does not exist
Enter fullscreen mode Exit fullscreen mode

If it invented a column:

ORA-00904: "POLICY_STATUS": invalid identifier
Enter fullscreen mode Exit fullscreen mode

That is a lint result, produced by the one parser that actually knows the schema: the database's own.

Wiring it into the agent loop

In my workflow every generated script goes through this gate before a human sees it, statement by statement, against the dev database:

for stmt in split_statements(script):
    try:
        cursor.execute(f"EXPLAIN PLAN FOR {stmt}")
    except oracledb.DatabaseError as e:
        errors.append((stmt, str(e)))
Enter fullscreen mode Exit fullscreen mode

Diagram of the EXPLAIN PLAN gate: an agent writes SQL fix scripts statement by statement, each statement goes through an EXPLAIN PLAN gate that parses it against the live dev schema with nothing executed; when it parses OK the script moves on to human review with every identifier real, and on errors like ORA-00942 table or view does not exist or ORA-00904 invalid identifier the error goes back to the model, which retries; a side effect is that the execution plan is already available via DBMS_XPLAN.DISPLAY

The errors go straight back to the model with the original task. Most name-level failures disappear in one retry, without me reading anything. What reaches me is a script whose every identifier is real.

The tool the agents call runs in a read-only safe mode: SELECT, DESCRIBE and EXPLAIN PLAN are allowed, everything else (DDL, DML, GRANT, COMMIT) is blocked before it reaches the database. So even a badly confused agent can't turn the lint step into a write.

The free bonus: you also get the plan

The check produces an execution plan as a side effect:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Enter fullscreen mode Exit fullscreen mode

Terminal output of EXPLAIN PLAN FOR an UPDATE on the policy table followed by SELECT FROM TABLE of DBMS_XPLAN.DISPLAY: the plan shows TABLE ACCESS FULL on POLICY with 41 million rows highlighted, because there is no index on REGION_ID, and nothing was executed

So the same gate that catches invented columns also shows you the full table scan on a 41-million-row table before anything runs. Two problems, one statement.

What it does not catch

A green lint is not a correct script. Here is what this gate misses:

  • Valid names, wrong logic. DELETE FROM policy WHERE status_cd = 'A' parses perfectly and can still delete the wrong rows. This gate replaces nothing at the review level.
  • PL/SQL blocks. EXPLAIN PLAN takes single SQL statements. For packages and procedures I compile against a scratch schema instead; that is a different gate.
  • Data assumptions. The parser checks that a column exists, not that 'A' is a value that ever appears in it.
  • Schema drift. I parse against the dev schema. A customer installation two versions behind can still disagree. Closest schema wins, not a guarantee.

The same idea works outside Oracle: PostgreSQL parses and plans with PREPARE or plain EXPLAIN, SQL Server has SET PARSEONLY ON. Any database that can plan a statement without running it can lint one.

Wrapping up

LLM SQL fails most often at the name level, and name resolution is exactly what the database parser already does. EXPLAIN PLAN turns that parser into a lint step: every generated statement checked against the live schema in milliseconds, with nothing executed and an error message the model can act on. Of all the guardrails around my agents, this one has the best ratio of effort to failures caught. It was one afternoon of work.

Top comments (0)