PLSQL2PGSQL

 PLSQL2PGSQL — Oracle-to-PostgreSQL Transpiler for macOS, Linux, Windows (macOS native app shown)

Overview

PLSQL2PGSQL is a cross‑platform Oracle PL/SQL transpiler that parses source files using ANTLR4 and emits semantically equivalent PostgreSQL PL/pgSQL (or PL/Python3u). It handles a broad range of Oracle constructs — functions, procedures, packages, triggers, types, collections, bulk operations, dynamic SQL, exception handling, transaction control, and DDL — across both single‑file and batch workflows.

Available as a lightweight CLI, a cross‑platform Wails desktop GUI, a native macOS SwiftUI app, a Windows WPF .NET app, and Linux .deb / .rpm packages.

🖥 macOS native app Linux binary + .deb/.rpm Windows WPF app + installer CLI for scripting / CI 

Parser Architecture

• ANTLR4‑based parsing using the Oracle PL/SQL grammar targeting the Go runtime, actually one grammar for PLSQL and one grammar for DDL
• Dual prediction mode: SLL (fast‑path) for typical constructs, with automatic fallback to LL (full context) when SLL produces degraded output — detected via heuristics on dollar‑quoted body completeness, missing END;, unconverted Oracle keywords, and bare‑identifier IF conditions
• Error recovery: token recognition errors and syntax errors are captured per‑file and displayed in the UI, clearly marking files that need manual review
• Regex pre‑/post‑processing for constructs the grammar cannot parse directly — SQL*Plus, GRANT/REVOKE, CONNECT BY, RETURN arithmetic, and body‑loss fallback
• Parser‑level SQL*Plus handling: SET, SPOOL, PROMPT, DEFINE, UNDEFINE, COLUMN, BREAK, COMPUTE, 
• REPHEADER, REPFOOTER, ACCEPT, EXECUTE, START, 
• @, @@, / (slash execute), WHENEVER 
• — all converted at the pre‑parser level before ANTLR invocation

Language Translations

Data Type Mappings

• VARCHAR2(n) → VARCHAR(n)
• NUMBER(p,s) → NUMERIC(p,s); bare NUMBER → NUMERIC
• DATE → DATE
• CLOB → TEXT
• BLOB → BYTEA
• PLS_INTEGER, BINARY_INTEGER → INTEGER
• RAW(n) → BYTEA
• TIMESTAMP WITH TIME ZONE → TIMESTAMPTZ
• %TYPE, %ROWTYPE → resolved to the target column’s PostgreSQL type at transpile time (default: TEXT / RECORD)

Functions & Control Flow

• NVL → COALESCE
• DECODE → CASE
• LISTAGG → STRING_AGG
• INSTR → STRPOS
• SYSDATE → now()
• ADD_MONTHS, MONTHS_BETWEEN, NEXT_DAY, LAST_DAY — date arithmetic equivalents
• TO_CHAR / TO_DATE / TO_NUMBER — type cast conversions with format‑model stripping where a PostgreSQL equivalent exists
• NVL2, NULLIF, COALESCE, SYS_GUID, UID, USER, USERENV, RAISE_APPLICATION_ERROR
• DETERMINISTIC → IMMUTABLE
• PIPELINED → RETURNS TABLE
• IF/ELSIF/ELSE, CASE, all loop types (FOR, WHILE, LOOP, FORALL), EXIT, CONTINUE, GOTO
• CONNECT BY PRIOR → WITH RECURSIVE CTE
• CONNECT BY LEVEL → generate_series()

Packages

• Package specs decomposed into individual function/procedure stubs
• Package bodies decomposed into standalone CREATE FUNCTION / CREATE PROCEDURE
• Package variables mapped to GUC getter/setter pairs (set_config / current_setting)
• TYPE BODY member functions → standalone functions with MEMBER→self parameter mapping, STATICpreserved as‑is

Triggers

• DML triggers (BEFORE, AFTER, INSTEAD OF) → trigger function + CREATE TRIGGER DDL
• COMPOUND TRIGGER decomposed into per‑timing‑point functions
• ALTER TRIGGER ENABLE/DISABLE → ALTER TABLE … ENABLE/DISABLE TRIGGER
• Non‑DML triggers (ON SCHEMA, ON DATABASE) → CREATE EVENT TRIGGER
• Cross‑unit trigger table resolution: CREATE TRIGGER in earlier files populates a table map for ALTER TRIGGER in later files

Collections & Records

• TABLE OF / VARRAY type declarations in standalone CREATE TYPE
• Anonymous‑block RECORD / TABLE / VARRAY declarations
• .COUNT, .FIRST, .LAST, .EXISTS, .DELETE(key), .DELETE(),
•  .EXTEND(n), .EXTEND(n,i), .TRIM, .TRIM(n)
• DECLARE‑section RECORD aliases → RECORD‑typed variables

Dynamic SQL

• EXECUTE IMMEDIATE → EXECUTE with USING IN/OUT clause
• RETURNING INTO injected inside the SQL string (PostgreSQL requirement)

Bulk Operations

