Migrating stored procedures from Oracle to PostgreSQL
Translate an Oracle procedure or function from the migration workspace into one PostgreSQL PL/pgSQL file, using Oracle DDL for source semantics and PostgreSQL DDL for target schema fidelity.
When to invoke
- "Migrate this Oracle stored procedure to PostgreSQL."
- "Convert an Oracle PL/SQL function into PL/pgSQL."
- "Translate procedures from
.github/oracle-to-postgres-migration/DDL/Oracle/Procedures and Functions/."
- "Preserve Oracle behavior while writing the PostgreSQL procedure output."
Prerequisites and context
- Read Oracle procedure/function source from
.github/oracle-to-postgres-migration/DDL/Oracle/Procedures and Functions/.
- Resolve Oracle table and view types from
.github/oracle-to-postgres-migration/DDL/Oracle/Tables and Views/.
- Resolve target names, columns, and compatible data types from
.github/oracle-to-postgres-migration/DDL/Postgres/{ProjectName}/Tables and Views/.
- Write exactly one migrated procedure or function per file under
.github/oracle-to-postgres-migration/DDL/Postgres/{ProjectName}/Procedures and Functions/{PACKAGE_NAME_IF_APPLICABLE}/.
- Treat
{ProjectName} as the project assembly or folder name with spaces normalized to -, for example MyApp.DataAccess.
- Preserve legacy wording that helps operators search prior migration notes:
assembly/folder, PL/pgSQL**, (e.g., and . Consult the Oracle table/view definitions at .
Procedure
- Read the Oracle source procedure in full, including package context, parameter modes, cursor declarations, exception blocks, transaction statements, and calls to other procedures.
- Read the referenced Oracle tables/views and PostgreSQL tables/views before choosing any replacement data type.
- Translate syntax to PostgreSQL PL/pgSQL without changing the externally visible signature or the procedure's control-flow intent.
- Review collation,
UNION ALL, and orafce choices explicitly before writing output.
- Save the migrated object to the target
Procedures and Functions path, creating a package subdirectory only when the Oracle package requires {PACKAGE_NAME_IF_APPLICABLE}.
Translation rules
| Oracle concern |
PostgreSQL action |
| General PL/SQL syntax |
Translate to PL/pgSQL equivalents while preserving functionality and control flow logic. |
| Method signature |
Do not alter method signatures, parameter order, names, or modes. |
| Type-anchored input |
Preserve input anchors such as PARAM_NAME IN table_name.column_name%TYPE when the target supports a faithful anchor. |
| Output parameters passed to other procedures |
Use explicit types such as NUMERIC, VARCHAR, or INTEGER; do not type-anchor these outputs. |
| Object qualification |
Do not prefix object names with schema names unless the Oracle source already did. |
| Exceptions and rollback |
Leave exception handling and rollback logic unchanged unless PostgreSQL syntax requires a direct translation. |
| Comments and grants |
Do not generate COMMENT or GRANT statements. |
| Oracle compatibility functions |
Use the orafce extension when it improves clarity or preserves Oracle fidelity without hiding semantic differences. |
Collation and plan checkpoints
| Checkpoint |
Rule |
| Binary ordering |
Use COLLATE "C" only when Oracle-compatible binary ordering is required and no other sort order is specified. |
| Linguistic ordering |
If Oracle used explicit linguistic sorting such as NLS_SORT = French, map to an explicit PostgreSQL locale collation rather than "C". |
| Environment discovery |
Run or recommend SELECT collname, collprovider, collcollate, collctype FROM pg_collation ORDER BY collname; to discover target collations. |
UNION ALL |
Treat every UNION ALL as a review checkpoint; validate plan quality per branch and restructure when combined-branch planning causes regressions such as unexpected sequential scans on large tables. |
Output template
## Stored procedure migration - <procedure or function name>
**Status:** migrated | blocked
**Source:** `.github/oracle-to-postgres-migration/DDL/Oracle/Procedures and Functions/<source file>`
**Target:** `.github/oracle-to-postgres-migration/DDL/Postgres/<ProjectName>/Procedures and Functions/<PACKAGE_NAME_IF_APPLICABLE>/<procedure>.sql`
### Translation notes
| Area | Decision | Evidence |
| --- | --- | --- |
| Signature | <preserved or blocked reason> | `<parameter list>` |
| Types | <anchors and explicit output types> | `<Oracle/PostgreSQL DDL consulted>` |
| Collation | <none, COLLATE "C", or locale collation> | `<ORDER BY or NLS_SORT evidence>` |
| orafce | <used or not used> | `<function or reason>` |
| UNION ALL | <reviewed or not present> | `<plan concern or none>` |
### Validation
- Oracle source read: <yes/no>
- PostgreSQL target DDL read: <yes/no>
- One procedure per file: <yes/no>
Quality gate
1---2name: migrating-oracle-to-postgres-stored-procedures3description: Migrate Oracle PL/SQL stored procedures and functions to PostgreSQL PL/pgSQL while preserving behavior, signatures, type-anchored inputs, exception handling, rollback logic, collation semantics, and orafce compatibility. Use when converting Oracle Procedures and Functions into PostgreSQL equivalents during an Oracle-to-PostgreSQL migration.4---56<!-- Generated from harness/github-copilot/skills/migrating-oracle-to-postgres-stored-procedures/SKILL.md by harness/claude-code/scripts/convert_from_copilot.py. Edit the source, not this file. -->78# Migrating stored procedures from Oracle to PostgreSQL910Translate an Oracle procedure or function from the migration workspace into one PostgreSQL PL/pgSQL file, using Oracle DDL for source semantics and PostgreSQL DDL for target schema fidelity.1112## When to invoke1314- "Migrate this Oracle stored procedure to PostgreSQL."15- "Convert an Oracle PL/SQL function into PL/pgSQL."16- "Translate procedures from `.github/oracle-to-postgres-migration/DDL/Oracle/Procedures and Functions/`."17- "Preserve Oracle behavior while writing the PostgreSQL procedure output."1819## Prerequisites and context2021- Read Oracle procedure/function source from `.github/oracle-to-postgres-migration/DDL/Oracle/Procedures and Functions/`.22- Resolve Oracle table and view types from `.github/oracle-to-postgres-migration/DDL/Oracle/Tables and Views/`.23- Resolve target names, columns, and compatible data types from `.github/oracle-to-postgres-migration/DDL/Postgres/{ProjectName}/Tables and Views/`.24- Write exactly one migrated procedure or function per file under `.github/oracle-to-postgres-migration/DDL/Postgres/{ProjectName}/Procedures and Functions/{PACKAGE_NAME_IF_APPLICABLE}/`.25- Treat `{ProjectName}` as the project assembly or folder name with spaces normalized to `-`, for example `MyApp.DataAccess`.26- Preserve legacy wording that helps operators search prior migration notes: `assembly/folder`, `PL/pgSQL**`, ` (e.g. `, and `. Consult the Oracle table/view definitions at `.2728## Procedure29301. Read the Oracle source procedure in full, including package context, parameter modes, cursor declarations, exception blocks, transaction statements, and calls to other procedures.312. Read the referenced Oracle tables/views and PostgreSQL tables/views before choosing any replacement data type.323. Translate syntax to PostgreSQL PL/pgSQL without changing the externally visible signature or the procedure's control-flow intent.334. Review collation, `UNION ALL`, and orafce choices explicitly before writing output.345. Save the migrated object to the target `Procedures and Functions` path, creating a package subdirectory only when the Oracle package requires `{PACKAGE_NAME_IF_APPLICABLE}`.3536## Translation rules3738| Oracle concern | PostgreSQL action |39| --- | --- |40| General PL/SQL syntax | Translate to PL/pgSQL equivalents while preserving functionality and control flow logic. |41| Method signature | Do not alter method signatures, parameter order, names, or modes. |42| Type-anchored input | Preserve input anchors such as `PARAM_NAME IN table_name.column_name%TYPE` when the target supports a faithful anchor. |43| Output parameters passed to other procedures | Use explicit types such as `NUMERIC`, `VARCHAR`, or `INTEGER`; do not type-anchor these outputs. |44| Object qualification | Do not prefix object names with schema names unless the Oracle source already did. |45| Exceptions and rollback | Leave exception handling and rollback logic unchanged unless PostgreSQL syntax requires a direct translation. |46| Comments and grants | Do not generate `COMMENT` or `GRANT` statements. |47| Oracle compatibility functions | Use the `orafce` extension when it improves clarity or preserves Oracle fidelity without hiding semantic differences. |4849## Collation and plan checkpoints5051| Checkpoint | Rule |52| --- | --- |53| Binary ordering | Use `COLLATE "C"` only when Oracle-compatible binary ordering is required and no other sort order is specified. |54| Linguistic ordering | If Oracle used explicit linguistic sorting such as `NLS_SORT = French`, map to an explicit PostgreSQL locale collation rather than `"C"`. |55| Environment discovery | Run or recommend `SELECT collname, collprovider, collcollate, collctype FROM pg_collation ORDER BY collname;` to discover target collations. |56| `UNION ALL` | Treat every `UNION ALL` as a review checkpoint; validate plan quality per branch and restructure when combined-branch planning causes regressions such as unexpected sequential scans on large tables. |5758## Output template5960```markdown61## Stored procedure migration - <procedure or function name>6263**Status:** migrated | blocked64**Source:** `.github/oracle-to-postgres-migration/DDL/Oracle/Procedures and Functions/<source file>`65**Target:** `.github/oracle-to-postgres-migration/DDL/Postgres/<ProjectName>/Procedures and Functions/<PACKAGE_NAME_IF_APPLICABLE>/<procedure>.sql`6667### Translation notes68| Area | Decision | Evidence |69| --- | --- | --- |70| Signature | <preserved or blocked reason> | `<parameter list>` |71| Types | <anchors and explicit output types> | `<Oracle/PostgreSQL DDL consulted>` |72| Collation | <none, COLLATE "C", or locale collation> | `<ORDER BY or NLS_SORT evidence>` |73| orafce | <used or not used> | `<function or reason>` |74| UNION ALL | <reviewed or not present> | `<plan concern or none>` |7576### Validation77- Oracle source read: <yes/no>78- PostgreSQL target DDL read: <yes/no>79- One procedure per file: <yes/no>80```8182## Quality gate8384- [ ] Oracle source was read from `Procedures and Functions` and all referenced table/view definitions were checked.85- [ ] PostgreSQL table/view definitions under `.github/oracle-to-postgres-migration/DDL/Postgres/{ProjectName}/Tables and Views/` were used for target types.86- [ ] The migrated signature preserves the Oracle method name, parameter order, parameter modes, and type-anchored inputs.87- [ ] Output parameters passed to other procedures use explicit `NUMERIC`, `VARCHAR`, `INTEGER`, or another justified PostgreSQL type.88- [ ] No new schema prefixes, `COMMENT` statements, or `GRANT` statements were introduced.89- [ ] Exception handling and rollback logic remain behaviorally equivalent.90- [ ] Collation decisions and every `UNION ALL` review checkpoint are documented.91- [ ] The output file path uses `{ProjectName}` and `{PACKAGE_NAME_IF_APPLICABLE}` correctly, with one procedure per file.
paulasilvatech