migrating-oracle-to-postgres-stored-procedures

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

Security requirement — path validation (must be enforced before any read or write operation): {ProjectName} MUST match the pattern ^[A-Za-z0-9][A-Za-z0-9._-]*$. It MUST NOT contain /, \, or any .. sequence. Reject and abort if the value fails this check. {PACKAGE_NAME_IF_APPLICABLE} is the Oracle package name (e.g., PKG_PAYROLL). If the procedure is standalone (not part of a package), omit this directory level entirely. It MUST match ^[A-Za-z0-9][A-Za-z0-9._-]*$ if present. MUST NOT contain /, \, or ... Resolve every path constructed from these placeholders to its canonical form and verify that it remains a strict descendant of .github/oracle-to-postgres-migration/ before reading or writing any file.

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 the following common mappings as a starting point:

    Oracle NLS setting	PostgreSQL collation
    BINARY / default	"C"
    FRENCH / FRENCH_CI / FRENCH_M	"fr-FR-x-icu"
    GERMAN	"de-DE-x-icu"
    SPANISH	"es-ES-x-icu"
    JAPANESE	"ja-JP-x-icu"

  • If the required collation is not listed above, instruct the user to run the following query manually against the target PostgreSQL instance (do not execute it yourself):

    SELECT collname, collprovider, collcollate, collctype FROM pg_collation ORDER BY collname;

    Insert a placeholder comment in the output file documenting the collation name that needs to be confirmed, and verify that any chosen ICU locale is available in the target PostgreSQL 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). Must satisfy ^[A-Za-z0-9][A-Za-z0-9._-]*$. This matches the path used by the agent and other migration skills. {PACKAGE_NAME_IF_APPLICABLE} is the Oracle package name containing the procedure (e.g., PKG_PAYROLL). Omit this directory level for standalone procedures. Must satisfy ^[A-Za-z0-9][A-Za-z0-9._-]*$ if used.