ora2pg-gap-report

English | Русский

A companion for migrating Oracle, MySQL/MariaDB and SQL Server to PostgreSQL with ora2pg: it finds what ora2pg will get wrong before you start, repairs what can be repaired mechanically, and checks the result against a real PostgreSQL - so the gaps turn up on your laptop, not in production.

pip install ora2pg-gap-report

# What will break, before anything is converted
ora2pg-gap-report schema/

# The whole path, checked against a real, throwaway PostgreSQL
ora2pg-gap-report --migrate out/ --load-check docker schema/

What --migrate does with schema/, step by step - each step is also a mode of its own:

 schema/ (Oracle DDL, a mysqldump, an SSMS script)
    |
    |  1. scan       133 confirmed ora2pg gaps    -> out/report.html, out/MIGRATION.md
    |  2. prepare    rewrite what ora2pg's parser trips over  (--prepare)
    |  3. convert    ora2pg, once per object type             -> out/converted/
    |  4. fix        repair ora2pg's known mechanical bugs    (--fix), and what the source says
    |  5. load       into a real PostgreSQL, every error tied to its gap  (--load-check)
    v
 out/converted/*.sql that loads - or a list of exactly what does not, and why

--dialect mysql or --dialect mssql for the other two sources. ora2pg itself is needed for --migrate (on PATH, or --ora2pg-bin docker:IMAGE); scanning alone needs nothing but Python.

Or nothing but docker. The image has the tool, ora2pg 25.0 and psql in it; give it the docker socket for --load-check docker:

docker run --rm --user "$(id -u):$(id -g)" --group-add "$(stat -c %g /var/run/docker.sock)" \
  -v "$PWD:/work" -v /var/run/docker.sock:/var/run/docker.sock \
  ghcr.io/lunch418/ora2pg-gap-report --migrate out/ --load-check docker schema/

--user and --group-add make the files in out/ yours and let the container reach docker; for a scan alone, docker run --rm -v "$PWD:/work" ghcr.io/lunch418/ora2pg-gap-report schema/ is enough. The image speaks English; add --lang ru for Russian. With the tool installed by pip but no ora2pg, the same image can be just the ora2pg: --ora2pg-bin docker:ghcr.io/lunch418/ora2pg-gap-report.

The problem

Migrating from Oracle to Postgres Pro Standard/Certified (i.e. without a Postgres Pro Enterprise license and without the proprietary ora2pgpro utility), the only available automated converter is the open-source ora2pg. By independent estimates it covers around ~80% of the PL/SQL -> PL/pgSQL conversion job on average. The remaining ~20% (packages, autonomous transactions, CONNECT BY, DBMS_*/UTL_* calls, compound triggers) is currently sorted out by hand, and is typically discovered after the fact — once something has already broken in production.

What this tool does

Scans an Oracle, MySQL/MariaDB or SQL Server schema before migration and reports exactly which objects ora2pg will skip without warning, underestimate the effort for, or convert incorrectly - and why; then, if you want, runs the migration itself and checks the result against a real PostgreSQL. Not a replacement for ora2pg, a layer on top of it: the list of what it actually fails to carry over was verified empirically against real PL/SQL code (docs/research/step0-show-report-baseline.md), not taken on faith.

Detectors

The twenty-seven below are the MySQL/MariaDB dialect (--dialect mysql, ora2pg -m — see "Source dialects" further down); every other detector in this table is Oracle-only.

And these twenty-one are the T-SQL/SQL Server dialect (--dialect mssql, ora2pg -M).

Plus ora2pg_wrapper.py — runs ora2pg per object type against exported DDL and parses --estimate_cost, and oracle_connector.py/oracle_export.py — a live export of PACKAGE BODY/TRIGGER straight from an Oracle schema via DBMS_METADATA.GET_DDL.

Why almost everything is high

