AutoGineer
Generated banner for Reviewing Oracle To Postgres Migration
Generated logo for Reviewing Oracle To Postgres Migration

Reviewing Oracle To Postgres Migration

GitHubOfficialFirst committed by PrimedPaulDistributed by GitHub

  • Skill
  • Workflow
  • Software development
  • MIT
  • No issues found
Download ZIP

From the author

Identifies Oracle-to-PostgreSQL migration risks by cross-referencing code against known behavioral differences (empty strings, refcursors, type coercion, sorting/collations, UNION ALL planner risks, materialized-view refresh requirements, timestamps, concurrent transactions, etc.). Use when planning a database migration, reviewing migration artifacts, or validating that integration tests cover Oracle/PostgreSQL differences.
— the description by PrimedPaul

AutoGineer summary

Written by AutoGineer from the skill’s own files, not by the author

This skill provides a systematic approach to managing database migrations from Oracle to PostgreSQL. It centers on a reference-based workflow that helps developers identify behavioral differences—such as empty string handling, type coercion, and pagination logic—during the planning phase and verify that these risks are addressed during the validation phase. By cross-referencing migration artifacts against known technical discrepancies, it ensures that application logic and database schemas are correctly adapted for the PostgreSQL environment.

What it does

  • Provides a risk assessment workflow for planning database migrations.
  • Offers a validation workflow to confirm migration work meets PostgreSQL requirements.
  • Maintains a library of known behavioral differences between Oracle and PostgreSQL.
  • Guides users through documenting risks and recommended fix patterns.
  • Ensures integration tests cover critical Oracle-to-PostgreSQL behavioral gaps.
  • Supports decision-making for handling specific database object migrations.

When to use it

  • When planning the migration of database objects like procedures, triggers, or views.
  • When reviewing completed migration artifacts to ensure all behavioral risks are mitigated.
  • When validating that integration tests adequately cover Oracle-to-PostgreSQL differences.
  • When you need a structured checklist to gate the success of a database migration task.

Advantages

  • Reduces the risk of runtime errors caused by subtle database behavioral differences.
  • Standardizes the migration process across different database objects.
  • Provides clear, actionable guidance for common migration pitfalls.
  • Facilitates thorough testing by highlighting specific scenarios that require validation.

Usage examples

“I am planning to migrate a set of Oracle stored procedures to PostgreSQL. How should I start?”
The agent will guide you through the Risk Assessment workflow, helping you list affected objects and screen them against the reference index to document necessary changes.
“I have finished migrating a query that uses ROWNUM. How do I validate this?”
The agent will initiate the Validation workflow, prompting you to map the artifact, check it against the ROWNUM-specific reference, and verify that your integration tests cover the new LIMIT/OFFSET logic.

Install

  1. Download the skill as a ZIP:

    https://mp-staging.autogineer.com/agent-skills/github/reviewing-oracle-to-postgres-migration/download

  2. Unzip it into the cross-client skills folder of your project:

    mkdir -p .agents/skills && unzip reviewing-oracle-to-postgres-migration.zip -d .agents/skills

Documentation by PrimedPaul

Oracle-to-PostgreSQL Database Migration

Surfaces migration risks and validates migration work against known Oracle/PostgreSQL behavioral differences documented in the references/ folder.

When to use

  1. Planning — Before starting migration work on a procedure, trigger, query, or refcursor client. Identify which reference insights apply so risks are addressed up front.

  2. Validating — After migration work is done, confirm every applicable insight was addressed and integration tests cover the new PostgreSQL semantics.

Workflow

Determine the task type:

Planning a migration? Follow the risk assessment workflow. Validating completed work? Follow the validation workflow.

Risk assessment workflow (planning)
Risk Assessment:
- [ ] Step 1: Identify the migration scope
- [ ] Step 2: Screen each insight for applicability
- [ ] Step 3: Document risks and recommended actions

Step 1: Identify the migration scope

List the affected database objects (procedures, triggers, queries, views) and the application code that calls them.

Step 2: Screen each insight for applicability

Review the reference index in references/REFERENCE.md. For each entry, determine whether the migration scope contains patterns affected by that insight. Read the full reference file only when the insight is potentially relevant.

Step 3: Document risks and recommended actions

For each applicable insight, note the specific risk and the recommended fix pattern from the reference file. Flag any insight that requires a design decision (e.g., whether to preserve Oracle empty-string-as-NULL semantics or adopt PostgreSQL behavior).

