Write a SQL Dialect Migration Prompt That Flags What Won't Port

Converting SQL from one dialect to another is mostly safe until it suddenly isn't. A SELECT with a JOIN ports cleanly from Oracle to PostgreSQL. An NVL call, a CONNECT BY hierarchy, or a MERGE statement does not, and the failure mode is SQL that runs on the target and returns subtly different results. A SQL dialect migration prompt exists to draw that line: convert the portable majority with confidence, flag the proprietary minority for review.

The pages ranking for this are paid converter tools and cloud-platform features. They show a static syntax-difference table and a "convert" button, but they don't reliably flag the constructs that approximate rather than translate. That silent approximation is the gap.

Why a clean conversion can still be wrong

Most SQL is standard enough to port. The optimizer hints, the proprietary functions, the vendor-specific date math, those are the minority, and they're where dialects genuinely disagree. A tool that converts NVL(x, 0) to COALESCE(x, 0) is usually right, but DECODE to a CASE expression has edge cases around null handling that change the result.

The opinion: a SQL migration's risk isn't measured in lines converted, it's measured in constructs approximated. A conversion that silently swaps a proprietary function for a near-equivalent is more dangerous than one that fails loudly, because the query still runs. A migration prompt has to tag confidence per statement, so the approximations announce themselves instead of hiding in a green test run.

What a SQL dialect migration prompt does

A SQL dialect migration prompt is a confidence-tagging converter that reads SQL in a source dialect, rewrites it for a target dialect, flags non-portable constructs, and tags each statement with how sure it is. It separates safe translations from approximations.

Run it to:

• Convert standard SQL and tag it high-confidence
• Flag proprietary functions (NVL, DECODE, LISTAGG) for review
• Rewrite hierarchical queries (CONNECT BY) as recursive CTEs
• Map sequence and auto-increment semantics across dialects
• Note date-arithmetic and null-handling behavior differences
• Lock a contract that tags each statement safe, review, or manual

The confidence tag is the field that matters. It's the difference between a conversion you can trust and one you have to read.

Anatomy of the prompt

Variables
  {{source_sql}}        → the SQL to convert
  {{source_dialect}}    → e.g. Oracle 19c
  {{target_dialect}}    → e.g. PostgreSQL 16
  {{schema_context}}    → tables/types involved (for behavior checks)

Prompt
  Role: you are a SQL dialect migration engineer.
  Task: convert {{source_sql}} from {{source_dialect}} to {{target_dialect}}.
  Rules:
    - Tag each statement high-confidence, review, or manual.
    - Flag proprietary constructs; never approximate silently.
    - Note any behavior difference in the target equivalent.

Output contract
  Return: converted SQL + per-statement confidence tag + behavior notes.

1. Name both dialects with versions

"Oracle to Postgres" is too vague; Oracle 19c to PostgreSQL 16 lets the model use the right function set and syntax. Set both in {{source_dialect}} and {{target_dialect}}.

2. Give it schema context

A behavior difference often depends on column types. Paste the relevant tables into {{schema_context}} so the model can spot a null-handling or date-math shift.

3. Read the confidence tags

High-confidence statements you can skim. Review and manual tags are your queue, the proprietary functions and hierarchical queries where the target equivalent might behave differently.

Prompt-craft patterns for dialect conversion

Tag confidence, don't just convert. The output contract's load-bearing field is the confidence tag. Standard SQL gets high-confidence; anything proprietary gets review or manual. Force the model to classify, so you know where to look.

Flag proprietary constructs, never approximate. When a function has no clean target equivalent, the model should flag it and name the closest match with its behavior difference, not silently substitute. A silent approximation that runs is the worst outcome.

Note behavior differences explicitly. Two queries can be syntactically valid and return different results across dialects, null ordering, date truncation, division. The contract should require a behavior note wherever the semantics shift.

Where Claude and ChatGPT diverge on approximation

The models differ in how they handle a construct with no clean equivalent. Claude tends to flag it, propose the target equivalent, and state the behavior difference, which is the safe path for a correctness-sensitive conversion. ChatGPT converts faster and will substitute a near-equivalent function silently unless the "flag non-portable constructs, don't approximate" rule is the last thing it reads.

Both models handle standard SQL well, so the value is in how they treat the dialect-specific minority. Null handling is a recurring trap: NVL and COALESCE differ on multi-argument behavior, and neither model warns unless asked. AWS documents that reliable database conversion needs an iterative error-correction loop rather than a single pass, which matches what these prompts produce: a first conversion plus a flagged review queue. Restate the no-approximation rule for ChatGPT on the final line; pin the model version, since a converter that began approximating proprietary functions after an update is a correctness regression you'll meet as wrong query results.

How this fits broader database migration

A dialect change is one half of a database migration; the schema change is the other. The destructive-operation safety that protects a schema migration applies whenever you touch a database. For the schema side, the safe database migration prompt flags destructive ops and stages a reversible rollout; for the API layer that sits on top, the detect breaking api changes prompt catches what breaks consumers.

Variables you'll set

Getting started

1. Copy the structure into ChatGPT, Claude, or Gemini.
2. Set both dialects with versions in the dialect variables.
3. Paste the SQL into {{source_sql}} with {{schema_context}}.
4. Run it and read the confidence tags before adopting any statement.
5. Resolve every review and manual tag against the target docs.
6. Run the converted SQL on a copy and check the flagged behaviors.

For a database migration where destructive operations and rollback are guarded across the whole change, the Database Migration Safety Harness runs the full safety pass.

A dialect migration often rides along with a language or framework upgrade. The Cross-Language Refactor Harness handles the application code that calls the database. New to buying packs versus rolling your own? Start with how to choose a reusable AI prompt pack.