Of the 133 registered gaps (gap_registry.py) — 85 from the Oracle source dialect, 27 from MySQL/MariaDB (dialect="mysql", ora2pg -m) and 21 from T-SQL/SQL Server (dialect="mssql", ora2pg -M); see "Source dialects" below — 127 are high and 6 are medium (context_object, invisible_index, virtual_column, index_organized_table, sdo_geometry on the Oracle side, mysql_set_type on the MySQL side; the MSSQL batch has no medium at all) — severity is a GapEntry field now, cross-checked by scripts/doctor.py against the literal a detector's own source actually uses, not just a count taken on faith. Separately, there's one more detector on top of those 133, dbms_utl_calls — a classifier for DBMS_*/UTL_* calls, not tied to a specific GAP-NNN (it has no single reproducible minimal example — that's a deliberately broad category), also medium. low is a valid value in the registry (--severity low, with an hour range in effort_estimator.py), but hasn't been assigned to any detector yet — honestly, not because the criterion wasn't thought through, but because none of the confirmed cases landed there. Not a distribution chosen for its own sake — it fell out of real findings, following this principle:

• high — either the generated code genuinely fails to compile/run in PostgreSQL (confirmed by running it on real PostgreSQL 16 — ERROR: syntax error... and similar, see the table in docs/research/AUDIT.md), or the construct disappears silently but the loss is architecturally significant: partitioning, an external table, a materialized view log, a READ ONLY guarantee, a database link — things that either break the migration outright or silently change system behavior in a way that isn't noticed right away, only in production.
• medium — doesn't block the migration and doesn't lose data, but a real behavioral divergence worth double-checking: invisible_index (the index stops being hidden from the optimizer — affects the query plan, not correctness), context_object (an application feature, often the basis for VPD, but the migration itself doesn't fail from losing it), virtual_column (the final value in the column is correct — what's lost isn't data, it's early diagnostics for a mistaken explicit assignment), index_organized_table (integrity constraints are preserved — what's lost is storage architecture, not correctness), and separately dbms_utl_calls (a deliberately broad classifier — the real impact of a specific call varies too much to honestly call all of them high).

Methodology

This project doesn't try to find a detector for every Oracle-specific construct that exists. ROWNUM, DECODE, NVL, SYSDATE, %TYPE, sequences, standard exception semantics — ora2pg converts all of these correctly, and no detector is needed for them, however exotically Oracle-flavored they sound.

A new detector only appears once the hypothesis has been checked in practice:

1. Pick a specific Oracle construct.
2. Build a minimal reproducible example.
3. Run the example through real ora2pg.
4. Check the generated PostgreSQL code for correctness.
5. If ora2pg handled it — the hypothesis is rejected, no detector gets written. If a real, reproducible bug turns up — a test fixture is added and a detector gets written.

That's how the initial hypothesis about CREATE PACKAGE was ruled out, for example — an obvious-looking candidate at first glance, but in practice ora2pg carries it over without issue (docs/research/step0-show-report-baseline.md). And that's how COMPOUND TRIGGER and the LEVEL bug in CONNECT BY were confirmed — both reproduced on a real ora2pg run, not assumed from a description.

Every confirmed finding is numbered and collected in docs/research/GAP_REGISTRY.md — each entry states which detector covers it and against which ora2pg version it was confirmed. docs/research/AUDIT.md is a summary check of the evidence behind every confirmed gap (research doc, real ora2pg output, expected/actual, tests, including guard tests against false positives).

Installation and usage

pip install ora2pg-gap-report   # (or: pip install . from a repo checkout)

The detector library itself (detectors/, models.py, report_generator.py) is pure Python with zero external dependencies — it can be imported on its own (e.g. from your own scripts) without installing anything else at all. The CLI has exactly one required dependency — rich, purely for a pleasant terminal output; it installs itself via pip install.

Right after installation, the command is available:

ora2pg-gap-report path/to/schema_dump.pkb another_file.sql

In an interactive terminal, the default is a colored report laid out like the HTML one: how many findings wait at each stage a migration reaches (conversion, schema load, run time, silently), the severity split and a rough hour range, every gap once, then each gap in detail — why, what to do, and its first few occurrences. For scripts/redirects — --format markdown, --format json, --format csv, --format sarif, or --format html (markdown also serves as the default format whenever stdout isn't a terminal):

--format can be omitted when --output's extension already says which one you want — .json, .csv, .sarif, .html/.htm and .md are recognised, anything else falls back to markdown, and an explicit --format always wins. -f, -o and -l are short forms of --format, --output and --lang.

ora2pg-gap-report path/to/schema_dump.pkb -o report.json   # format from the extension
ora2pg-gap-report path/to/schema_dump.pkb --format markdown > report.md
ora2pg-gap-report path/to/schema_dump.pkb --format csv --output report.csv

# SARIF 2.1.0 — for GitHub code scanning (Security tab) or GitLab SAST.
# Severity is mapped to SARIF levels: high -> error, medium -> warning,
# low -> note (SARIF has no separate critical level, and neither does
# this tool).
ora2pg-gap-report path/to/schema_dump.pkb --format sarif --output report.sarif

# A self-contained HTML page (no external CSS/JS/fonts — opens offline)
# — to show a client/manager, without installing anything.
ora2pg-gap-report path/to/schema_dump.pkb --format html --output report.html

# Optional: lint ora2pg's own generated code for CONNECT BY.
# Requires ora2pg to be installed (see https://github.com/darold/ora2pg)
# — the only external (non-Python) dependency anywhere in this project,
# and only for this one specific check.
ora2pg-gap-report path/to/schema_dump.pkb --check-connect-by

The --format json format is described by a formal JSON Schema — schemas/report.schema.json (and the baseline-snapshot format from --save/--baseline is in schemas/baseline.schema.json), so third-party tools can reliably parse the output instead of guessing from examples. Both schemas are checked in the tests against real output (tests/test_schemas.py) — not just written and left as-is. --format sarif is checked the same way in tests/test_sarif.py against the official OASIS SARIF 2.1.0 schema (vendored into tests/fixtures/, so the tests don't depend on the network).

DDL files can be passed as-is — a single file may contain multiple packages/triggers, the detectors figure out object boundaries themselves, including from a script's own separators — SQL*Plus's /, T-SQL's GO, MySQL's DELIMITER. A directory can be passed too: everything with a .sql/.pks/.pkb extension inside gets scanned recursively (e.g. an entire DBMS_METADATA.GET_DDL export directory):

ora2pg-gap-report path/to/schema_dump_dir/

ora2pg-gap-report --version — show the installed version.

Interactive mode (--tui)

Everything above is flag-driven, on purpose — that's what makes it scriptable and CI-friendly. For browsing interactively instead of remembering flags, --tui opens a mouse/keyboard-driven screen: pick a file or directory in a tree, choose severity/language, scan, then move through the results table with the arrow keys: the box under it follows the cursor and shows each finding's GAP-NNN, when it actually breaks, what to do and why — the same information --explain and the terminal report show. It is styled after Claude Code's own terminal UI. To install and open it:

pip install "ora2pg-gap-report[tui]"   # adds textual — not part of the base install
ora2pg-gap-report --tui                # opens in the current directory
ora2pg-gap-report --tui path/to/schema_dump/   # opens there instead

Standalone mode, like --explain/--verify: the CLI takes at most one path (a starting point for the tree, not a list to scan directly — picking what to scan is the point of being inside the tree) and none of the scan-shaping flags (--severity, --format, --fail-on, --save, and so on all no-op once you're inside the TUI, so combining them is rejected outright rather than silently ignored). Once inside, the screen itself covers the same ground the flag-based workflow does: queue more than one file/directory with "Add to selection" before scanning, tick "Check CONNECT BY" for the same opt-in ora2pg-backed check --check-connect-by runs, and point the baseline field at a --save snapshot to see NEW/RESOLVED/UNCHANGED counts on the results screen (with its own "Save baseline" button to write one), or tick "Verify mode" to run the same post-migration --verify comparison against it. Running --tui without the [tui] extra installed prints a plain install hint, not a traceback.

"Migrate" runs --migrate on what is picked: a screen asks where to write, which ora2pg to use (ora2pg on PATH or docker:IMAGE) and whether to load the result into PostgreSQL in docker, shows each step while it runs, and ends with the same summary the command line prints. The directory it writes is the same too - report.html, MIGRATION.md, converted/, and load-check.txt/.json with the load.

Documentation straight from the CLI

--explain GAP-023 (or just --explain 23) prints a specific gap's research document from the registry — the Oracle construct, real ora2pg output, the observed problem, the verdict, and the ora2pg/PostgreSQL versions the finding was confirmed against (currently 25.0/16 for all 133 — a single version, because there hasn't been a second one yet; gap_registry.py is already set up to store different versions for future findings) — without scanning any files:

ora2pg-gap-report --explain GAP-023

Research documents (docs/research/) are part of the repository but not part of the pip package (the package is ora2pg_gap_report/ only). When run from a package installed via pip install, rather than from a repo checkout, --explain shows a direct link to the document on GitHub instead of the document's text.

Migration recipes

The research docs say what goes wrong. The recipes say what to write instead: fifteen pages, one per class of problem (hierarchical queries, collections and BULK COLLECT, autonomous transactions, package state, temporary tables, PIVOT, MERGE/upserts, error handling, database links, DBMS_* calls, analytic functions, read-only and invisible objects, partitioning, T-SQL and MySQL expressions), each with the PostgreSQL pattern and what does not carry over. Every gap that has a recipe links to it from --explain, the terminal and HTML reports, the TUI, the checklist and --load-check.

The code in the recipes is not illustration: the test suite loads every page's SQL into a real PostgreSQL 16 and runs the ASSERTs in it, in both languages, so a recipe that stops working fails the build.

Target PostgreSQL version (--pg-version)

Every gap was confirmed on PostgreSQL 16. Some stop being a problem on a newer version, and --pg-version takes that into account - only where it was checked on that version:

ora2pg-gap-report --pg-version 17 schema/                          # JSON_TABLE is not reported
ora2pg-gap-report --pg-version 17 --load-check docker generated/   # loads into postgres:17

So far: JSON_TABLE (GAP-017) loads and returns the same rows on 17 and 18, NESTED PATH included - except with Oracle's ERROR ON ERROR before COLUMNS, which is still reported. What was left out is said on stderr. With a version older than 16 nothing is left out, and a note says the gaps were confirmed on 16: check the generated code with --load-check docker --pg-version N, which loads it into that version.

What the scan cannot see: SQL built at run time

EXECUTE IMMEDIATE v_sql, OPEN c FOR v_sql, DBMS_SQL.PARSE(c, v_sql), MySQL's PREPARE s FROM @sql, T-SQL's EXEC(@sql) and sp_executesql @sql run SQL whose text is only known when the program runs. No reading of the source can check it, this tool included. So instead of staying silent about it, every report lists these statements in a section of their own, "Not checked", after the findings: file, line, object, the statement, and "partly" where the text is joined from string literals and variables (the literals are scanned, what the variables add is not). SQL made only of literals is scanned in full and is not listed.

They are not findings: they count toward no severity, stage, effort estimate or --fail-on. In MIGRATION.md each is a box to tick once it has been run on PostgreSQL, and the ticks are kept like the others'. In JSON they are the unchecked array (schema_version 3); CSV and SARIF carry findings only.

Source dialects (--dialect)

ora2pg isn't Oracle-only: -m/--mysql and -M/--mssql point it at a MySQL/MariaDB or SQL Server source instead, still targeting PostgreSQL. Both were confirmed to work file-based (-i <file>, no live source database needed), so this project scans all three:

ora2pg-gap-report schema/                        # Oracle (the default)
ora2pg-gap-report --dialect mysql mysqldump.sql  # GAP-068..086, 106..111, 127..128
ora2pg-gap-report --dialect mssql ssms.sql       # GAP-087..105, 125, 126

Every non-Oracle gap was confirmed exactly the way the Oracle ones were: a minimal example, a real ora2pg -m/-M run, the generated PostgreSQL loaded onto a real PostgreSQL 16 server — and for the ones that never raise an error, a query actually run against real data to show what changes.

The three dialects' detectors are structurally separate (core.py's _ORACLE_DETECTORS/_MYSQL_DETECTORS/_MSSQL_DETECTORS tuples), so a file scanned under the wrong --dialect cannot trigger another dialect's detectors — by construction, not by keyword luck.

--verify, --fix and --tui all work across the three dialects too:

• --verify needs no --dialect at all. Which detectors re-scan the generated output is worked out from the baseline itself — every detector belongs to exactly one dialect, so the names already in the snapshot determine it. That also means baselines written before dialects existed keep verifying unchanged, with no schema bump. Passing --dialect anyway is allowed but cross-checked: a snapshot taken with one dialect and verified with another's detectors would report "not detected" for every finding, which is a tautology rather than a check, so the pair is rejected instead. A snapshot mixing dialects, or naming detectors this build doesn't have, is rejected for the same reason — verifying against part of a baseline would produce a confident number computed from incomplete input.
• --fix runs the mechanical fixes registered for --dialect. Each dialect has its own (see below); a dialect with none would say so instead of reporting every file as "nothing to fix", which would read as "your output is fine".
• --tui has a dialect picker beside the severity and language ones, and applies the same rules — including taking the dialect from the baseline in verify mode.

--check-connect-by stays Oracle-only and now says so: CONNECT BY is Oracle syntax and the check runs ora2pg in Oracle mode, so on another dialect's file it could only ever find nothing.

Output language

The default output is in Russian — it doesn't change without an explicit action, so existing scripts and CI that parse the current output keep working unchanged. English is available as an option:

• --lang en — for this run only, saves nothing;
• --set-lang — opens a language picker ([1] English / [2] Русский) and saves it as the default for all future runs (~/.config/ora2pg-gap-report/language, or $XDG_CONFIG_HOME);
• ORA2PG_GAP_REPORT_LANG=en — for CI, not saved;
• on first run in an interactive terminal, if no language is set anywhere, the --set-lang picker shows itself once and saves the choice.

Priority order: --lang -> environment variable -> saved choice -> interactive picker (a real terminal only) -> Russian by default.

The entire scan output is translated: the terminal report, --format markdown/html, per-detector explanations and remediation hints, error messages, and --help (it follows --lang and the same priority order above). The research documents in docs/research/ exist in both languages — the English text at gap-NNN-*.md, the Russian beside it as .ru.md, for every gap — and --explain prints the one matching the output language.

Tracking migration progress (baseline)

A schema is usually fixed up iteratively — a snapshot of "what's wrong right now," then some fixes, then a re-run. --save stores the current run's findings as a snapshot; --baseline compares the next run against it and shows NEW/RESOLVED/UNCHANGED (on stderr, separate from the report itself):

ora2pg-gap-report path/to/schema_dump/ --save baseline.json
# ... fix up the schema, convert some objects by hand ...
ora2pg-gap-report path/to/schema_dump/ --baseline baseline.json

Findings are matched between runs not by line number (which shifts on any file edit), but by a fingerprint built from the detector, file, object, and matched snippet — so a finding is recognized as "the same one" even if the code around it was rewritten. --save/--baseline always operate on the full set of findings, regardless of --severity/--object (those flags only affect what gets displayed in the report).

A checklist that remembers (-f checklist)

For the weeks of work after the first scan, -f checklist writes a Markdown task list: one box per object and gap, grouped like the reports, each gap with what to do, its recipe and the --explain command. Commit it to the repository or paste it into an issue, and tick boxes as you go.

ora2pg-gap-report schema/ -f checklist -o MIGRATION.md
# ... fix things, tick boxes in MIGRATION.md ...
ora2pg-gap-report schema/ -f checklist -o MIGRATION.md   # same -o: progress is kept

**Done: 2 of 4 (50 %)**

## GAP-003 `TYPE ... IS TABLE OF` / `BULK COLLECT INTO` / `FORALL`

high · breaks at: run time · 1 of 2 open

**What to do:** Rewrite TYPE/BULK COLLECT as a PostgreSQL array ...
**Recipe:** [Collections and bulk operations](docs/recipes/collections-and-bulk.md)

- [ ] `EQUITABLE_SALARY_TRG` - `triggers.sql` (line 215)
- [x] `EQUITABLE_SALARIES_PKG` - `triggers.sql` (line 76)

Regenerating into the same -o reads the previous file first: a box you ticked stays ticked, an item no longer found in a file this run scanned again ticks itself ("no longer found"), and an item in a file not scanned this time keeps its state, so scanning a subset never marks the rest done. Run it from the same directory each time: items are keyed by object and by file path relative to it. A tick follows its object when the file is renamed or moved, and a routine when its package is split or renamed (PKG.LOG -> PKG_LOGGING.LOG), marked "was: ..." - but only when the match is unambiguous; otherwise the old item and the new one both stay. An existing file that is not a checklist this tool wrote is never overwritten.

CI gate

--fail-on high (or medium/low) — exit with code 1 if there's at least one finding at that severity level or higher (high above medium above low). Like --save/--baseline, this is evaluated against the full set of findings, not what's left after --severity/--object:

ora2pg-gap-report path/to/schema_dump/ --fail-on high
echo $?   # 1 if at least one high finding turned up

Exit codes, all distinct on purpose so a CI job can tell a real result from a broken run:

Code 3 matters most in CI: an analyzer that crashed used to exit 1, indistinguishable from a gate that had honestly done its job and found problems.

A real-world output example against an open-source package — docs/examples/logger-autonomous_tx-report.md. On GitHub, the repository is a ready-made Action that does the scan, the SARIF upload to code scanning and the gate in one step:

- uses: Lunch418/ora2pg-gap-report@v0.17.0
  with:
    paths: schema/
    fail-on: high

Its inputs, and a full CI recipe — gating a PR, running alongside ora2pg itself, findings as inline PR annotations via SARIF — are in docs/ci-integration.md.

The effort estimate in the report is a rough heuristic by severity (an hour range, not a single number). It's a planning reference, not an estimate calibrated against real migrations — don't hand it to a client as a commitment. The severity range only prices the first occurrence of each detector — repeat findings from the same detector (the same already-learned fix applied again, not a new task) are priced with a separate, much smaller range instead of being counted as independent high/medium tasks each: 8 autonomous_tx findings in one package isn't 8 separate problems.

Post-migration check (--verify)

--save/--baseline compare two runs against the Oracle source over time. --verify is different: it compares pre-migration findings against what actually remains in the generated ora2pg PostgreSQL code:

ora2pg-gap-report oracle_schema/ --save migration.json   # before migration
# ... run ora2pg, get generated_postgresql/ ...
ora2pg-gap-report --verify --baseline migration.json generated_postgresql/

Baseline detectors  4
Still present        2
Not detected          1
Not verifiable        1

cross_apply       GAP-022   3 -> 1   STILL_PRESENT
json_table        GAP-017   2 -> 0   NOT_DETECTED
identity_column   GAP-028   4 -> 4   STILL_PRESENT
read_only_table   GAP-026   1 -> —   NOT_VERIFIABLE

This is not a functional check — the tool never connects to a database, never executes anything, never compares data. It statically looks for the same pattern already in the generated code. And even so, it doesn't work the same way for every detector:

• Some constructs ora2pg copies into its output as-is (cross_apply, json_table, identity_column, and 56 more — 59 of the 134 detectors) — for these, re-running the detector against the output is meaningful: STILL_PRESENT if the pattern remains, NOT_DETECTED if it's gone.
• Some ora2pg drops or rewrites away entirely (read_only_table, table_partitioning, and 70 more — 74 of the 134) — the construct isn't in the output by definition, regardless of whether someone fixed the problem by hand some other way. For these, the honest status is NOT_VERIFIABLE, not a fabricated NOT_DETECTED: treating absence as proof of a fix would be exactly the kind of manufactured confidence this project specifically avoids (see "Why almost everything is high" above).

Which mode applies to which detector, and why, for all 133 gaps — docs/verification-capability-matrix.md.

NOT_DETECTED also doesn't mean "provably fixed" — only "the pattern wasn't found in this code." A small difference, but it's exactly what separates an honest check from a comfortable lie.

--verify is a standalone mode: requires --baseline, incompatible with --explain/--save/--fail-on/--check-connect-by/--severity/--object, supports only --format terminal (default) and --format json.

The whole path in one command (--migrate)

ora2pg-gap-report --migrate out/ --load-check docker schema/
ora2pg-gap-report --migrate out/ --ora2pg-bin docker:my-ora2pg-image schema/   # ora2pg from an image

out/
  report.html        the scan of the source: what breaks, when, where, with a recipe per gap
  MIGRATION.md       the work as a checklist (keeps your ticks on the next run)
  prepared/          a copy of the source after --prepare; the source itself is not touched
  converted/         ora2pg's output, one file per object type, in load order, after --fix
  load-check.txt     with --load-check: every statement that did not load, and why
  load-check.json    the same for a pipeline

The steps are the modes described below, run in the order a migration needs them. A few things --migrate does that running them by hand would not:

• ora2pg runs once per object type on all the source files at once, so a call from one package to another converts (two separate runs lose it, see GAP-117);
• in file mode ora2pg's -t TYPE, FUNCTION and PROCEDURE also extract the members of packages, without the package name, so every one of them would come out twice; those runs get the source without its packages, and -t PACKAGE gets only the packages (it would glue an object that follows the last package body into that body);
• it puts back what ora2pg's output has lost but the source still says, checked on PostgreSQL 16 the way --fix is: a trigger without FOR EACH ROW stays a statement trigger (GAP-118), a package constant whose value is a literal is written as that literal wherever ora2pg left a current_setting() read or a DEFAULT naming it (GAP-114, GAP-119, GAP-036), a MySQL ENUM column gets the CREATE TYPE ora2pg names but never writes (GAP-068);
• running again into the same out/ replaces the generated files and keeps the checklist's ticks; a directory it did not create is refused.

Exit code 1 when --load-check finds statements that do not load, 2 when the run cannot be done (no ora2pg, no docker), 0 otherwise.

Preparing the source (--prepare)

Some gaps cannot be repaired in ora2pg's output, because ora2pg has already thrown away what the repair would need: a T-SQL script with bracketed names comes out with a column of type [INT] and the nvarchar(100) length gone, a MySQL trigger under DELIMITER // does not come out at all. Each of these converts correctly when the same source is written in the plainer form ora2pg's parser expects. --prepare writes it that way, before ora2pg reads the dump:

cp -r dump/ dump.prepared/                                        # work on a copy
ora2pg-gap-report --prepare --dialect mysql dump.prepared/          # prints a diff
ora2pg-gap-report --prepare --dialect mysql --write dump.prepared/  # rewrites the files
ora2pg -m -i dump.prepared/schema.sql ...

The text means the same before and after: brackets, a DELIMITER directive, a version-comment wrapper or a definer change how it is written, not what it defines. Nothing inside a string or a comment is touched. Every rewrite was confirmed by running ora2pg 25.0 on both forms and loading the results into PostgreSQL 16, and the test suite repeats that: against the real ora2pg in CI, and against PostgreSQL with checks of behaviour (the trigger fires, the procedure updates its row). The scan report and --explain name the command for every gap it removes.

Autofix (--fix)

Everything above only flags and explains — this project is a detector, not a parser, and rewriting DDL about to be deployed is a much riskier thing to get wrong than a missed or extra flag (see docs/ARCHITECTURE.md). --fix is a narrow, deliberate exception: only corrections where the "buggy" shape is never what a correct migration would produce and the fix is a pure, unambiguous text transformation. Seven qualify so far, and which of them run is decided by --dialect:

All seven were verified the same way the gaps themselves were: the broken output failing to load into a real PostgreSQL 16, and the fixed output loading and running.

ora2pg-gap-report --fix generated_postgresql/          # prints a diff, changes nothing
ora2pg-gap-report --fix --write generated_postgresql/  # actually rewrites the files
ora2pg-gap-report --fix --dialect mssql --write out/   # the T-SQL fixes

Like --verify, it reads its paths as ora2pg's generated PostgreSQL output, not the Oracle source — the bug lives in ora2pg's own conversion logic, not in anything the Oracle DDL says. Dry-run by default; --write is required to touch anything on disk. A file changes only where it is fixed: its encoding (cp1251 included), Windows line endings, BOM and permissions are kept, and the diff is written in the file's own bytes, so it applies with patch/git apply. Standalone mode, same as --verify/--tui/--explain — not combinable with the scan-shaping flags.

A runnable, real (not simulated) walk through the whole SCAN -> migrate -> VERIFY lifecycle — real ora2pg 25.0 output, both the broken and a manually fixed version confirmed against a real PostgreSQL 16 server — examples/end-to-end/.

Load check against a real PostgreSQL (--load-check)

--verify and --fix read text. --load-check asks PostgreSQL itself: it loads ora2pg's generated files into a real server and says, for every statement that fails, what it is and what to do about it.

ora2pg-gap-report --load-check docker generated_postgresql/
ora2pg-gap-report --load-check docker:postgres:17 generated_postgresql/   # another image
ora2pg-gap-report --load-check postgresql://me@localhost/scratch out/     # an existing server
ora2pg-gap-report --load-check docker out/ -f json -o load.json           # for a pipeline

* Load check against PostgreSQL

  Server       docker postgres:16-alpine (16.4)
  Loaded       12 files, 418 statements
  Didn't load  9
    --fix repairs it       2
    a known gap            4
    not in the registry    1
    a missing object       2

● --fix repairs these  2
  out/TABLE_output.sql:41  42601  syntax error at or near "("
    GAP-028 · GENERATED ... AS IDENTITY (...) with options — a doubled-parenthesis bug
    -> ora2pg-gap-report --fix --write out/TABLE_output.sql

● Known ora2pg gaps  4
  out/PACKAGE_output.sql:212  42601  syntax error at or near "IS"
    GAP-003 · TYPE ... IS TABLE OF / BULK COLLECT INTO / FORALL
    -> What to do: ora2pg-gap-report --explain GAP-003
  ...

Each error lands in one of five groups, in the order worth working through:

Targets. docker starts a fresh postgres:16-alpine container (the version every gap here was confirmed on), publishes no port, and removes it afterwards - only docker is needed, psql runs inside the container. docker:IMAGE uses an image of your own (say, one with orafce). Anything else is a libpq connection string or URI, used through the local psql.

Nothing is left behind. All files run in one transaction with psql's ON_ERROR_ROLLBACK, so a failed statement doesn't stop the rest, and the transaction is rolled back at the end. Before loading, each file's own COMMIT/BEGIN/END and psql commands (\set ON_ERROR_STOP, \i, \connect) are blanked out - replaced with spaces, so line numbers stay exactly those of your file. Even so, with a connection string point it at an empty scratch database: DDL holds its locks until the rollback.

What "loaded" means. ora2pg puts SET check_function_bodies = false at the top of every file it writes, which makes PostgreSQL accept a PL/pgSQL body without parsing it - so a procedure still full of Oracle syntax "loads". The check turns it back on, which is where most of this registry's "fails at compile time" gaps show up. Nothing is executed: a statement that loads can still behave differently from Oracle at run time, and the report says so.

Order. Files named on the command line load in the order given. A directory's files load in ora2pg's type order - types, sequences, tables, views, routines, triggers, indexes, constraints, foreign keys, grants - so a table exists before its indexes and foreign keys.

Exit codes: 0 everything loaded, 1 something didn't (a CI gate, like --fail-on), 2 the check couldn't run (no docker or psql, no connection) or a file was skipped. --format terminal (default) and --format json (schema); --dialect picks the detectors and fixes used to explain the errors. Standalone mode, like --verify/--fix.

Exporting DDL directly from Oracle (optional)

If you have a live Oracle schema on hand instead of an already-prepared DDL dump:

pip install "ora2pg-gap-report[oracle]"   # adds python-oracledb, thin mode, no Instant Client

ora2pg-gap-export --dsn host:1521/ORCLPDB1 --user hr --output-dir dumps/
# the password comes from the ORACLE_PASSWORD environment variable, or is prompted for interactively

ora2pg-gap-report dumps/*.sql

It exports 14 object types — package specs and bodies, triggers, standalone procedures and functions, types and type bodies, views, materialized views and their logs, tables, indexes, sequences and synonyms — one .sql file per object, so the schema-level detectors (table clauses, indexes, sequences, synonyms) see as much as the code-level ones do. Narrow it with --types when a schema is large and only part of it is in scope:

ora2pg-gap-export --dsn host:1521/ORCLPDB1 --user hr --types package-body,trigger

An object whose DDL the connected user may not read (ORA-31603, routine on a real schema) is skipped and named at the end, rather than failing the whole export. Exporting again into the same directory replaces the previous export's files.

ora2pg-gap-export is a separate command, not a flag on ora2pg-gap-report, deliberately: exporting requires network access to Oracle, analysis never does. In a closed environment this is often two different machines (a jump host with DB access, and an isolated workstation for analysis) — the only thing that needs to cross that boundary is the already-exported .sql files.

Installing without internet access (closed network)

This tool's target audience is exactly isolated networks with no outside access, so pip install usually isn't an option there. The solution: build a self-contained archive on a machine with internet access, move it over by whatever means the environment allows (scp/sftp/via a jump host/on a USB drive), and install it on the target machine with no network at all:

# On a machine with internet access, from a repo checkout:
python scripts/build_offline_bundle.py --oracle   # --oracle is optional, --dev for pytest
# -> ora2pg-gap-report-offline.tar.gz (the package + rich + everything
#   transitively, including oracledb and its dependencies if --oracle is given)

scp ora2pg-gap-report-offline.tar.gz user@jump-host:/tmp/
# ...however you can get it the rest of the way to the target machine —
# sftp, another jump host, a physical transfer

# On the target machine, WITHOUT internet access:
tar xzf ora2pg-gap-report-offline.tar.gz
cd ora2pg-gap-report-offline
./install.sh oracle        # or: python3 install.py oracle

install.sh/install.py call pip install --no-index --find-links=./wheels ... — pip installs entirely from the .whl files sitting next to it, not a single network call.

rich and its dependencies (markdown-it-py, pygments, mdurl) are pure Python — one set of wheels works everywhere. oracledb (only pulled in with --oracle) ships platform-specific wheels — if the build machine differs from the target machine's OS/architecture/Python version, pass --platform/--python-version/--abi to build_offline_bundle.py (see --help) to download wheels for the actual target platform, not the one the script happens to be running on.

Every GitHub Release also ships a base bundle (no --oracle) as a downloadable asset — built the same way in CI — for anyone who just wants the base install without running the script themselves.

Development and architecture

pip install -e ".[dev]"   # editable mode + pytest
pytest

How the tool is built internally (the lexer, masking, finding attribution, dynamic SQL handling, file layout) — in docs/ARCHITECTURE.md. How to verify changes, what real open-source code corpus is used to check detectors for false positives, how to confirm a finding against a live Oracle instance — in docs/DEVELOPMENT.md. How to submit a finding or a PR — in CONTRIBUTING.md, code of conduct — in CODE_OF_CONDUCT.md, how to report a vulnerability — in SECURITY.md. Where the project is headed, and what is already built versus still just an idea waiting for a real use case — in ROADMAP.md.

(Each of these also has a Russian version beside it, as .ru.md.)

Changelog

Version history — CHANGELOG.md.

License

Apache 2.0, see LICENSE.