Two Prompt Rules Take a Lightweight Model from 35% to 64% on Oracle Text-to-SQL

Introduction: An Underrated Benchmark

Text-to-SQL has long been a hot direction for deploying LLMs in enterprise scenarios, but differences between database dialects are often overlooked. Recently, a new benchmark called UniQL translated 1,534 questions from the BIRD dataset into 16 SQL dialects, and the Oracle column's results were particularly dismal: the best published result was only 49.61% (Claude Sonnet 4.5), while Gemini 2.5 Pro scored just 26.14%.

A developer tried a lightweight model, Gemini 2.5 Flash, paired with the open-source tool schemagate, and the result was unexpected—by simply adding two Oracle-specific rules to the prompt, first-attempt accuracy jumped from 35.0% to 64.5%, nearly 15 percentage points above the best public score. The story behind this deserves a close read by every team deploying AI applications.

Text-to-SQL and the BIRD Dataset: The Text-to-SQL task requires models to convert natural language questions into executable SQL queries. BIRD (BIg Bench for LaRge-scale Database Grounded Text-to-SQL Evaluation) is currently one of the most challenging cross-domain Text-to-SQL benchmarks, containing numerous real databases and domain-specific questions, emphasizing models' understanding of database values, external knowledge, and SQL dialects. UniQL chose BIRD as the source dataset because its questions are diverse and close to enterprise scenarios; after translation into multiple dialects, it enables fair comparison of models' generalization capabilities across different databases.

SQL Dialect Differences: Although SQL has an ANSI standard, mainstream databases such as Oracle, MySQL, and PostgreSQL differ significantly in identifier quoting, reserved words, pagination syntax, and date functions. For example, Oracle prohibits the AS keyword before table aliases and strictly restricts the use of words like DATE and NUMBER as column names. These details are "long-tail knowledge" for models, yet they directly determine whether the generated SQL can execute, making dialect adaptation a key bottleneck for Text-to-SQL deployment.

First Run: The 69.9% Illusion

The developer initially tested on Oracle Free 23ai using UniQL's built-in evaluator. The schemagate + Gemini 2.5 Flash combination achieved only 35.0% accuracy on the first attempt, but after adding an "error repair" loop, the score soared to 69.9%. This number looks great but hides a statistical trap.

Error repair means letting the model first run the generated SQL; if Oracle throws an error, the error message is fed back to the model for a retry. This loop does improve final usability, but it is inconsistent with the "single attempt" scoring standard used by other models on the UniQL leaderboard. The developer candidly admitted: "69.9% is mainly due to the repair loop, not the model's own capability." Looking at first attempts alone, Flash's 35% is even below the bare model's baseline.

This kind of "unfair comparison" is not uncommon in AI evaluation. Many teams like to showcase the best results after multiple retries while ignoring the importance of aligning metrics with peers. As the original article says: "Measure before tuning, and report the number that is comparable to others."

Error Repair Loop: This is a common engineering technique: after the model generates SQL, it is first executed on the target database; if an error occurs, the error message is fed back as a prompt for the model to generate a corrected version, which can iterate multiple times. It can significantly improve final usability but changes the evaluation conditions—leaderboards typically use "single attempt" (pass@1) as the standard, while the repair loop is equivalent to multiple sampling or self-correction. Mixing repaired results with single-attempt results overestimates model capability and misleads technical selection. Therefore, a rigorous approach is to clearly distinguish "first-attempt accuracy" from "post-repair accuracy" and use only the former when comparing with public leaderboards.

Two Rules Solve Ninety Percent of Failures

The real breakthrough came from attribution analysis of failure cases. Among the 694 queries that failed on first attempt, two error patterns covered almost all of them:

• 551 queries were written as FROM account AS a—Oracle does not allow the AS keyword before table aliases.
• 100 queries used columns named DATE or NUMBER without quotes—these two words are reserved in Oracle.

These two issues have nothing to do with model reasoning capability; they are purely dialect grammar details. schemagate's solution is straightforward: when the target database is Oracle, explicitly tell the model these two rules in the prompt. Prompts for other databases remain unchanged to avoid unnecessary interference.

The effect was immediate: first-attempt accuracy rose from 35.0% to 64.5%, and failed queries plummeted from 694 to 85. A lightweight model thus surpassed the public scores of heavyweight competitors. This reminds us that in vertical-domain AI applications, explicit injection of domain knowledge is often more important than model size.

Reserved Words and Identifier Quoting: In Oracle, words such as DATE, NUMBER, LEVEL, and SIZE are reserved words or keywords; if used directly as column or table names without double quotes, the parser treats them as syntax elements and throws an error. Standard SQL allows identifiers to be wrapped in double quotes to distinguish them from keywords, but models often overlook this. Similarly, adding AS before table aliases is legal in most databases (such as PostgreSQL and MySQL), but Oracle explicitly prohibits it. These rules are "hard constraints" of database dialects, unrelated to semantic reasoning, yet they can account for an extremely high proportion of failure cases. Explicitly injecting such rules into prompts is equivalent to providing the model with a lightweight dialect constraint checklist—low cost and quick results.

Implications for AI Application Development

This story offers several practical lessons for Text-to-SQL and broader AI application deployment.

Evaluation Metrics Must Be Aligned

The error repair loop is a reasonable engineering design, but it must not be mixed into the single-attempt evaluation column. The developer proactively reported the post-repair 67.6% separately from the first-attempt 64.5% and submitted to the UniQL leaderboard (issue #2); this rigor is worth learning from.

Failure Analysis Is More Valuable Than Blind Parameter Tuning

Rather than repeatedly adjusting parameters like temperature and top-p, it is better to first see where the model actually goes wrong. Among the 694 failure cases, 651 (94%) stemmed from two grammar rules; this concentration shows that targeted prompt engineering can bring order-of-magnitude improvements.

Lightweight Models + Domain Rules Is a Viable Path

Gemini 2.5 Flash costs far less than Sonnet 4.5, but by injecting Oracle dialect rules, it achieved an upset on a specific task. For enterprises with limited budgets, this offers a pragmatic technical route.

Limitations and Outlook

A note: this test only targeted the Oracle dialect, and the two rules were distilled from manual analysis of failure cases. For other dialects such as MySQL and PostgreSQL, whether similar "high-frequency error patterns" exist requires separate attribution analysis. In addition, 64.5% first-attempt accuracy is still far from production-ready; the error repair loop remains indispensable in actual deployment.

The complete prediction results, evaluator outputs, and reproducible workflow are publicly available in the schemagate repository; interested readers can verify for themselves.

One-line Summary

Two Oracle dialect rules raised a lightweight model's first-attempt accuracy from 35% to 64.5%, surpassing the best public score.

Paragraph Summary

The article describes a Text-to-SQL optimization case on the Oracle dialect. A developer used the lightweight model Gemini 2.5 Flash with the schemagate tool; in the UniQL benchmark's Oracle test, first-attempt accuracy was only 35.0%. By analyzing 694 failed queries, they found that 551 failed due to misuse of AS before table aliases and 100 due to unquoted reserved-word column names like DATE/NUMBER; two Oracle-specific rules solved 94% of failures. After adding these two rules to the prompt, first-attempt accuracy jumped to 64.5%, exceeding the published best of 49.61%. The article emphasizes that evaluation metrics must be aligned, failure analysis is more valuable than blind parameter tuning, and lightweight models plus domain rules are a viable path.