Metadata-Version: 2.4
Name: ora2pg-gap-report
Version: 0.10.0
Summary: Detectors that find Oracle → PostgreSQL migration gaps ora2pg misses or converts incorrectly, each verified against real ora2pg and PostgreSQL runs.
Author: Lunch418
License-Expression: Apache-2.0
Project-URL: Homepage, https://github.com/Lunch418/ora2pg-gap-report
Project-URL: Documentation, https://github.com/Lunch418/ora2pg-gap-report#readme
Project-URL: Repository, https://github.com/Lunch418/ora2pg-gap-report
Project-URL: Changelog, https://github.com/Lunch418/ora2pg-gap-report/blob/main/CHANGELOG.md
Project-URL: Issues, https://github.com/Lunch418/ora2pg-gap-report/issues
Keywords: oracle,postgresql,ora2pg,migration,plsql
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3 :: Only
Classifier: Operating System :: OS Independent
Classifier: Intended Audience :: Developers
Classifier: Topic :: Database
Classifier: Environment :: Console
Requires-Python: >=3.10
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: rich<16,>=13
Provides-Extra: oracle
Requires-Dist: oracledb>=2.0; extra == "oracle"
Provides-Extra: tui
Requires-Dist: textual<9,>=1.0; extra == "tui"
Provides-Extra: dev
Requires-Dist: pytest>=7.0; extra == "dev"
Requires-Dist: pytest-asyncio>=0.24; extra == "dev"
Requires-Dist: ruff<0.17,>=0.6; extra == "dev"
Requires-Dist: jsonschema>=4.0; extra == "dev"
Requires-Dist: textual<9,>=1.0; extra == "dev"
Requires-Dist: mypy>=1.10; extra == "dev"
Dynamic: license-file

# ora2pg-gap-report

*English | [Русский](README.ru.md)*

