1---2name: reviewing-oracle-to-postgres-migration-23description: Review Oracle-to-PostgreSQL migration plans or completed artifacts for behavioral risks: empty strings, exceptions, refcursors, type coercion, sorting and collations, UNION ALL planner changes, materialized-view refresh, timestamps, sequences, ROWNUM, NVL/DECODE, and concurrent transactions. Use when planning or validating database migrations and integration tests.4---56# Oracle-to-PostgreSQL migration review78Cross-reference migration scope against the bundled Oracle/PostgreSQL behavior references, identify risks by cross-referencing before implementation, and validate post-migration work against integration-test evidence.910## When to invoke1112- "Review this Oracle to PostgreSQL migration plan."13- "Validate that our migrated procedure preserves Oracle behavior."14- "Which Oracle/Postgres differences apply to this query?"15- "Check integration tests for Oracle-to-PostgreSQL edge cases."16- "Assess refcursor, timestamp, or UNION ALL migration risk."1718## Procedure19201. Classify the task as planning or validation.212. Identify the migration scope: procedures, triggers, queries, views, materialized views, and application callers.223. Read `references/REFERENCE.md` as the index, then open only the reference files whose behavior appears in scope.234. Map each applicable insight to a concrete risk, required code change, and required integration test.245. For validation, verify migrated artifacts, scripts, and tests prove the new PostgreSQL semantics.256. Gate the result with a risk table and an explicit pass/fix verdict.2627## Risk assessment workflow2829| Step | Action | Evidence to capture |30| --- | --- | --- |31| Identify scope | List affected database objects and caller code. | Object names, SQL files, application classes, refcursor clients. |32| Screen insights | Compare the scope to every entry in `references/REFERENCE.md`. | Applicable reference filenames and skipped references with reason. |33| Decide semantics | Flag behavior choices such as Oracle empty-string-as-NULL versus native PostgreSQL empty string behavior. | Decision owner or default recommendation. |34| Recommend fixes | Apply patterns from the specific reference file. | Query rewrite, function rewrite, test requirement, or design decision. |3536## Validation workflow3738| Step | Action | Gate |39| --- | --- | --- |40| Map artifact | Summarize the migrated object and change set. | Every changed procedure, trigger, query, view, or caller is named. |41| Cross-check insights | Confirm each applicable reference behavior is acknowledged and addressed. | No applicable insight is left as "not reviewed". |42| Verify tests | Confirm happy paths and failure paths exercise PostgreSQL behavior. | Integration tests cover exceptions, sorting, `UNION ALL`, refcursor consumption, concurrent transactions, timestamps, and materialized-view freshness when applicable. |43| Gate result | Report pass, conditional pass, or fix required. | Migration scripts run and tests pass, or blockers are explicit. |4445## Behavior risk catalog4647| Risk area | Look for | Bundled reference |48| --- | --- | --- |49| Empty strings | Oracle treats `''` like `NULL`; PostgreSQL does not. | `references/empty-strings-handling.md` |50| No data exceptions | Oracle `NO_DATA_FOUND` exception flow may become empty result handling. | `references/no-data-found-exceptions.md` |51| `NVL` and `DECODE` | Oracle-specific functions need PostgreSQL-safe equivalents. | `references/oracle-nvl-decode-functions.md` |52| Parenthesized `FROM` | Oracle join syntax and parentheses can change semantics. | `references/oracle-parentheses-from-clause.md` |53| Pagination | `ROWNUM` patterns need PostgreSQL pagination or window functions. | `references/oracle-rownum-pagination.md` |54| Sequences and `DUAL` | `SYSDATE`, sequences, and `DUAL` need PostgreSQL replacements. | `references/oracle-sysdate-sequences-dual.md` |55| Sorting and collations | NULL ordering, sorting/collations, collation, and case behavior may differ. | `references/oracle-to-postgres-sorting.md` |56| Timestamps | Time zone and precision semantics may change. | `references/oracle-to-postgres-timestamp-timezone.md` |57| Numeric formatting | `TO_CHAR` numeric output can differ. | `references/oracle-to-postgres-to-char-numeric.md` |58| Type coercion | Implicit casts accepted by Oracle may fail or change plans in PostgreSQL. | `references/oracle-to-postgres-type-coercion.md` |59| Concurrent transactions | Locking and isolation behavior may differ. | `references/postgres-concurrent-transactions.md` |60| Materialized views | Refresh requirements and freshness expectations must be explicit. | `references/postgres-materialized-view-refresh.md` |61| Refcursors | Application consumption and transaction scope must match PostgreSQL behavior. | `references/postgres-refcursor-handling.md` |62| `UNION ALL` planner | PostgreSQL planner choices may change behavior/performance or ordering assumptions. | `references/postgres-union-all-planner.md` |6364## Progressive disclosure and bundled resources6566- `references/REFERENCE.md`: index of all migration insights; read this first from the `references/` bundle.67- `references/*.md`: detailed behavior-specific guidance; read only the files that match the migration scope.6869## Gotchas7071- **Do not treat syntax conversion as semantic equivalence**: the migration can compile and still mishandle empty strings, timestamps, or exceptions.72- **Do not skip caller code**: refcursor handling, transaction boundaries, and materialized-view freshness often fail in application code, not only SQL.73- **Do not rely on unit tests alone**: database migration risks require integration tests against PostgreSQL behavior.7475## Output template7677```markdown78## Oracle-to-PostgreSQL migration review7980**Status:** pass | conditional pass | fix required81**Mode:** planning | validation82**Scope:** <objects, queries, views, and callers reviewed>8384| Insight | Applies? | Risk | Required action | Test evidence |85| --- | --- | --- | --- | --- |86| `references/<file>.md` | yes/no | <behavior difference> | <rewrite, design decision, or none> | <test name/result or missing> |8788### Migration gate89- Scripts run: pass | fail | not checked90- Integration tests: pass | fail | missing91- Blocking decisions: <none or list>92```9394## Quality gate9596- [ ] `references/REFERENCE.md` was used as the index before opening detailed references.97- [ ] Every affected procedure, trigger, query, view, materialized view, and caller is listed or explicitly out of scope.98- [ ] Applicable differences include empty strings, exceptions, sorting, `UNION ALL`, refcursors, concurrent transactions, timestamps, and materialized-view refresh where relevant.99- [ ] Planning output includes risks and recommended actions; validation output includes test evidence.100- [ ] Integration tests cover both happy path and failure scenarios for every applicable behavior difference.101- [ ] The final verdict is pass, conditional pass, or fix required.
paulasilvatech