The only tool that parses Oracle PL/SQL with a real grammar and transpiles it to PostgreSQL – not with regex band-aids, but with an ANTLR4 parser that understands what it’s reading.
The Problem Nobody Talks About
Oracle-to-PostgreSQL migration has a dirty secret: everyone focuses on schema and data migration, but the hard part is the PL/SQL code.
There are tools that’ll move your tables, indexes, and constraints. There are tools that’ll COPY your data across. There are even PostgreSQL extensions that give you Oracle-compatible functions.
But none of these touch your stored procedures, functions, packages, and triggers – your business logic locked inside thousands of lines of Oracle PL/SQL.
If you’re migrating a serious Oracle application to PostgreSQL, your codebase likely includes:
– Hundreds of stored procedures and functions
– Complex packages with spec/body separation and package variables
– Nested exception handlers with custom PRAGMA EXCEPTION_INIT mappings
– Cursor FOR loops, BULK COLLECT, FORALL statements
– Oracle-specific function calls: NVL, DECODE, SYSDATE, TO_CHAR with Oracle format masks
– Dynamic SQL via EXECUTE IMMEDIATE
– XML manipulation via XMLTYPE, XMLTABLE, UPDATEXML, EXISTSNODE
– MERGE statements, hierarchical CONNECT BY queries
– SQL*Plus scripting constructs: DEFINE, SET VERIFY, substitution variables
You can’t regex your way through this. A 15-year-old codebase with real-world PL/SQL has edge cases that will defeat any pattern-matching approach on the first page.
The Old Way: Regex-and-Pray
The other tools for Oracle-to-PostgreSQL code conversion fall into two categories:
Regex-based converters scan your PL/SQL source for known patterns and replace them with PostgreSQL equivalents. This works for the first 80% – the basic function renames (NVL → COALESCE, SYSDATE → now()), the obvious syntax changes. But the remaining 20% is where production bugs live:
– A nested block with exception handlers inside a cursor loop? The regex misses the closing `END` and your function body is silently truncated.
– A package variable referenced across spec and body files? Regex can’t track state between multiple files.
– `EXECUTE IMMEDIATE ‘DELETE FROM employees WHERE id = :1’ USING v_id;` with a RETURNING clause? The regex puts RETURNING after the closing quote.
– A comment that happens to contain the word `RAISE EXCEPTION`? The regex fires on it and corrupts your output.
The operators of these tools know this. Their workflow is: convert → manually fix the 300+ syntax errors → test → fix more → ship late. The cost of the “conversion tool” is dwarfed by the cost of the manual cleanup.
Schema migration tools like ora2pg are excellent at what they do – extracting DDL from a live Oracle database. But their PL/SQL conversion is a secondary feature, not the core competency. They are implemented with Perl regex patterns. They have no grammar, no parser, no AST – they are functionally equivalent to SED `s/pattern/replacement/g` on steroids. It works for simple cases and fails silently on anything complex.
What plsql2pgsql Does Differently
plsql2pgsql uses an ANTLR4 grammar to parse Oracle PL/SQL as a real programming language, not as a text-matching problem.
The Pipeline
Oracle PL/SQL Source
→ Pre-processing (SQL*Plus commands, type mappings, function conversions)
→ ANTLR4 PL/SQL Parser (full grammar, SLL→LL fallback)
→ Tree walk + visitor (converts Oracle AST → PostgreSQL text)
→ Post-processing (exception handlers, collection types, helpers)
→ PostgreSQL PL/pgSQL (or PL/Python3u)
When the parser encounters something it can’t handle in fast SLL mode, it falls back to LL mode, trying harder before giving up. When the ANTLR grammar simply can’t represent a construct (like comparison operators inside EXCEPTION handlers), a targeted pre-processing regex rewrites the problematic pattern into something the parser CAN handle. This cascading approach means the grammar handles real-world code, not just textbook examples.
What This Means in Practice
Packages work. Spec and body are processed independently, but package variables are tracked via auto-generated GUC (Grand Unified Configuration) getter/setter functions. Cross-file references resolve correctly. You don’t get broken output because the package body references a variable the converter didn’t know about.
Exception handling works. Custom exceptions via `PRAGMA EXCEPTION_INIT(e, -20001)` are mapped to PostgreSQL SQLSTATE codes (`U0001`). `RAISE e` becomes `RAISE EXCEPTION ‘e’ USING ERRCODE = ‘U0001’`. `WHEN e THEN` becomes `WHEN SQLSTATE ‘U0001’ THEN`. Every code path is covered: the ANTLR visitor, the post-processing safety net for the body-loss fallback, and the skip-list that prevents false matches on PG keywords like `RAISE NOTICE`.
Collections work. `TYPE t IS TABLE OF NUMBER INDEX BY PLS_INTEGER` becomes a native PostgreSQL array. `t(a, b, c)` constructor calls become `ARRAY[a, b, c]`. Access via `v(1)` becomes `v[1]`. No manual conversion needed.
XML functions work. All 25+ Oracle XML functions (XMLTABLE, XMLCAST, UPDATEXML, DELETEXML, INSERTCHILDXML, XMLQUERY, EXISTSNODE, EXTRACTVALUE, etc.) have PostgreSQL equivalents — implemented as PL/pgSQL helper functions or using PG’s built-in XML support. None remain as TODOs.
Complex Oracle-specific functions work. E.g.: REGEXP_SUBSTR, MONTHS_BETWEEN, NEXT_DAY, SCN_TO_TIMESTAMP – all have PG equivalents. Not TODOs. Not “manually review” placeholders. Working code.
By the Numbers
Two Full ANTLR4 grammars (PLSQL and DDL)
Output targets: PL/pgSQL + PL/Python3u |
Five Supported platforms (darwin amd64/arm64, linux amd64/arm64, windows amd64)
Runtime dependencies: Zero
Desktop apps: macOS native SwiftUI, other platforms to follow
Lines of Go: ~15,000
Why This Matters Now
PostgreSQL adoption is accelerating – DB-Engines ranks it as the fastest-growing major database year over year. Oracle licenses are expensive and Oracle’s cloud lock-in is deepening. Every company with an Oracle codebase is facing the same question: how do we get out?
Disclaimer
ALL TRADEMARKS BELONG TO THEIR OWNERS.