PLSQL2PGSQL

 PLSQL2PGSQL — Oracle-to-PostgreSQL Transpiler for macOS

    Overview

    PLSQL2PGSQL is a native macOS application that parses Oracle PL/SQL source files using ANTLR4 and emits semantically equivalent PostgreSQL PL/pgSQL or PL/Python3u. It handles the full Oracle PL/SQL grammar:
functions, procedures, packages, triggers, types, collections, bulk operations, dynamic SQL, exception handling, transaction control, and DDL — across both single-file and batch workflows.

    Parser Architecture

    – ANTLR4-based parsing using the Oracle PL/SQL grammar targeting Go runtime

    – 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

    – Parser-level SQL\Plus handling: SET, SPOOL, PROMPT, DEFINE, UNDEFINE, COLUMN, BREAK, COMPUTE, REPHEADER, REPFOOTER, ACCEPT, EXECUTE, START, @, @@, / (slash execute), WHENEVER — all processed at the parser level before ANTLR invocation

    Language Translations

  Data Type Mappings

    Oracle VARCHAR2(n) → PostgreSQL VARCHAR(n)

    Oracle NUMBER(p,s) → PostgreSQL NUMERIC(p,s); bare NUMBER → NUMERIC

    Oracle DATE → PostgreSQL DATE

    Oracle CLOB → PostgreSQL TEXT

    Oracle BLOB → PostgreSQL BYTEA

    Oracle PLS_INTEGER, BINARY_INTEGER → PostgreSQL INTEGER

    Oracle RAW(n) → PostgreSQL BYTEA

    Oracle TIMESTAMP WITH TIME ZONE → PostgreSQL TIMESTAMPTZ

    Oracle %TYPE, %ROWTYPE → resolved to the target column’s PostgreSQL type at transpile time

    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 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

    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, STATIC preserved 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() (no-arg), .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 → TODO (requires manual porting)

    – 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, DUP_VAL_ON_INDEX, etc.

    – OTHERS handler → WHEN OTHERS THEN

    – Custom exception declarations → DECLARE + RAISE

    – PRAGMA EXCEPTION_INIT → EXCEPTION WHEN SQLSTATE ‘…’

    – PRAGMA AUTONOMOUS_TRANSACTION → dblink-wrapped autonomous execution block

    – PRAGMA SERIALLY_REUSABLE → comment with suggestion

    XML Functions (via PL/Python helpers in plpython3u mode)

    XMLELEMENT, XMLFOREST, XMLAGG, XMLTABLE, XMLPARSE, XMLSERIALIZE, XMLTYPE, EXISTSNODE, EXTRACTVALUE, XMLCONCAT, XMLCDATA, XMLCOMMENT, XMLPI, XMLROOT, XMLISVALID, XMLSEQUENCE, XMLCAST, XMLQUERY, UPDATEXML, DELETEXML, INSERTCHILDXML, APPENDCHILDXML

    DDL Handling

    – CREATE TABLE — type mapping, inline defaults, constraints, identity columns

    – ALTER TABLE — MOVE TABLESPACE → SET TABLESPACE, partition handling

    – CREATE SEQUENCE → CREATE SEQUENCE (Oracle→PG syntax)

    – 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 privilege mapping (99+ privilege pairs)

    – 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

    ALL_TABLES, ALL_TAB_COLUMNS, ALL_CONSTRAINTS, ALL_INDEXES, ALL_VIEWS, ALL_SOURCE, ALL_SEQUENCES, ALL_TAB_PRIVS, ALL_COL_COMMENTS, ALL_TAB_COMMENTS, ALL_TRIGGERS, ALL_SYNONYMS, DBA_TAB_PRIVS, USER_TABLES, USER_OBJECTS, V$SESSION, V$LOCK, V$SQL, V$PARAMETER, DBA_DATA_FILES, DBA_FREE_SPACE — all mapped to PostgreSQL catalog equivalents (pg_catalog, information_schema, pg_stat_activity, etc.)

    Distribution

    – Soon Available on the Mac App Store

    – Native performance on both Apple Silicon and Intel Macs

    – Sandboxed with read-write file access, hardened runtime

    – Code-signed

    – Requires macOS 14.0+