pgrecon 0.6.0: grants, comments, and a namespace map

The schema now travels with its paperwork. Extraction captures object grants, table and column comments, and the database character set in both script tiers; the converter emits COMMENT ON for every surviving object and GRANT statements with their roles bootstrapped idempotently. Oracle READ maps to SELECT, sequence privileges to USAGE and UPDATE, and everything without a direct counterpart - EXECUTE included - is a named residue line, because PostgreSQL grants on routines need argument lists.

Every emitted name now passes through a namespace map that mirrors the target's. PostgreSQL keeps tables, views, materialized views, sequences, indexes, and key-backing indexes in one namespace per schema and truncates identifiers to 63 bytes; Oracle divides its namespaces differently and allows 128-byte names from 12.2, so a legal source schema can hold pairs that fold to a single name on the target. The first claimant wins and every later collider becomes a residue line naming the earlier object - including where CREATE OR REPLACE would otherwise replace it silently.

The runbook writes apply_schema.sh, the ordered apply script with an error log. The rule catalog reaches 76. A malformed spool row now degrades instead of crashing a load. And the claim on the tin got a harness: CI applies the bundled sample's conversion to a live PostgreSQL 16 on every commit - the release you are reading passed it.

Everything here was applied to a live PostgreSQL before tagging, per the standing rule: nothing invalid ships, nothing is lost silently.

pgrecon 0.5.0

Materialized views convert for real when the dump carries their defining query: CREATE MATERIALIZED VIEW with the container's column names, the query through the same translation and guards as views, and notes for the refresh method and query rewrite. Constraints, foreign keys, and triggers that target a materialized view refuse by name - PostgreSQL does not allow them there. Query-less dumps keep the container-table behavior of 0.4.

Ten rules from the field close the gap catalog at 74: MODEL, PIVOT/UNPIVOT, flashback queries, multi-table INSERT, WITH FUNCTION, SQL macros, invisible columns, read-only tables, DEFAULT ON NULL, and materialized view logs. The SQL constructs detect at the token level, so a PIVOT in a comment stays silent.

pgrecon runbook generates the data-movement artifacts from the inventory alone: a data-only ora2pg configuration, row-count and spot-sum validation SQL for both engines, post-load sequence alignment, materialized view refresh and ANALYZE steps, and the cutover checklist. The tool still never connects to a database; it directs the move instead.

The extraction scripts capture license posture (edition banner, cpu counts, DBA_FEATURE_USAGE_STATISTICS under SELECT_CATALOG_ROLE, degrading to empty files without it), materialized view defining queries, and complete partition HIGH_VALUE in both script tiers.

As with every release, the converted output was applied to a live PostgreSQL 16 before tagging: both fact-bearing benchmark schemas at zero errors, the materialized view refreshed and read back, and a row inserted through a converted interval-partition bound landing in the correct child.

pgrecon 0.4.0

Hierarchical queries come home, and Oracle's empty-string rules hold in two more places.

CONNECT BY views convert to WITH RECURSIVE: one table, one PRIOR equality, plain-column projections, LEVEL, and SYS_CONNECT_BY_PATH with a literal separator. START WITH filters the base branch, a WHERE applies after the hierarchy exactly as Oracle evaluates it, and a hidden key column carries the parent join so the projection list does not have to. NOCYCLE, ORDER SIBLINGS BY, joins, and PRIOR over expressions refuse by name. The conversion was verified row-for-row against a live Oracle database running the original view - same rows, same LEVEL values, same paths, byte for byte.

DECODE translates with Oracle's null rules intact. In Oracle an empty-string argument is NULL, so DECODE(x, '', a, b) must test x IS NULL - a comparison against '' could never match. Literal searches keep plain equality, and a column search carries the both-NULL match DECODE gives it. Views over JSON_TABLE now refuse by name - PostgreSQL adds JSON_TABLE in version 17 with different clause syntax, and the residue line says so instead of surfacing a parser error.

Materialized views stop converting silently: the dictionary hands them over as container tables, so the table still converts and a residue line names the loss - the defining query, the refresh schedule, and the absence of query rewrite in PostgreSQL.

