1---2name: reviewing-oracle-to-postgres-migration3description: 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<!-- Generated from harness/github-copilot/skills/reviewing-oracle-to-postgres-migration/SKILL.md by harness/claude-code/scripts/convert_from_copilot.py. Edit the source, not this file. -->78# Oracle-to-PostgreSQL migration review910Cross-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.1112## When to invoke1314- "Review this Oracle to PostgreSQL migration plan."15- "Validate that our migrated procedure preserves Oracle behavior."16- "Which Oracle/Postgres differences apply to this query?"17- "Check integration tests for Oracle-to-PostgreSQL edge cases."18- "Assess refcursor, timestamp, or UNION ALL migration risk."1920## Procedure21221. Classify the task as planning or validation.232. Identify the migration scope: procedures, triggers, queries, views, materialized views, and application callers.243. Read `references/REFERENCE.md` as the index, then open only the reference files whose behavior appears in scope.254. Map each applicable insight to a concrete risk, required code change, and required integration test.265. For validation, verify migrated artifacts, scripts, and tests prove the new PostgreSQL semantics.276. Gate the result with a risk table and an explicit pass/fix verdict.2829## Risk assessment workflow3031| Step | Action | Evidence to capture |32| --- | --- | --- |33| Identify scope | List affected database objects and caller code. | Object names, SQL files, application classes, refcursor clients. |34| Screen insights | Compare the scope to every entry in `references/REFERENCE.md`. | Applicable reference filenames and skipped references with reason. |35| Decide semantics | Flag behavior choices such as Oracle empty-string-as-NULL versus native PostgreSQL empty string behavior. | Decision owner or default recommendation. |36| Recommend fixes | Apply patterns from the specific reference file. | Query rewrite, function rewrite, test requirement, or design decision. |3738## Validation workflow3940| Step | Action | Gate |41| --- | --- | --- |42| Map artifact | Summarize the migrated object and change set. | Every changed procedure, trigger, query, view, or caller is named. |43| Cross-check insights | Confirm each applicable reference behavior is acknowledged and addressed. | No applicable insight is left as "not reviewed". |44| 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. |45| Gate result | Report pass, conditional pass, or fix required. | Migration scripts run and tests pass, or blockers are explicit. |4647## Behavior risk catalog4849| Risk area | Look for | Bundled reference |50| --- | --- | --- |51| Empty strings | Oracle treats `''` like `NULL`; PostgreSQL does not. | `references/empty-strings-handling.md` |52| No data exceptions | Oracle `NO_DATA_FOUND` exception flow may become empty result handling. | `references/no-data-found-exceptions.md` |53| `NVL` and `DECODE` | Oracle-specific functions need PostgreSQL-safe equivalents. | `references/oracle-nvl-decode-functions.md` |54| Parenthesized `FROM` | Oracle join syntax and parentheses can change semantics. | `references/oracle-parentheses-from-clause.md` |55| Pagination | `ROWNUM` patterns need PostgreSQL pagination or window functions. | `references/oracle-rownum-pagination.md` |56| Sequences and `DUAL` | `SYSDATE`, sequences, and `DUAL` need PostgreSQL replacements. | `references/oracle-sysdate-sequences-dual.md` |57| Sorting and collations | NULL ordering, sorting/collations, collation, and case behavior may differ. | `references/oracle-to-postgres-sorting.md` |58| Timestamps | Time zone and precision semantics may change. | `references/oracle-to-postgres-timestamp-timezone.md` |59| Numeric formatting | `TO_CHAR` numeric output can differ. | `references/oracle-to-postgres-to-char-numeric.md` |60| Type coercion | Implicit casts accepted by Oracle may fail or change plans in PostgreSQL. | `references/oracle-to-postgres-type-coercion.md` |61| Concurrent transactions | Locking and isolation behavior may differ. | `references/postgres-concurrent-transactions.md` |62| Materialized views | Refresh requirements and freshness expectations must be explicit. | `references/postgres-materialized-view-refresh.md` |63| Refcursors | Application consumption and transaction scope must match PostgreSQL behavior. | `references/postgres-refcursor-handling.md` |64| `UNION ALL` planner | PostgreSQL planner choices may change behavior/performance or ordering assumptions. | `references/postgres-union-all-planner.md` |6566## Progressive disclosure and bundled resources6768- `references/REFERENCE.md`: index of all migration insights; read this first from the `references/` bundle.69- `references/*.md`: detailed behavior-specific guidance; read only the files that match the migration scope.7071## Gotchas7273- **Do not treat syntax conversion as semantic equivalence**: the migration can compile and still mishandle empty strings, timestamps, or exceptions.74- **Do not skip caller code**: refcursor handling, transaction boundaries, and materialized-view freshness often fail in application code, not only SQL.75- **Do not rely on unit tests alone**: database migration risks require integration tests against PostgreSQL behavior.7677## Output template7879```markdown80## Oracle-to-PostgreSQL migration review8182**Status:** pass | conditional pass | fix required83**Mode:** planning | validation84**Scope:** <objects, queries, views, and callers reviewed>8586| Insight | Applies? | Risk | Required action | Test evidence |87| --- | --- | --- | --- | --- |88| `references/<file>.md` | yes/no | <behavior difference> | <rewrite, design decision, or none> | <test name/result or missing> |8990### Migration gate91- Scripts run: pass | fail | not checked92- Integration tests: pass | fail | missing93- Blocking decisions: <none or list>94```9596## Quality gate9798- [ ] `references/REFERENCE.md` was used as the index before opening detailed references.99- [ ] Every affected procedure, trigger, query, view, materialized view, and caller is listed or explicitly out of scope.100- [ ] Applicable differences include empty strings, exceptions, sorting, `UNION ALL`, refcursors, concurrent transactions, timestamps, and materialized-view refresh where relevant.101- [ ] Planning output includes risks and recommended actions; validation output includes test evidence.102- [ ] Integration tests cover both happy path and failure scenarios for every applicable behavior difference.103- [ ] The final verdict is pass, conditional pass, or fix required.
paulasilvatech