ora2pg-gap-report

English | Русский

A tool for assessing an Oracle → PostgreSQL Pro (Standard/Certified) migration before it starts.

pip install ora2pg-gap-report
ora2pg-gap-report path/to/oracle_schema_dump/

Oracle DDL (PACKAGE BODY / TRIGGER / TABLE / INDEX / ...)
                    │
                    ▼
            ora2pg-gap-report
                    │
                    ▼
   67 confirmed types of ora2pg migration gaps
   ┌────────────────────────────────────────────────────────┐
   │ HIGH    GAP-006  database_link    — @dblink not in PG  │
   │ HIGH    GAP-023  oracle_text      — CONTAINS()/...     │
   │ MEDIUM  GAP-025  invisible_index  — loses invisibility │
   └────────────────────────────────────────────────────────┘

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 schema before migration and reports exactly which objects ora2pg will skip without warning, underestimate the effort for, or convert potentially incorrectly — and why. 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

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 67 registered gaps (gap_registry.py), 62 are high and 5 are medium (context_object, invisible_index, virtual_column, index_organized_table) — 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 a 48th detector, 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: a summary panel (how many findings, breakdown by severity, a rough hour estimate), a compact findings table, and an explanation under every detector that fired. 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):

ora2pg-gap-report path/to/schema_dump.pkb --format json --output report.json
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. 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 click a row in the results table to see its full explanation (message, GAP-NNN, and when it actually breaks — same information --explain and the terminal report already show, just click-driven):

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.

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 67 — 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.

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. Not translated: --help (would need to know the language before argparse has parsed --lang out of argv — a separate piece of work, not done in this pass) and the research documents themselves in docs/research/ (--explain under --lang en still prints their text in Russian, as before — only the version header is translated).

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

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

A real-world output example against an open-source package — docs/examples/logger-autonomous_tx-report.md. A full CI recipe — gating a PR, running alongside ora2pg itself, and getting findings as inline PR annotations via SARIF without a custom bot — is 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 11 more) — 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, 20 more) — 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 67 — 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.

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: exactly one correction, for the one gap where the "buggy" shape is never what a correct migration would produce and the fix is a pure, unambiguous text transformation — GAP-028's identity columns. ora2pg wraps the sequence options in an extra, redundant pair of parens (GENERATED ALWAYS AS IDENTITY ((START WITH 1 INCREMENT BY 1))), which fails to load into PostgreSQL at all. --fix strips exactly that outer pair, nothing else:

ora2pg-gap-report --fix generated_postgresql/          # prints a diff, changes nothing
ora2pg-gap-report --fix --write generated_postgresql/  # actually rewrites the files

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

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

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.

(These deeper docs are currently in Russian only.)

Changelog

Version history — CHANGELOG.md.

License

Apache 2.0, see LICENSE.