Oracle → PostgreSQL: A Worked Example

Oracle is the richest source dialect Swordfish handles (238 rules, more than any other database) because Oracle has accumulated decades of proprietary syntax and behavior that has no direct PostgreSQL equivalent. This post walks through the constructs you’ll hit most often, with the actual rule that fires, the recommended change for both community PostgreSQL and EDB Postgres Advanced Server (EPAS), and the review step that keeps a human in the loop.

Each example is something the assessment surfaces from real code. You can reproduce these against the sample Oracle app baked into the Docker image (EnterpriseDB/swordfish-sample-apps): cd ops && docker compose up -d, create a project with source type oracle, point it at /srv/test-apps, and run a scan.

A note on the target. Many Oracle-isms below are natively supported by EPAS but not by community PostgreSQL. Swordfish carries a separate recommendation for each target (pg_fix vs epas_fix), so a finding that’s a rewrite on community PostgreSQL may be a no-op on EPAS. Pick your target before you estimate effort — it materially changes the work.

---

1. Hierarchical queries: CONNECT BY

Finding: ORA-001: CONNECT BY Hierarchical Query (high severity, category sql_syntax).

Oracle’s hierarchical query syntax:

SELECT employee_id, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

PostgreSQL recommendation: rewrite as a recursive CTE:

WITH RECURSIVE org AS (
    SELECT employee_id, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
  UNION ALL
    SELECT e.employee_id, e.manager_id, o.level + 1
    FROM employees e
    JOIN org o ON e.manager_id = o.employee_id
)
SELECT employee_id, manager_id, level FROM org;

EPAS recommendation: none needed; EPAS supports CONNECT BY natively.

Review note: confirm the original query used CONNECT BY features that map cleanly. CONNECT BY with NOCYCLE, CONNECT_BY_ROOT, or SYS_CONNECT_BY_PATH needs extra handling in the CTE; the recursive form above covers the common parent/child walk only.

---

2. Row limiting: ROWNUM

Finding: ORA-002: ROWNUM Pseudo-column (high severity).

The classic Oracle pagination triple-nest:

SELECT * FROM (
  SELECT a.*, ROWNUM rn FROM (
    SELECT * FROM orders ORDER BY created_at
  ) a WHERE ROWNUM <= 20
) WHERE rn > 10;

PostgreSQL recommendation:

SELECT * FROM orders ORDER BY created_at LIMIT 10 OFFSET 10;

Review note: this is also a behavioral check, not just syntax. ROWNUM is assigned before ORDER BY unless nested (as above). If you find bare WHERE ROWNUM <= n without an inner ordered subquery, the original results were effectively unordered — confirm whether the application actually depended on a specific order before you add one.

---

3. NULL handling: NVL, NVL2, DECODE

Findings: ORA-004: NVL Function, ORA-005: NVL2 Function, ORA-003: DECODE Function (medium severity, category sql_syntax).

NVL(notes, 'n/a')
NVL2(commission, commission, 0)
DECODE(status, 1, 'Active', 2, 'Closed', 'Unknown')

PostgreSQL recommendation:

COALESCE(notes, 'n/a')
CASE WHEN commission IS NOT NULL THEN commission ELSE 0 END   -- or COALESCE(commission, 0)
CASE status WHEN 1 THEN 'Active' WHEN 2 THEN 'Closed' ELSE 'Unknown' END

EPAS recommendation: none needed; NVL, NVL2, and DECODE are supported natively.

Review note: these are mechanical and low-risk on their own. The risk is when they’re combined with the empty-string trap below — e.g. NVL(comment, 'none') that was silently catching empty strings in Oracle because Oracle stored them as NULL. Flag those for the section 7 review.

---

4. Outer joins: the (+) operator

Finding: ORA-006: Oracle Outer Join (+) Syntax (high severity).

SELECT e.name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.dept_id(+);

PostgreSQL recommendation: ANSI join syntax:

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

Review note: (+) on the wrong side, or mixed with regular predicates in the same WHERE, can change which table is preserved. Verify the join direction (LEFT vs RIGHT) against the original intent — this is a common place to silently flip an outer join into an inner one.

---

5. DUAL and SYSDATE

Findings: ORA-007: SELECT FROM DUAL, ORA-034: SYSDATE Function.