As always: across the nine-schema comparison lab the converter emitted zero statements PostgreSQL rejected; everything it cannot prove is a named line in the residue report.

pgrecon 0.3.0

Triggers convert, concatenation keeps Oracle's NULL semantics, and the emitter is hardened by a nine-schema live sweep.

Triggers: a simple DML trigger becomes a trigger function plus the CREATE TRIGGER statement PostgreSQL wants. :NEW and :OLD lose their colons, INSERTING/UPDATING/DELETING become TG_OP tests, bare RETURN gains the row result, UPDATE OF column lists survive from the parse tree, WHEN clauses translate through the same folding as views - including the shape PostgreSQL refuses at CREATE, a WHEN reading NEW on a DELETE trigger, which moves into the body as a guard. Disabled triggers stay disabled. Compound, INSTEAD OF, system triggers, and UPDATING('column') refuse by name into the residue report.

Concatenation: every || chain becomes NULLIF(concat(...), ''). Oracle treats NULL as the empty string where PostgreSQL || yields NULL; concat() ignores NULLs, and NULLIF restores the one case Oracle does return NULL - every part empty. Inner rewrites compose inside the operands, and Oracle's CONCAT() folds the same way, in code, triggers, views, checks, defaults, and WHEN clauses.

Hardening from running the converter across nine schemas - Oracle's HR, OE, and customer_orders samples, utPLSQL, PLJSON, Logger, Alexandria, and two migration corpora - with every output applied to live PostgreSQL 16 under check_function_bodies: identity columns become integer identity columns and widen their foreign key references with them; SYSTIMESTAMP and GROUPING_ID fold to their PostgreSQL forms; SYS_OP_* internals refuse; I_SNAP$ snapshot indexes skip with a note; object views refuse by name; divisions fold to decimal so two-argument ROUND survives; constraints and indexes named after their table gain a suffix instead of colliding in PostgreSQL's relation namespace; database links ship as a commented oracle_fdw recipe. Across all nine schemas the converter emitted zero statements PostgreSQL rejected; everything it cannot prove is a named line in the residue report.

pgrecon 0.2.1

Code-lane refinements, all proven on a live PostgreSQL 16 before tagging.

• Bare procedure-call statements become CALL statements with the
  parentheses CALL requires, resolved against the extracted
  procedures.
• EXIT WHEN cursor%NOTFOUND directly after a FETCH of the same cursor
  becomes EXIT WHEN NOT FOUND, which is what plpgsql's FOUND reports
  at that point. This converts the classic OPEN/FETCH/EXIT/CLOSE
  cursor loop mechanically.
• :name bind placeholders inside EXECUTE IMMEDIATE literals fold to
  numbered parameters when their count matches the USING arity;
  Oracle binds them by position exactly as PostgreSQL numbers them.
  OUT bind arguments refuse; mismatched counts stay verbatim under
  the standing verify-by-hand note.
• Sized character declarations in code (VARCHAR2(200) and friends)
  now map; the size pattern never matched type names containing a
  digit, so they were refused as unsupported.

See CHANGELOG.md for details.

pgrecon 0.2.0

pgrecon converts now.

• pgrecon convert emits PostgreSQL DDL from the inventory, offline:
  tables under a documented type mapping, primary and unique keys,
  checks, foreign keys, and secondary indexes; everything the
  converter cannot port faithfully lands in a residue report naming
  the object and the reason instead of becoming wrong DDL.
• Partition bounds are extracted (HIGH_VALUE through the same chunked
  path as check conditions, subpartitions included) and converted to
  native partition children: RANGE with MAXVALUE, LIST with DEFAULT,
  HASH with MODULUS/REMAINDER, and composite shapes. Interval-driven
  creation carries a note pointing at scheduled creation such as
  pg_partman.
• The rest of schema structure converts: sequences restarted at their
  extracted position with bigint-safe bounds, schema-local synonyms
  as updatable views, database links scaffolded as oracle_fdw
  servers, column defaults, and virtual columns as generated columns.
