Convert Stored Procedures Across Engines, Transpiler-First

Tier: Advanced · Workflow area: Migration & Modernization · ~45 minutes

The temptation is to paste PL/SQL into a chat window and ask for PostgreSQL. Resist it. Transpilers are deterministic, auditable and honest about what they cannot do; LLMs are fluent, tireless and confidently wrong about semantics — NULL behavior, implicit commits, date arithmetic. The professional pipeline uses each where it is strong: rules for the bulk, generation for the residue, and parity tests (pb-15) as the only judge either one answers to.

When to use

• Oracle/SQL Server to PostgreSQL migrationsthe classic cost-driven conversion, with mature transpilers (Ora2Pg, AWS DMS Schema Conversion, pgloader for MySQL sources) to anchor the pipeline.
• A procedure estate too big to hand-converthundreds of procedures where the transpiler clears the mechanical 60–80% and human attention concentrates on the hard residue.
• When a vendor or SI quotes an automatic-conversion percentagethis playbook is how you audit that number on your own codebase in an afternoon.

When NOT to use

• LLM-only conversion of business-critical proceduresfluent output with no conversion report and no determinism. Without a transpiler baseline you cannot even list what changed.
• When the logic should move to the applicationa migration is the once-a-decade chance to retire stored-procedure business logic; converting it faithfully may be preserving a mistake. Decide move-vs-convert per procedure first (pb-14's excavation informs this).
• Before the target schema is settledprocedure conversion against a moving schema is rework. Tables, types and constraints convert and stabilize first.

The recipe

1. 1.

   Run the transpiler and capture its honesty. Ora2Pg assesses before it converts — start there:

   Copy

   # Assessment: complexity scoring and human-days estimate per object
   ora2pg -t SHOW_REPORT --estimate_cost -c ora2pg.conf
   
   # Convert the procedure estate
   ora2pg -t PROCEDURE -o procedures.sql -c ora2pg.conf
   ora2pg -t FUNCTION  -o functions.sql  -c ora2pg.conf
   

   The output contains converted code and failure markers — grep them out into your residue list:

   Copy

   grep -n "ORA2PG_COMMENT\|FIXME\|autonomous_transaction" procedures.sql
   

   Record the real automatic-conversion rate: objects converted clean / total. This number — measured on your code — is your answer to the vendor slide.

2. 2.

   Triage the residue into buckets. Typical residue: autonomous transactions, CONNECT BY hierarchies the transpiler punted on, UTL_/DBMS_ package calls, bulk-collect patterns, dynamic SQL built from strings. Bucket each: mechanical (AI-refinable), architectural (needs a design decision — autonomous transactions have no direct Postgres equivalent; choose dblink, a queue table, or app-level restructuring), and retire (dead code pb-14 identified).

3. 3.

   Refine mechanical residue with AI — one object, full context, explicit traps.

   Copy

   Convert this PL/SQL block to PL/pgSQL for PostgreSQL 16. It is the
   residue Ora2Pg could not convert (its partial output and FIXME
   markers included below, plus the already-converted target schema).
   
   Semantic traps — handle each EXPLICITLY with a comment where your
   conversion differs from Oracle behavior:
   1. Oracle '' IS NULL; Postgres '' is a value. Flag every string
      comparison this affects.
   2. Oracle DATE arithmetic returns days as numbers; Postgres
      returns intervals. Convert the date math accordingly.
   3. Oracle implicit commits (DDL, autonomous txns); Postgres runs
      in-transaction. State the transactional difference.
   4. SYSDATE vs now()/clock_timestamp() — choose per statement and
      justify (statement-stable vs real-time).
   5. Exception codes: map Oracle SQLCODEs to Postgres SQLSTATEs;
      do NOT swallow OTHERS silently.
   Output: converted code + a DIFFERENCES table (line, Oracle behavior,
   Postgres behavior, risk).
   

   The DIFFERENCES table is the review surface. An AI conversion without one is unreviewable fluency.

4. 4.

   Review the differences table, then compile. Load into a scratch database and let the engine catch what review missed:

   Copy

   psql -d migr_target -v ON_ERROR_STOP=1 -f procedures_refined.sql
   

   Compilation success means syntax, nothing more. Semantics are next.

5. 5.

   Prove behavior with parity tests — the non-negotiable gate. For each converted procedure, execute source and target against identical seeded inputs and diff the outcomes: result sets, output parameters, side-effect table states, and error behavior for invalid inputs. Build the harness per pb-15; even the quick version catches the classics:

   Copy

   -- Target side: same seeded inputs the Oracle side ran
   CALL calc_customer_discount(1001, '2026-07-31'::date);
   SELECT * FROM discount_audit WHERE customer_id = 1001;
   -- diff against the Oracle-side capture, column by column,
   -- with explicit NULL-vs-empty-string comparison
   

   Expect the parity suite to catch AI conversions that compiled cleanly and run wrong — empty-string equality checks and date-boundary arithmetic are the usual culprits. Log every catch: procedure, defect, which pipeline stage produced it.

6. 6.

   Assemble the conversion record. Per procedure: transpiler verdict, residue bucket, AI differences table, review sign-off, parity result. Estate-wide: the measured auto-conversion rate, the AI-failure log, and the retire list. This record is what makes the migration auditable — and what makes your next conversion estimate accurate.

Make it yours

• SQL Server → PostgreSQLDMS Schema Conversion (or Babelfish assessment for a different strategy entirely) plays the Ora2Pg role; the trap list changes: @@ROWCOUNT/GET DIAGNOSTICS, TOP/LIMIT, transaction-scoped temp tables vs Postgres temp tables, MERGE behavior differences, and T-SQL's error-handling model (TRY/CATCH vs exception blocks).
• Oracle → SQL ServerSSMA for Oracle is the transpiler; autonomous transactions map to loopback-linked-server patterns or Service Broker, and the same differences-table discipline applies to SSMA's own emulation layer (sysdb functions), which deserves parity tests too.
• MySQL → PostgreSQLpgloader handles schema and data; the procedure estate is usually small but the traps differ: zero-dates, silent truncation semantics, backtick identifiers and case-sensitivity of table names. The same pipeline shape, lighter residue.

Quality check

• The transpiler ran first, and its report is filedan LLM-first conversion has no baseline, no failure markers and no measured rate. Rules first is the discipline.
• Every AI conversion carries a differences tablereviewed line by line; conversions that arrived without one went back.
• Parity tests executed against seeded data on both enginescompilation is not correctness. The suite ran, and its catches are logged, not quietly fixed.
• The vendor percentage was replaced by your measured onethe number in your migration plan is the rate from step 1, on your code, with the residue cost estimated from the actual buckets.