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.
This skill guides the translation of Oracle PL/SQL stored procedures and functions into their PostgreSQL PL/pgSQL equivalents during a database migration. It solves a well-known pain point in Oracle-to-Postgres migrations: the two databases share SQL heritage but diverge sharply in procedural syntax, type anchoring, collation behavior, and built-in functions, so a naive copy-paste rarely compiles or preserves behavior. The skill provides a disciplined, rule-driven conversion process that keeps functionality and control flow intact while adapting Oracle-specific constructs to Postgres.
The workflow is a three-step loop: read the Oracle source procedure (consulting Oracle table and view DDL for type resolution), translate it applying a fixed set of rules, then write each migrated procedure to its own file in a Postgres output directory. The rules preserve type-anchored input parameters (`%TYPE`), require explicit types for output parameters passed to other procedures, leave method signatures and exception/rollback logic unchanged, and forbid generating COMMENT or GRANT statements or adding schema prefixes. It handles collation deliberately (using `COLLATE "C"` only for binary ordering and mapping Oracle linguistic sorts such as `NLS_SORT` to explicit Postgres locale collations), treats `UNION ALL` as a plan-quality review checkpoint, and leverages the `orafce` compatibility extension where it improves fidelity.
It targets database engineers, DBAs, and migration teams moving Oracle workloads to PostgreSQL who need consistent, reviewable procedure conversions rather than ad-hoc rewrites. The skill is a single SKILL.md guide that assumes a specific migration directory layout under `.github/oracle-to-postgres-migration/`.
It translates Oracle PL/SQL stored procedures and functions into PostgreSQL PL/pgSQL, preserving original functionality, control flow, exception handling, and method signatures while adapting Oracle-specific syntax.
It reads Oracle procedures from `.github/oracle-to-postgres-migration/DDL/Oracle/Procedures and Functions/` and consults Oracle and Postgres table/view DDL for type resolution, then writes one migrated procedure per file under the Postgres `Procedures and Functions/` output directory.
It applies collation intentionally: `COLLATE "C"` only when Oracle-compatible binary ordering is required, and explicit Postgres locale collations when Oracle used linguistic sorting such as NLS_SORT. It suggests querying pg_collation to discover available collations in the target.
Yes. It does not emit COMMENT or GRANT statements, does not alter method signatures, and does not prefix object names with schema names unless they were already present in the Oracle source.
It leverages the orafce extension where that improves clarity or fidelity to Oracle behavior, but orafce is used opportunistically rather than being strictly mandatory for every procedure.
Quick Setup:
.claude/skills/Repository
github/awesome-copilot