SELECT SYSDATE FROM dual;

PostgreSQL recommendation:

SELECT CURRENT_TIMESTAMP;   -- PostgreSQL has no DUAL; SELECT needs no FROM

EPAS recommendation: DUAL and SYSDATE are available in EPAS; no rewrite required.

Review note: SYSDATE and CURRENT_TIMESTAMP are not perfectly interchangeable. SYSDATE returns the database server’s local date-and-time with no timezone; CURRENT_TIMESTAMP is timezone-aware (now() / LOCALTIMESTAMP are alternatives). If the application does timezone math or stores the result in a TIMESTAMP WITH TIME ZONE, decide deliberately which Postgres function matches the original behavior.

---

6. Sequences

Finding: Oracle sequence usage (*.NEXTVAL / *.CURRVAL), category sql_syntax.

INSERT INTO orders (id, total) VALUES (orders_seq.NEXTVAL, 42.00);

PostgreSQL recommendation: two options, depending on how far you want to modernize:

-- Minimal change: keep explicit sequences
INSERT INTO orders (id, total) VALUES (nextval('orders_seq'), 42.00);

-- Modernized: let the column generate it
-- DDL:  id bigint GENERATED BY DEFAULT AS IDENTITY
INSERT INTO orders (total) VALUES (42.00);

Review note: watch the allocation/cache settings. Oracle sequences with a large CACHE (and ORM allocation sizes, e.g. Hibernate’s default of 50) can leave gaps and assume a specific increment. If any code depends on sequence values being gap-free or contiguous, that assumption does not survive — flag it.

---

7. The one that bites: empty string = NULL

Finding: ORA-BEH-002: Empty String Treated as NULL (Oracle vs PostgreSQL Mismatch) (critical, category behavioral).

This is the most important finding in any Oracle migration, and it produces no syntax error. Oracle treats the empty string '' as NULL. PostgreSQL does not — in Postgres, '' is a real, distinct empty string.

-- In Oracle, this never matches any row, because '' IS NULL:
SELECT * FROM customers WHERE middle_name = '';

-- In PostgreSQL, the same query matches rows whose middle_name is exactly ''.

Every place the application inserted '' expecting it to become NULL, compared against '', or used NVL/IS NULL checks that leaned on this behavior, now behaves differently. The query compiles. The tests (probably) pass. The data drifts.

Recommendation: there is no automatic fix, because the correct behavior depends on intent. Swordfish flags every site and routes it for human review. For each one, decide: did this code mean “is empty,” or did it mean “is null/absent”? Then make it explicit (IS NULL, = '', or COALESCE(NULLIF(col, ''), ...) as appropriate) rather than relying on engine behavior.

Review note: this is the canonical example of why the tool assesses rather than auto-converts. A coding agent handed WHERE middle_name = '' will faithfully port it and move on, having silently changed your results.

---

From finding to reviewed change

The per-construct fixes above are the what. Here’s the workflow that turns the assessment into merged code without losing control:

1. Scan the project (rules + extraction + optional LLM tiers). You get findings grouped by severity, file, and category, with effort estimates.
2. Review and edit. Open a finding, read the recommendation, and adjust it. The ORA-BEH-002 empty-string sites are where you’ll spend real judgment; the NVL/DUAL/(+) sites are mostly confirm-and-move-on.
3. Pick your target. If you’re going to EPAS, filter out the natively-supported constructs (CONNECT BY, NVL, DECODE, DUAL, ROWNUM, (+)) — they’re no-ops there, and your scope just shrank.
4. Hand off. Export a copilot prompt pack for the rewrites, or run a rewrite in one of the execution modes. Output lands in .new sibling files — your source is never touched.
5. Diff and test. Review every .new against the original, merge what’s correct, and run the rewritten code through your suite — paying special attention to the behavioral findings, which are exactly the ones your existing tests probably don’t cover.

That’s the loop. Swordfish tells you precisely what Oracle is doing that PostgreSQL won’t, recommends the change, and lets your tools and your judgment do the rest. The next post in this series runs the same playbook for MySQL.

---

Reproduce this walkthrough against the bundled sample apps: github.com/EnterpriseDB/swordfish-sample-apps. Swordfish itself (Apache-2.0): github.com/EnterpriseDB/swordfish-migrations