• FORALL i IN 1..n → FOR loop with array iteration
• FORALL INDICES OF / VALUES OF → recognised; emits TODO comment (requires manual porting — Oracle’s associative‑array semantics have no direct PL/pgSQL analogue)
• BULK COLLECT INTO → stripped (PostgreSQL uses plain SELECT INTO)

Exception Handling

• Named Oracle exceptions mapped to PG equivalents: NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE→DIVISION_BY_ZERO,
•  DUP_VAL_ON_INDEX→UNIQUE_VIOLATION, VALUE_ERROR→DATA_EXCEPTION, and others
• OTHERS handler → WHEN OTHERS THEN
• Custom exception declarations → DECLARE + RAISE
• PRAGMA EXCEPTION_INIT → custom SQLSTATE 'Uxxxx'
• PRAGMA AUTONOMOUS_TRANSACTION → dblink‑wrapped autonomous execution block
• PRAGMA SERIALLY_REUSABLE → comment with suggestion

XML Functions

Most XML functions are implemented as pure PL/pgSQL helper functions in xml_plpg.go. Unicode composition/decomposition (COMPOSE, DECOMPOSE) use PL/Python3u with Python stdlib unicodedata.normalize().

• XMLELEMENT, XMLFOREST, XMLAGG, XMLTABLE, XMLPARSE, XMLSERIALIZE, XMLTYPE
• EXISTSNODE, EXTRACTVALUE, XMLCONCAT, XMLCDATA, XMLCOMMENT, XMLPI, XMLROOT
• XMLISVALID, XMLSEQUENCE, XMLCAST, XMLQUERY
• UPDATEXML, DELETEXML, INSERTCHILDXML, APPENDCHILDXML, INSERTXMLBEFORE
• XMLTRANSFORM → xslt_process() via xml2 C extension

DDL Handling

• CREATE TABLE — type mapping, inline defaults, constraints, identity columns (NUMBER IDENTITY → BIGINT IDENTITY)
• ALTER TABLE — MOVE TABLESPACE → SET TABLESPACE, partition handling
• CREATE SEQUENCE — Oracle→PG syntax adaptation
• CREATE SYNONYM → CREATE VIEW wrapper
• CREATE TABLESPACE → PG tablespace with /tmp/ fallback path
• CREATE INDEX — BITMAP → B‑tree with comment
• GRANT / REVOKE — Oracle‑to‑PostgreSQL system and object privilege mapping (14 system privileges + object grants)
• CREATE DATABASE LINK → FDW CREATE SERVER + USER MAPPING
• External tables (ORGANIZATION EXTERNAL … ORACLE_LOADER) → file_fdw foreign tables with automatic server creation and deduplication

System View Mapping

Oracle system views replaced with PostgreSQL catalog equivalents:

Note: ALL_SEQUENCES, ALL_TAB_PRIVS, ALL_COL_COMMENTS, ALL_TAB_COMMENTS, ALL_SYNONYMS, USER_TABLES, USER_OBJECTS, V$PARAMETER, DBA_DATA_FILES and DBA_FREE_SPACE are recognised but not yet mapped — they pass through unchanged for manual replacement.

Helper Function Pattern

Oracle functions without a direct PostgreSQL built‑in are implemented as auto‑generated CREATE FUNCTIONhelpers. The transpiler emits both the translated call and the helper DDL in the output.

• JULIAN → julian‑to‑date conversion
• REGEXP_INSTR → regex position search
• SCN_TO_TIMESTAMP → pg_xact_commit_timestamp(xid)
• INTEGER_TO_WORDS → JSP‑format number spelling
• COMPOSE, DECOMPOSE → PL/Python3u unicodedata.normalize()
• UNISTR, ASCIISTR → Unicode escape/unescape
• XML helpers (UPDATEXML, DELETEXML, INSERTCHILDXML, etc.)

Distribution

Available Now

• CLI binary — Go cross‑compiled executable for macOS (arm64+amd64), Linux (arm64+amd64), Windows (amd64). Ideal for scripting and CI/CD. (It is ready, coming soon to distribution)
• Wails desktop GUI — cross‑platform native window (macOS, Windows, Linux)
• Windows WPF .NET app — full Windows desktop experience with Inno Setup installer 
• Linux .deb / .rpm — for Debian/Ubuntu and Fedora/RHEL

Just Released

• Mac App Store — sandboxed, code‑signed, hardened runtime, macOS 14.0+ https://apps.apple.com/us/app/plsql2pgsql/id6785566298?mt=12&itscg=30200&itsct=apps_box_link&mttnsubad=6785566298

Technical Details

• Language: Go (ANTLR4 runtime), roughly 15000 Lines of Go code
• Parser: ANTLR4 Oracle PL/SQL grammar
• Output targets: PL/pgSQL, PL/Python3u
• macOS native app: SwiftUI (Xcode project) with Cgo bridge
• Windows native app: C# WPF .NET with P/Invoke to Go DLL
• Cross‑platform GUI: Wails v2 (Go backend + WebView frontend) – for Linux and Windows
• Test suite: Go test functions + Python integration runner + end‑to‑end tests against live PostgreSQL

Disclaimer: All trademarks belong to their owners.