[![tests](https://github.com/Lunch418/ora2pg-gap-report/actions/workflows/tests.yml/badge.svg)](https://github.com/Lunch418/ora2pg-gap-report/actions/workflows/tests.yml)
[![PyPI](https://img.shields.io/pypi/v/ora2pg-gap-report)](https://pypi.org/project/ora2pg-gap-report/)
[![Python](https://img.shields.io/pypi/pyversions/ora2pg-gap-report)](https://pypi.org/project/ora2pg-gap-report/)
[![License: Apache 2.0](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](LICENSE)

A tool for assessing a migration to PostgreSQL Pro (Standard/Certified)
**before** it starts. Three source dialects, all through `ora2pg`:
Oracle, MySQL/MariaDB (`--dialect mysql`, the source side of `ora2pg -m`)
and T-SQL/SQL Server (`--dialect mssql`, the source side of `ora2pg -M`).

```sh
pip install ora2pg-gap-report
ora2pg-gap-report path/to/oracle_schema_dump/
ora2pg-gap-report --dialect mysql path/to/mysqldump.sql
ora2pg-gap-report --dialect mssql path/to/ssms_script.sql
```

```
Oracle DDL (PACKAGE BODY / TRIGGER / TABLE / INDEX / ...)
MySQL/MariaDB dump (TABLE / PROCEDURE / TRIGGER / VIEW)
T-SQL script from SSMS (TABLE / PROCEDURE / INDEX / ...)
                    │
                    ▼
            ora2pg-gap-report
                    │
                    ▼
   105 confirmed types of ora2pg migration gaps
   ┌──────────────────────────────────────────────────────────────────┐
   │ HIGH    GAP-006  database_link          — @dblink not in PG      │
   │ MEDIUM  GAP-025  invisible_index        — loses invisibility     │
   │ HIGH    GAP-073  mysql_key_index        — mysqldump's KEY breaks │
   │ HIGH    GAP-082  mysql_foreign_key      — FK dropped, no error   │
   │ HIGH    GAP-087  mssql_bracket_identifier — [dbo].[T] breaks all │
   │ HIGH    GAP-089  mssql_update_set       — every UPDATE mangled   │
   └──────────────────────────────────────────────────────────────────┘
```

![ora2pg-gap-report — scanning real PL/SQL code in the terminal](docs/demo.gif)

## 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`](https://github.com/darold/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.

| | |
|---|---|
| **Static analysis** | Looks for patterns in the source code (Oracle, MySQL/MariaDB or T-SQL), no `ora2pg` install required (except `connect_by`, see below) |
| **Reproducible** | Every finding is confirmed by a real `ora2pg` + PostgreSQL run, not by reading the docs |
| **6 output formats** | terminal, markdown, json, csv, `sarif`, `html` — the same set of findings every time |
| **CI gate** | `--fail-on` + SARIF for GitHub/GitLab code scanning |
| **Works offline** | Self-contained bundle for closed networks (`scripts/build_offline_bundle.py`), see below |
| **Baseline** | `--save`/`--baseline` — NEW/RESOLVED/UNCHANGED between runs |
| **Post-migration check** | `--verify` — which pre-migration findings are still present in the generated code; takes its dialect from the baseline (not a functional check, see below) |
| **Interactive mode** | `--tui` (optional, `pip install "ora2pg-gap-report[tui]"`) — browse and click instead of remembering flags |
| **Autofix** | `--fix`/`--write` — three known-safe mechanical fixes for `ora2pg`'s generated code, picked by `--dialect`, see below |

## Detectors

| Detector | What it catches |
|---|---|
| `autonomous_tx` | `PRAGMA AUTONOMOUS_TRANSACTION` inside a `PACKAGE BODY` — ora2pg converts it via dblink, but under-costs or drops the cost entirely in `SHOW_REPORT`/`--estimate_cost` |
| `compound_triggers` | `COMPOUND TRIGGER` — ora2pg's file parser silently returns 0 triggers, with no error at all |
| `dbms_utl_calls` | Classifier for specific `DBMS_*`/`UTL_*` calls — which ones ora2pg actually converts, and which are left as-is |
| `connect_by` | Lints ora2pg's own generated `WITH RECURSIVE` for the `LEVEL` bug. Enabled with `--check-connect-by` and, unlike the others, requires `ora2pg` to be installed |
| `merge_delete_clause` | `MERGE ... WHEN MATCHED THEN UPDATE SET ... DELETE WHERE ...` — a compound Oracle construct with no equivalent in PostgreSQL's MERGE. A plain MERGE without DELETE WHERE isn't flagged — that's fine, it's not a gap |
| `bulk_collect` | Local `TYPE ... IS TABLE OF`, `BULK COLLECT INTO`, `FORALL` — practically never converted by ora2pg. The most common finding in real-world code of any detector in this project |
| `database_link` | `table@dblink_name` — a direct reference to a remote DB via database link. Copied as-is, no equivalent without manually setting up postgres_fdw/dblink |
| `model_clause` | `MODEL PARTITION BY ... DIMENSION BY ... MEASURES ... RULES` — spreadsheet-style computation in SQL. Has no direct equivalent in PostgreSQL at all |
| `pivot_clause` | `PIVOT`/`UNPIVOT` — rotating rows into columns directly in SQL. Copied as-is, PostgreSQL has no built-in equivalent |
| `object_type` | `CREATE TYPE ... AS OBJECT`/`TYPE BODY` — Oracle object types. `--estimate_cost` has no costing mechanism for them at all, not just an underestimate |
| `with_function` | `WITH FUNCTION`/`WITH PROCEDURE` — an inline function inside a query's own WITH clause. ora2pg's parser breaks the source structure, it doesn't just fail to convert it |
| `flashback_query` | `AS OF TIMESTAMP`/`AS OF SCN` — a flashback query. Copied as-is, no equivalent in PostgreSQL at all |
| `global_temp_table` | `CREATE GLOBAL TEMPORARY TABLE` — the `ON COMMIT` clause is dropped entirely, and Oracle's and PostgreSQL's defaults are opposite (a silent behavior change, not an error) |
| `table_partitioning` | `PARTITION BY RANGE/LIST/HASH` — table partitioning is dropped entirely, with no warning at all |
| `connect_by_nocycle` | `CONNECT BY NOCYCLE`/`ORDER SIBLINGS BY` — unlike plain `CONNECT BY`, breaks the structure of the entire surrounding PL/SQL block |
| `context_object` | `CREATE CONTEXT` — an application context (often the basis for VPD) isn't converted at all, leaving only a trace in the DEBUG log |
| `insert_all` | `INSERT ALL`/`INSERT FIRST` — a multi-table insert. Copied as-is, PL/pgSQL fails at body-compilation time |
| `json_table` | `JSON_TABLE(...)` — doesn't exist in PostgreSQL 16 and earlier (it exists in 17, but with a different COLUMNS syntax) |
| `external_table` | `CREATE TABLE ... ORGANIZATION EXTERNAL` — the section is dropped entirely, the table becomes an ordinary, empty one |
| `sql_macro` | `SQL_MACRO` — converted into an ordinary function, fails when called the way it was written to be used |
| `invisible_column` | An `INVISIBLE` column loses its invisibility — silently shows up in `SELECT *` after conversion |
| `collection_type` | `CREATE TYPE ... TABLE OF`/`VARRAY OF` — the collection type vanishes without a trace, dependent tables fail as soon as the DDL is loaded |
| `cross_apply` | `CROSS APPLY`/`OUTER APPLY` — PostgreSQL has no APPLY syntax at all, the closest equivalent is JOIN LATERAL |
| `oracle_text` | Oracle Text — the domain index (`INDEXTYPE IS CTXSYS.*`) is dropped, `CONTAINS`/`CATSEARCH`/`MATCHES` are not carried over |
| `recursive_with` | A native recursive `WITH ... AS (...)` (not via CONNECT BY) missing the `RECURSIVE` keyword that PostgreSQL requires |
| `invisible_index` | An `INVISIBLE` index loses its invisibility to the optimizer — PostgreSQL has no equivalent |
| `read_only_table` | `CREATE TABLE ... READ ONLY` loses its immutability guarantee — INSERT succeeds where Oracle would have reliably blocked it |
| `materialized_view_log` | `CREATE MATERIALIZED VIEW LOG` isn't converted at all, leaving only a trace in the DEBUG log |
| `identity_column` | `GENERATED ... AS IDENTITY (...)` with options — a double-parenthesis substitution bug in ora2pg itself, not a skipped conversion |
| `rowid_type` | `ROWID`/`UROWID` as a column's data type — converted to `oid`, a replacement type incompatible with the data it's supposed to hold |
| `sequence_cycle` | `CREATE SEQUENCE ... CYCLE` — the `CYCLE` section is dropped, `NEXTVAL` fails once the range is exhausted instead of wrapping around |
| `default_on_null` | `DEFAULT ON NULL` is copied verbatim — a syntax error the moment `CREATE TABLE` itself is applied |
| `public_synonym` | `CREATE [PUBLIC] SYNONYM` — loses the target object's schema; when the names match, the result is a self-referencing VIEW |
| `virtual_column` | `GENERATED ALWAYS AS (...) VIRTUAL` — loses the `ORA-54016` protection against explicit assignment; the generated trigger silently overwrites the value |
| `nested_subprogram` | A locally nested procedure/function "leaks" out as a separate object, its containing block disappears, and its body gets corrupted |
| `conditional_compilation` | `$IF`/`$ELSIF`/`$ELSE`/`$END` are copied verbatim — fails on the first call, not at CREATE time |
| `package_state` | A package-level variable — the `set_config`/`current_setting` emulation is broken (no type cast, no `missing_ok`) |
| `index_organized_table` | `ORGANIZATION INDEX` (IOT) is dropped — the table becomes an ordinary heap with a separate index, losing the storage architecture |
| `match_recognize` | `MATCH_RECOGNIZE` — row pattern matching, copied verbatim; PostgreSQL has no equivalent at all, so the DDL fails to load |
| `connect_by_pseudocolumn` | `CONNECT_BY_ROOT`/`CONNECT_BY_ISLEAF`/`CONNECT_BY_ISCYCLE` — carried into the generated recursive CTE unconverted. `SYS_CONNECT_BY_PATH` is deliberately *not* flagged: ora2pg converts that one correctly |
| `keep_dense_rank` | `KEEP (DENSE_RANK FIRST/LAST ORDER BY ...)` — Oracle's aggregate modifier, copied verbatim; no KEEP syntax in PostgreSQL |
| `multiset_operator` | `CAST(MULTISET(...))`, `MULTISET UNION/INTERSECT/EXCEPT`, `MEMBER OF`, `SUBMULTISET OF` — collection operators, none of which exist in PostgreSQL |
| `sample_clause` | `SAMPLE (n)` / `SAMPLE BLOCK (n)` — PostgreSQL has the same capability under different syntax (`TABLESAMPLE`), but ora2pg doesn't translate it |
| `accessible_by` | `ACCESSIBLE BY` — the caller whitelist is copied straight into the generated function header, which PostgreSQL rejects |
| `local_time_zone` | `TIMESTAMP WITH LOCAL TIME ZONE` becomes a bare `timestamp` — the session-time-zone conversion silently disappears (`timestamptz` would be faithful). No error, ever |
| `temporal_validity` | `PERIOD FOR` (Temporal Validity) is mangled into a truncated `period FOR` fragment — breaks the whole `CREATE TABLE`, not just the feature |
| `bitmap_index` | `CREATE BITMAP INDEX` becomes `USING gin`, which PostgreSQL refuses on an ordinary scalar column (no default operator class) — the index isn't created at all |
| `object_table` | `CREATE TABLE ... OF <type>` — `OF` ends up as a *column name* and the constraints are lost. With the type present the load succeeds silently, leaving a structurally wrong table |
| `ignore_nulls` | `IGNORE NULLS` / `RESPECT NULLS` on analytic functions is copied verbatim; PostgreSQL 16 has no such syntax at all, so the query fails to parse |
| `nlssort` | `NLSSORT` becomes a `COLLATE` clause carrying the Oracle language name across — PostgreSQL has no collation by that name, so the query fails at run time |
| `long_raw_type` | `LONG RAW` is mapped to `text` even though ora2pg's own documented default is `LONG RAW:bytea` — binary data then cannot be loaded at all |
| `anydata_type` | `SYS.ANYDATA` / `ANYDATASET` / `ANYTYPE` is copied through as a type name; PostgreSQL has neither the type nor a `SYS` schema |
| `system_trigger` | A trigger `ON DATABASE`/`ON SCHEMA` is emitted as an ordinary table trigger on a table literally named `database`/`schema`, keeping the Oracle event keyword |
| `trigger_follows` | `FOLLOWS`/`PRECEDES` leaks *inside* the generated trigger function's body — the trigger loads cleanly and then breaks every write to the table |
| `table_collection` | The `TABLE(...)` collection-unnesting operator is copied verbatim; PostgreSQL has no such operator |
| `cursor_expression` | `CURSOR(SELECT ...)` is copied verbatim; PostgreSQL has no cursor expressions |
| `for_update_wait` | `FOR UPDATE ... WAIT n` is copied verbatim; PostgreSQL offers only `NOWAIT` and `SKIP LOCKED` there |
| `rownum_dml` | `ROWNUM` in an `UPDATE`/`DELETE` is rewritten to `LIMIT n`, which PostgreSQL does not accept on DML (in a subquery it converts correctly and is not flagged) |
| `to_date_rr` | An `RR` format model inside `TO_DATE` is left in place; PostgreSQL silently returns year 1 BC instead of raising anything — wrong data, no error |
| `authid_clause` | `AUTHID CURRENT_USER`/`DEFINER` makes ora2pg drop the entire routine — no output, no error, not even a DEBUG log line |
| `pragma_exception_init` | `PRAGMA EXCEPTION_INIT` handlers all collapse onto `SQLSTATE '50001'`, which PostgreSQL never raises — the handler becomes dead code and the error escapes |
| `subtype_range` | `SUBTYPE ... RANGE lo .. hi` is carried into `CREATE DOMAIN` verbatim, and PostgreSQL's `CREATE DOMAIN` has no `RANGE` clause |
| `alt_quote_literal` | Oracle's `q'[...]'` alternative quoting is copied verbatim; PostgreSQL parses `q` as an identifier and the rest of the statement derails |
| `goto_statement` | `GOTO` is copied verbatim; PL/pgSQL has no `GOTO` at all |
| `cursor_rowtype` | `<cursor>%ROWTYPE` is copied verbatim; PL/pgSQL allows `%ROWTYPE` only against a table or view (plain `<table>%ROWTYPE` converts fine and is not flagged) |
| `wm_concat` | `WM_CONCAT` is copied verbatim — unlike `LISTAGG`, which ora2pg does rewrite to `string_agg` |
| `read_only_view` | `WITH READ ONLY` is dropped; the resulting PostgreSQL view is auto-updatable, so writes Oracle rejected now silently succeed |
| `sdo_geometry` | `SDO_GEOMETRY` becomes the PostGIS `geometry` type with no `CREATE EXTENSION postgis` emitted — the DDL fails to load on a stock server |

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

| Detector | What it catches |
|---|---|
| `mysql_enum_type` | `ENUM(...)` — ora2pg synthesizes a named PostgreSQL type for it but never emits the `CREATE TYPE ... AS ENUM (...)` that type needs; `CREATE TABLE` fails to load |
| `mysql_on_update_current_timestamp` | `DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP` — the `ON UPDATE ...` fragment is copied verbatim into `DEFAULT`, which PostgreSQL's `DEFAULT` has no syntax for at all |
| `mysql_on_duplicate_key_update` | `INSERT ... ON DUPLICATE KEY UPDATE` — copied verbatim into the function/procedure body; PostgreSQL's `INSERT` has no such clause, fails on first call |
| `mysql_signal` | `SIGNAL`/`RESIGNAL` — copied verbatim; neither exists in PL/pgSQL, fails on first call |
| `mysql_fulltext_index` | `FULLTEXT KEY`/`FULLTEXT INDEX` inside `CREATE TABLE`'s column list — not recognized as an index at all; the bare keywords are left where a column definition was expected, and `CREATE TABLE` fails to load |
| `mysql_key_index` | `KEY <name> (<cols>)` — mysqldump's own default spelling for a secondary index. Left as a `key <NAME>` stub where a column was expected, so `CREATE TABLE` fails to load. The `INDEX` synonym and `UNIQUE KEY` both convert fine |
| `mysql_spatial_index` | `SPATIAL KEY`/`SPATIAL INDEX` — same shape as FULLTEXT, but restored as a GiST index over a PostGIS type |
| `mysql_limit_comma` | `LIMIT offset, count` — copied verbatim; PostgreSQL rejects the comma form outright (`LIMIT #,# syntax is not supported`) |
| `mysql_replace_into` | `REPLACE INTO` — copied verbatim; no PostgreSQL equivalent, and `ON CONFLICT DO UPDATE` is not a literal substitute (REPLACE deletes, so delete-side cascades fire) |
| `mysql_insert_ignore` | `INSERT IGNORE` — copied verbatim; `ON CONFLICT DO NOTHING` is narrower than what IGNORE actually suppresses |
| `mysql_prepare_from` | `PREPARE <name> FROM <string>` — PostgreSQL spells its own PREPARE differently (`AS <query>`, not a string variable); PL/pgSQL's `EXECUTE` is the real equivalent |
| `mysql_last_insert_id` | `LAST_INSERT_ID()` — copied verbatim; no such function in PostgreSQL |
| `mysql_auto_increment_start` | `AUTO_INCREMENT=<n>` table option — the column becomes `serial` correctly but the starting value is lost, so the sequence restarts at 1 and the first insert after a data migration collides on the primary key |
| `mysql_date_format` | `DATE_FORMAT(...)` — emitted as a bare row constructor with the `to_char` name missing and `%d` untranslated. Nothing errors at any stage; the query just silently returns a tuple instead of a formatted string |
| `mysql_foreign_key` | `FOREIGN KEY` — dropped entirely (both the named-CONSTRAINT and bare forms), and ora2pg has no foreign-key export type at all. No error ever: referential integrity and cascades just cease to exist |
| `mysql_zero_date` | `'0000-00-00'` — MySQL's "not set" marker is silently rewritten to a real `'1970-01-01'`, so unfilled-date queries stop matching and reports start showing 1970 as an event |
| `mysql_declare_handler` | `DECLARE ... HANDLER` — dropped with no `EXCEPTION` block in its place, so a routine's whole error-handling policy disappears: what MySQL swallowed now aborts the caller's transaction |
| `mysql_collate` | `COLLATE`/`CHARACTER SET` on a column — dropped. MySQL's usual `*_ci` rules are case-insensitive, PostgreSQL's default is not, so queries silently start returning different rows |
| `mysql_set_type` | `SET(...)` — becomes plain `text`. The only `medium` of the MySQL batch: the schema works and existing data survives, but nothing validates future writes |

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

| Detector | What it catches |
|---|---|
| `mssql_bracket_identifier` | `[dbo].[Orders]`, `[Id]`, `[int]` — the brackets SSMS emits for every name are never stripped on the file-based path; they end up inside the generated identifier and inside type names, and the DDL fails to load. The widest-reaching gap of the batch |
| `mssql_newid_default` | `NEWID()` — mapped onto `uuid_generate_v4()` with no `CREATE EXTENSION "uuid-ossp"` emitted, so `CREATE TABLE` fails to load |
| `mssql_update_set` | `UPDATE ... SET` — mistaken for T-SQL's variable-assignment `SET`: the keyword is deleted and `=` becomes `:=`, breaking every UPDATE in every procedure |
| `mssql_identity_column` | `IDENTITY(1,1)` — dropped entirely (no serial, no sequence), so the first ordinary insert fails on NOT NULL |
| `mssql_parameterless_procedure` | A procedure with no parameters gets an unparseable empty `DECLARE ;` block — verified by A/B against the same procedure with a parameter, which comes out clean |
| `mssql_if_statement` | `IF` — with a `BEGIN/END` block it gets `THEN` but never `END IF`; without one it gets no `THEN` at all |
| `mssql_raiserror` | `RAISERROR`/`THROW` — copied verbatim; PL/pgSQL has neither |
| `mssql_try_catch` | `BEGIN TRY`/`BEGIN CATCH` — copied verbatim, `END TRY`/`END CATCH` included |
| `mssql_top_clause` | `SELECT TOP n` — copied verbatim; PostgreSQL has no TOP |
| `mssql_scope_identity` | `SCOPE_IDENTITY()`/`@@IDENTITY`/`IDENT_CURRENT()` — copied verbatim |
| `mssql_output_clause` | `OUTPUT INSERTED.*` — copied verbatim; `RETURNING` is the equivalent, and not an exact one |
| `mssql_iif` | `IIF()` — copied verbatim, while the neighbouring CHARINDEX in the same statement does get translated |
| `mssql_datediff` | `DATEDIFF()` — copied verbatim, though `DATEADD` and `DATEPART` beside it convert correctly |
| `mssql_charindex` | `CHARINDEX()` — translated into `position()`, but with the quotes doubled: `position(''abc'' in x)`, which is not valid SQL |
| `mssql_filtered_index` | `CREATE INDEX ... WHERE` — dropped entirely, even though PostgreSQL has partial indexes with the same syntax (an `INCLUDE` index beside it converts fine) |
| `mssql_foreign_key` | `FOREIGN KEY` — dropped entirely, exactly as on the MySQL side; no error at any stage |
| `mssql_collation` | `COLLATE` — dropped, every string column becomes case-insensitive `citext`; for a `_CS_` source collation that inverts comparison behaviour, verified on live data |
| `mssql_computed_column` | A computed column (`AS (expr) PERSISTED`) is typed `citext` whatever the expression computes, so a numeric result is stored as text |
| `mssql_rowversion` | `ROWVERSION` → `bytea`, which never self-updates, so optimistic-locking checks silently stop detecting conflicts |

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 105 registered gaps (`gap_registry.py`) — 67 from the Oracle source
dialect, 19 from MySQL/MariaDB (`dialect="mysql"`, `ora2pg -m`) and 19 from
T-SQL/SQL Server (`dialect="mssql"`, `ora2pg -M`); see "Source dialects"
below — 99 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 105, `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`](docs/research/GAP_REGISTRY.md) — each
entry states which detector covers it and against which `ora2pg` version it
was confirmed. [`docs/research/AUDIT.md`](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

```sh
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`](https://github.com/Textualize/rich), purely for a pleasant terminal
output; it installs itself via `pip install`.

Right after installation, the command is available:

```sh
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):

```sh
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`](schemas/report.schema.json) (and the
baseline-snapshot format from `--save`/`--baseline` is in
[`schemas/baseline.schema.json`](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):

```sh
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):

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

![ora2pg-gap-report --tui — scanning, a baseline diff, and a finding's full explanation](docs/tui_demo.gif)

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:

```sh
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.

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

```sh
ora2pg-gap-report schema/                        # Oracle (the default)
ora2pg-gap-report --dialect mysql mysqldump.sql  # GAP-068..086
ora2pg-gap-report --dialect mssql ssms.sql       # GAP-087..105
```

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`.** MySQL
  deliberately has none (see below), and says 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. 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):

```sh
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`:

```sh
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 | Meaning |
| --- | --- |
| `0` | The scan finished and the gate (if any) passed |
| `1` | `--fail-on` gate failed — findings at or above the threshold |
| `2` | Bad usage, or some input couldn't be scanned (missing/unreadable file, empty directory, broken baseline) |
| `3` | Internal error — a bug in this tool, not a migration finding. The scan continues past a crashing detector and still reports everything else, but the run is incomplete and `--save` is skipped |

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`](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`](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**:

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

```text
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`](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: only corrections where the "buggy" shape
is never what a correct migration would produce and the fix is a pure,
unambiguous text transformation. Three qualify so far, and which of them
run is decided by `--dialect`:

| Dialect | Fix | What it undoes |
|---|---|---|
| `oracle` | GAP-028 | `ora2pg` wraps an identity column's sequence options in an extra, redundant pair of parens (`GENERATED ALWAYS AS IDENTITY ((START WITH 1))`), which won't load. Strips exactly that outer pair |
| `mssql` | GAP-100 | `CHARINDEX` is translated to the right function but with the quotes doubled — `position(''abc'' in x)`, which is not valid SQL. Removes the doubling, touching nothing else |
| `mssql` | GAP-091 | A parameterless procedure gets an empty, unparseable `DECLARE ;` block. Deletes it — which is exactly what `ora2pg` itself emits for the same procedure when it takes a parameter |
| `mysql` | — | None, deliberately: every confirmed MySQL gap needs either a design decision (what to replace the construct with) or data the generated file no longer carries |

All three 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.

```sh
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. 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/`](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:

```sh
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:

```sh
# 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

```sh
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`](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`](docs/DEVELOPMENT.md). How to submit a finding or a
PR — in [`CONTRIBUTING.md`](CONTRIBUTING.md), code of conduct — in
[`CODE_OF_CONDUCT.md`](CODE_OF_CONDUCT.md), how to report a vulnerability —
in [`SECURITY.md`](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`](ROADMAP.md).

(These deeper docs are currently in Russian only.)

## Changelog

Version history — [CHANGELOG.md](CHANGELOG.md).

## License

Apache 2.0, see [LICENSE](LICENSE).