Validation workflow (post-migration)
Validation:
- [ ] Step 1: Map the migration artifact
- [ ] Step 2: Cross-check applicable insights
- [ ] Step 3: Verify integration test coverage
- [ ] Step 4: Gate the result

Step 1: Map the migration artifact

Identify the migrated object and summarize the change set.

Step 2: Cross-check applicable insights

For each reference in references/REFERENCE.md, confirm the behavior or test requirement is acknowledged and addressed in the migration work.

Step 3: Verify integration test coverage

Confirm tests exercise both the happy path and the failure scenarios highlighted in applicable insights (exceptions, sorting, UNION ALL behavior/performance risks, refcursor consumption, concurrent transactions, timestamps, materialized-view freshness, etc.).

Step 4: Gate the result

Return a checklist asserting each applicable insight was addressed, migration scripts run, and integration tests pass.

Files

references/no-data-found-exceptions.md

PostgreSQL Exception Handling: SELECT INTO No Data Found

Overview

A common issue when migrating from Oracle to PostgreSQL involves SELECT INTO statements that expect to raise an exception when no rows are found. This pattern difference can cause integration tests to fail and application logic to behave incorrectly if not properly handled.

Problem Description

Scenario

A stored procedure performs a lookup operation using SELECT INTO to retrieve a required value:

SELECT column_name
INTO variable_name
FROM table1, table2 
WHERE table1.id = table2.id AND table1.id = parameter_value;
Oracle Behavior

When a SELECT INTO statement in Oracle does not find any rows, it automatically raises:

ORA-01403: no data found

This exception is caught by the procedure's exception handler and re-raised to the calling application.

PostgreSQL Behavior (Pre-Fix)

When a SELECT INTO statement in PostgreSQL does not find any rows, it:

  • Sets the FOUND variable to false

  • Silently continues execution without raising an exception

This fundamental difference can cause tests to fail silently and logic errors in production code.

Root Cause Analysis

The PostgreSQL version was missing explicit error handling for the NOT FOUND condition after the SELECT INTO statement.

Original Code (Problematic):

SELECT column_name
INTO variable_name
FROM table1, table2 
WHERE table1.id = table2.id AND table1.id = parameter_value;

IF variable_name = 'X' THEN
 result_variable := 1;
ELSE
 result_variable := 2;
END IF;

Problem: No check for NOT FOUND condition. When an invalid parameter is passed, the SELECT returns no rows, FOUND becomes false, and execution continues with an uninitialized variable.

Key Differences: Oracle vs PostgreSQL

Add explicit NOT FOUND error handling to match Oracle behavior.

Fixed Code:

SELECT column_name
INTO variable_name
FROM table1, table2 
WHERE table1.id = table2.id AND table1.id = parameter_value;

-- Explicitly raise exception if no data found (matching Oracle behavior)
IF NOT FOUND THEN
    RAISE EXCEPTION 'no data found';
END IF;

IF variable_name = 'X' THEN
 result_variable := 1;
ELSE
 result_variable := 2;
END IF;

Migration Notes for Similar Issues

When fixing this issue, verify:

  1. Success path tests - Confirm valid parameters still work correctly

  2. Exception tests - Verify exceptions are raised with invalid parameters

  3. Transaction rollback - Ensure proper cleanup on errors

  4. Data integrity - Confirm all fields are populated correctly in success cases

Requirements and compatibility

Written for: GitHub Copilot

Works with: Claude Code, Claude apps, OpenAI Codex, GitHub Copilot, Cursor, Gemini CLI, Kiro, OpenCode, Goose, Amp, Roo Code, JetBrains Junie, Factory Droid, Devin (ex-Windsurf), Mistral Vibe, OpenHands, Letta

“Works with” means the tool reads the open Agent Skills format; a skill written for one tool may use features others lack.

Security

No issues found by our static scan· No issues found

A static scan is not a guarantee. Skills are third-party content: review a skill before you let an agent use it.

Attribution and licence

First committed by
GitHub avatar of PrimedPaulPrimedPaul
Publisher
GitHub (Official)
Distributed by
GitHub
Source
github/awesome-copilot · skills/reviewing-oracle-to-postgres-migration · commit 797337f
Licence
MIT License (redistribution allowed)
  • LICENSE (added by AutoGineer from LICENSE)
  • ATTRIBUTION.md (written by AutoGineer)
Synced
Contributors
PrimedPaul

AutoGineer is not affiliated with or endorsed by PrimedPaul or GitHub.

Download ZIP

The ZIP holds the skill’s files, its licence files and an ATTRIBUTION.md. Download reviewing-oracle-to-postgres-migration.zip