Quick Search Checklist (copy/paste into your editor)

Look for these patterns; they often need manual fixes:

  • EXECUTE IMMEDIATE · DelimList( · SQL built with '...' || ... || '...' (dynamic SQL)
  • Oracle outer join: (+ )
  • Oracle only bits: FROM dual · NVL( · DECODE( · ROWNUM · CONNECT BY|START WITH|LEVEL
  • Aggregation name differences: LISTAGG( (Oracle) ↔ string_agg( (Postgres)
  • Date/time: SYSDATE/SYSTIMESTAMP ↔ CURRENT_DATE/CURRENT_TIMESTAMP
  • In views: ORDER BY inside CREATE VIEW (don’t rely on it)


If your converted SQL suddenly returns empty strings or NULL where text was expected, check for subqueries that were nested inside function calls (see Gotcha #2).


Gotcha #1 — Dynamic SQL (queries built as strings)

Problem: Converters can’t rewrite Oracle‑only syntax hidden inside string literals. The SQL runs later as‑is and fails in Postgres.


Oracle (dynamic, fragile):

SELECT ,
DelimList(
'SELECT d_origin.origin_desc ' ||
'FROM taxon_subnatl_dist s_dist, d_origin ' ||
'WHERE s_dist.d_origin_id = d_origin.d_origin_id (+) ' ||
'AND s_dist.element_subnational_id = ' || est.element_subnational_id, ','
) AS origin_list
FROM element_subnational est;


PostgreSQL (static & safe):

SELECT
est.element_subnational_id,
string_agg(d_origin.origin_desc, ',' ORDER BY d_origin.origin_desc) AS origin_list
FROM element_subnational est
LEFT JOIN taxon_subnatl_dist s_dist
ON s_dist.element_subnational_id = est.element_subnational_id
LEFT JOIN d_origin
ON s_dist.d_origin_id = d_origin.d_origin_id
GROUP BY est.element_subnational_id;

Fix pattern: Replace dynamic string‑built SQL with a normal SELECT + LEFT JOINs and string_agg.

If you must keep dynamic SQL, manually rewrite contents: (+) → LEFT JOIN, remove FROM dual, NVL → COALESCE, etc.


Gotcha #2 — Subqueries inside function calls

Problem: Converters sometimes drop or neuter logic when subqueries are nested in NVL/COALESCE, CONCAT, or ||.


Oracle (original):

SELECT eo.eo_id, NVL(
(SELECT 'Princ' FROM eo eo1 WHERE eo1.principal_eo_shape_id = eo.shape_id
GROUP BY eo1.principal_eo_shape_id)
||
(SELECT 'Sub' FROM eo eo1 WHERE eo1.eo_id = eo.eo_id
AND eo1.principal_eo_shape_id IS NOT NULL)
, '') AS princ_sub
FROM eo;


Bad converted (loses logic):

SELECT eo.eo_id, COALESCE(CONCAT(''), NULL) AS princ_sub FROM eo;


Portable rewrite (clear intent):

SELECT
eo.eo_id,
NULLIF(
COALESCE(
CASE WHEN EXISTS (
SELECT 1 FROM eo eo1
WHERE eo1.principal_eo_shape_id = eo.shape_id
) THEN 'Princ' END, ''
) ||
COALESCE(
CASE WHEN EXISTS (
SELECT 1 FROM eo eo1
WHERE eo1.eo_id = eo.eo_id
AND eo1.principal_eo_shape_id IS NOT NULL
) THEN 'Sub' END, ''
),
''
) AS princ_sub
FROM eo;


Fix pattern: Replace “subquery returns a constant just to concatenate it” with CASE WHEN EXISTS (...) THEN 'text' END.


Gotcha #3 — Oracle outer join (+) → ANSI LEFT JOIN

Oracle:

... WHERE a.id = b.id (+)

PostgreSQL / ANSI:

... FROM a LEFT JOIN b ON a.id = b.id

Fix pattern: Move join conditions from WHERE into JOIN ... ON and choose LEFT/RIGHT/FULL as needed.


Gotcha #4 — Common keyword swaps

  • NVL(x,y) → COALESCE(x,y)
  • DECODE(expr, a, b, c, d, else) → CASE WHEN expr=a THEN b WHEN expr=c THEN d ELSE else END
  • FROM dual → (remove) just SELECT ...
  • LISTAGG(col, ',') → string_agg(col, ',')
  • SYSDATE / SYSTIMESTAMP → CURRENT_DATE / CURRENT_TIMESTAMP
  • ROWNUM <= N → ... ORDER BY ... LIMIT N

Views: don’t rely on ORDER

  • Avoid ORDER BY inside CREATE VIEW. Always sort at query time:
     SELECT ... FROM my_view ORDER BY phylum_seq, class_seq;

Copy/Paste Fix Patterns (mini‑reference)

  • Outer joins: = b.id (+) → LEFT JOIN b ON a.id = b.id
  • Null handling: NVL(x,y) → COALESCE(x,y)
  • Concat flags: subqueries → CASE WHEN EXISTS (subquery) THEN 'Flag' END
  • Comma lists: LISTAGG/DelimList → string_agg with LEFT JOIN + GROUP BY
  • Dynamic SQL: prefer static SELECTs; if kept, rewrite the string contents for Postgres syntax.

When in doubt

If a converted query:

  • Suddenly returns empty strings only
  • Fails with syntax near (+) or dual
  • Uses lots of '||' around SQL text

…it likely needs a manual rewrite using the patterns above.


Copy/Paste Prompt — Oracle → PostgreSQL Conversion Assistant

Use this prompt to analyze a single Oracle query and get either a cross‑compatible rewrite or a PostgreSQL‑only alternative when needed.

Prompt:

You are an expert in SQL and database migrations. I will give you a single Oracle SQL query.

Your task is to:

Determine whether the query can be rewritten in a way that is compatible with both Oracle and PostgreSQL.

If it can be rewritten compatibly, provide that version.

If it cannot, clearly list the Oracle-specific features that prevent compatibility, and then provide a PostgreSQL-compatible alternative.

Format your response in the following structure:

Compatibility: [Yes / No]

Reason(s) Incompatible (if applicable):

[List each Oracle-specific feature that prevents cross-compatibility]

Cross-Compatible Query (if possible):

sql
Copy
Edit
-- SQL that runs in both Oracle and PostgreSQL
-- Replace this with the compatible version

PostgreSQL-Only Alternative (if cross-compatible version is not possible):

sql
Copy
Edit
-- PostgreSQL-specific version of the query

Please analyze the following Oracle query:

sql
Copy
Edit
-- Paste the Oracle SQL query here