migrating-oracle-to-postgres-stored-procedures

github/awesome-copilot

Translate Oracle PL/SQL stored procedures to PostgreSQL PL/pgSQL with syntax mapping and collation handling.

What is migrating-oracle-to-postgres-stored-procedures?

Converts Oracle PL/SQL stored procedures and functions to PostgreSQL PL/pgSQL equivalents during database migration. Handles Oracle-specific syntax translation, preserves method signatures and type-anchored parameters, applies collation mapping intentionally, and leverages the orafce extension where appropriate.

Translates Oracle PL/SQL syntax to PostgreSQL PL/pgSQL equivalents
Preserves original functionality, control flow, and method signatures
Maintains type-anchored input parameters while using explicit types for output parameters
Applies intelligent collation mapping (COLLATE "C" for binary ordering, locale collations for linguistic sorting)
Leverages orafce extension for improved clarity and fidelity
Validates query plans at UNION ALL checkpoints to prevent performance regressions

How to install migrating-oracle-to-postgres-stored-procedures

npx skills add https://github.com/github/awesome-copilot --skill migrating-oracle-to-postgres-stored-procedures

Prerequisites

Access to Oracle source procedure definitions in .github/oracle-to-postgres-migration/DDL/Oracle/Procedures and Functions/
Oracle table/view definitions for type resolution at .github/oracle-to-postgres-migration/DDL/Oracle/Tables and Views/
Target PostgreSQL schema definitions at .github/oracle-to-postgres-migration/DDL/Postgres/{ProjectName}/Tables and Views/
PostgreSQL environment with optional orafce extension installed

Claude Code

Cursor

Windsurf

Cline

How to use migrating-oracle-to-postgres-stored-procedures

1.Read the Oracle source procedure from the Oracle DDL directory and consult table/view definitions for type resolution
2.Apply translation rules: convert Oracle-specific syntax, preserve functionality and signatures, keep type-anchored input parameters, use explicit types for output parameters
3.Apply collation mapping: use COLLATE "C" for binary ordering when required, map explicit linguistic sorting to PostgreSQL locale collations, query pg_collation to discover available collations
4.Validate query plans at UNION ALL checkpoints and restructure if combined-branch planning causes performance regressions
5.Write the migrated procedure to .github/oracle-to-postgres-migration/DDL/Postgres/{ProjectName}/Procedures and Functions/{PACKAGE_NAME_IF_APPLICABLE}/ with one procedure per file

Use cases

Good for

Migrating a legacy Oracle database application to PostgreSQL
Converting stored procedures during a full database schema migration
Translating Oracle functions that use package-specific syntax to PostgreSQL equivalents
Handling collation-sensitive procedures that require specific sort orders
Refactoring procedures that use orafce-compatible Oracle functions

Who it's for

Database migration engineers
Oracle-to-PostgreSQL conversion projects
Backend developers managing legacy database transitions
Database architects planning cloud or open-source database migrations

migrating-oracle-to-postgres-stored-procedures FAQ

Should I type-anchor output parameters passed to other procedures?

No. Use explicit types (NUMERIC, VARCHAR, INTEGER) for output parameters passed to other procedures, but keep type-anchored parameters for inputs (e.g., PARAM_NAME IN table_name.column_name%TYPE).

When should I use COLLATE "C" vs. locale collations?

Use COLLATE "C" only when Oracle-compatible binary ordering is required with no other sort order specified. If Oracle used explicit linguistic sorting (e.g., NLS_SORT = French), map to an explicit PostgreSQL locale collation instead.

Should I add schema prefixes to object names?

No. Do not prefix object names with schema names unless they were already present in the Oracle source.

When should I use the orafce extension?

Leverage orafce when it improves clarity or fidelity in translating Oracle-specific functions and features.

What should I do if a UNION ALL query shows performance regressions?

Treat UNION ALL as a review checkpoint. Validate plan quality per branch and restructure if combined-branch planning causes regressions like unexpected sequential scans on large tables.

Full instructions (SKILL.md)

Source of truth, from github/awesome-copilot.

name: migrating-oracle-to-postgres-stored-procedures description: 'Migrates Oracle PL/SQL stored procedures to PostgreSQL PL/pgSQL. Translates Oracle-specific syntax, preserves method signatures and type-anchored parameters, leverages orafce where appropriate, and applies explicit collation mapping (`COLLATE "C"` only when appropriate, locale collations when required). Use when converting Oracle stored procedures or functions to PostgreSQL equivalents during a database migration.'

Migrating Stored Procedures from Oracle to PostgreSQL

Translate Oracle PL/SQL stored procedures and functions to PostgreSQL PL/pgSQL equivalents.

Workflow

Progress:
- [ ] Step 1: Read the Oracle source procedure
- [ ] Step 2: Translate to PostgreSQL PL/pgSQL
- [ ] Step 3: Write the migrated procedure to Postgres output directory

Step 1: Read the Oracle source procedure

Read the Oracle stored procedure from .github/oracle-to-postgres-migration/DDL/Oracle/Procedures and Functions/. Consult the Oracle table/view definitions at .github/oracle-to-postgres-migration/DDL/Oracle/Tables and Views/ for type resolution.

Step 2: Translate to PostgreSQL PL/pgSQL

Apply these translation rules:

Translate all Oracle-specific syntax to PostgreSQL equivalents.
Preserve original functionality and control flow logic.
Keep type-anchored input parameters (e.g., PARAM_NAME IN table_name.column_name%TYPE).
Use explicit types (NUMERIC, VARCHAR, INTEGER) for output parameters passed to other procedures — do not type-anchor these.
Do not alter method signatures.
Do not prefix object names with schema names unless already present in the Oracle source.
Leave exception handling and rollback logic unchanged.
Do not generate COMMENT or GRANT statements.
Apply collation intentionally when ordering text:
- Use COLLATE "C" only when Oracle-compatible binary ordering is required and no other sort order is specified.
- If Oracle used explicit linguistic sorting (for example NLS_SORT = French), map to an explicit PostgreSQL locale collation instead of "C".
- Use SELECT collname, collprovider, collcollate, collctype FROM pg_collation ORDER BY collname; to discover collations in the target environment.
Treat UNION ALL as a review checkpoint. Validate plan quality per branch and restructure if combined-branch planning causes regressions (for example, unexpected sequential scans on large tables).
Leverage the orafce extension when it improves clarity or fidelity.

Consult the PostgreSQL table/view definitions at .github/oracle-to-postgres-migration/DDL/Postgres/{ProjectName}/Tables and Views/ for target schema details.

Step 3: Write the migrated procedure to Postgres output directory

Place each migrated procedure in its own file under .github/oracle-to-postgres-migration/DDL/Postgres/{ProjectName}/Procedures and Functions/{PACKAGE_NAME_IF_APPLICABLE}/. One procedure per file.

{ProjectName} is the project's assembly/folder name with spaces normalized to - (e.g. MyApp.DataAccess). This matches the path used by the agent and other migration skills.