• The converter grows a code lane: standalone functions and
  procedures whose every construct is provably equivalent convert
  mechanically to PL/pgSQL, comments and formatting carried through.
  SELECT INTO becomes INTO STRICT so NO_DATA_FOUND still raises;
  cursor%ROWTYPE becomes a record variable; NVL, SYSDATE, sequence
  NEXTVAL, DBMS_OUTPUT.PUT_LINE, EXECUTE IMMEDIATE, q-quoted
  literals, and FROM DUAL all translate. Everything semantic refuses
  by name and line into the residue report, and a routine that calls
  a refused routine is refused with it. Function bodies are validated
  by PostgreSQL itself: the emitted DDL applies with
  check_function_bodies on, never disabled.
• Views transpile with (+) outer joins folded to ANSI; CONNECT BY,
  database-link reads, and object-relational methods refuse with
  named reasons.

The full output of the test estates applies to a live PostgreSQL 16
at zero errors, and the converted routines were executed there to
confirm behavior. See CHANGELOG.md for details.

pgrecon 0.1.5

Conditional compilation is handled and flagged (R-SRC-21): $IF directives are blanked before parsing so every branch's code stays analyzed, inquiry references such as $$plsql_unit parse as expressions, and the directive itself becomes a finding, because the port has to pick a branch. 64 rules.

Object-type methods with DEFAULT parameter values and type declarations carrying OID identity clauses no longer count as parse failures; both are legal Oracle the vendored grammar predates. Found by feeding pljson, Logger, utPLSQL, and the legacy Oracle sample schemas through the tool.

pgrecon 0.1.4

Fixes from an independent code review; thanks to its author.

• Indexes whose GENERATED flag arrives NULL from a partial dump no
  longer silently escape the function-based and global-index rules.
• The effort baseline no longer scales per table: environments and
  cutover scaffolding do not grow linearly with table count, and at
  estate scale the old rate produced an indefensible number. The
  baseline is flat; per-object work stays in schema conversion where
  it belongs.
• The effort rates are described as what they are: the author's
  field defaults, uncited and visible, to be calibrated per
  engagement.
• The import boundary check now also covers the commercial report
  package, so deterministic code cannot grow a dependency on it.
• orafce is credited on the rules it genuinely helps: UTL_FILE,
  DBMS_OUTPUT, and DECODE.
• A hand-mangled numeric field in a dump degrades to a missing value
  instead of aborting the load, and pgrecon load says out loud that
  it replaces an existing database file.
• The SQL% fallback grep no longer matches PLSQL%.

pgrecon 0.1.3

• Wrapped PL/SQL gets its own rule (R-SRC-20): a wrapped unit is
  obfuscated bytecode with nothing to assess from the database, so it
  is reported as needing its original source instead of producing a
  parse failure and garbage token matches. 63 rules.
• Assessment scale is measured: a synthetic estate of 5,000 tables
  and 100,000 lines of PL/SQL across 1,600 stored units, including a
  16,000-line package body, loads and deep-parses in under two
  minutes and reports in seconds. tools/make_scale_dump.py generates
  the estate, so the measurement is reproducible.

pgrecon 0.1.2

• Table DDL as DBMS_METADATA emits it in the field now parses:
  spelled-out partition specification lists, DEFERRABLE constraint
  states, INTERVAL column types with spaced precision, LONG RAW, and
  virtual column visibility markers no longer count as parse
  failures. Found by assessing real extracted corpora, including
  Oracle's official sample schemas.
• Evolved object types are handled and flagged: DBA_SOURCE lists the
  CREATE and the ALTER TYPE statements that changed it in one
  listing, which used to defeat the parse. The original definition
  now parses alone and the evolution is a finding of its own
  (R-OBJ-09), because in-place type evolution has no PostgreSQL
  equivalent. 62 rules.
• XDB's machine-generated helper triggers (PURCHASEORDER$xd and
  friends) are recorded as generated instead of parse failures; they
  are Oracle's machinery, not user code to port.