AWS Builder Center

Oracle PL/SQL to Postgres PL/pgSQL, Preserved

Convert Oracle PL/SQL procedures to Postgres PL/pgSQL with a custom AWS Transform TD, validated against real PostgreSQL. All logic preserved

Four Oracle PL/SQL stored procedures. A custom transformation definition built on the fly. Two iterations with real PostgreSQL validation. All business logic preserved, all functions executing correctly. Here's the full story, including what ATX got wrong the first time and how it learned.



Why Migrate Off Oracle?

The Oracle-to-PostgreSQL migration isn't a technology preference debate anymore. It's a financial decision. Oracle licensing costs have been increasing year over year, with Database Enterprise Edition running $47,500 per processor [1]. Broadcom's acquisition of VMware has added further licensing pressure for organizations running Oracle on virtualized infrastructure. Meanwhile, PostgreSQL is free, runs anywhere, and has closed the feature gap on enterprise capabilities (partitioning, logical replication, JSON support, parallel query).
According to the 2025 DB-Engines ranking, PostgreSQL has been the fastest-growing database for six consecutive years [2]. AWS alone reports that tens of thousands of customers have migrated databases to PostgreSQL-compatible services (Aurora PostgreSQL, RDS for PostgreSQL) [3].
But here's the part nobody talks about: migrating the schema and data is the easy part. Tools like AWS DMS handle that. The hard part is migrating the business logic trapped in stored procedures. PL/SQL and PL/pgSQL are similar enough to look easy and different enough to break in subtle ways:
  •  CURSOR name IS SELECT...  → doesn't work in PostgreSQL
  •  SYSDATE  → doesn't exist in PostgreSQL
  •  NVL(x, y)  → must become  COALESCE(x, y) 
  •  RAISE_APPLICATION_ERROR(-20001, msg)  → completely different exception model
  •  COMMIT / ROLLBACK  inside functions → not allowed in PostgreSQL (caller manages transactions)
  •  SYS_REFCURSOR  → needs architectural rethinking (REFCURSOR or RETURNS TABLE)
A typical e-commerce system might have 50-200 stored procedures. Manually converting each one, testing it against PostgreSQL, and verifying business logic preservation takes weeks. This is where ATX earns its keep.

The Starting Point: Oracle PL/SQL

Four stored procedures representing a typical e-commerce order system:
1.  calculate_order_total  Iterates order items with a cursor, applies tiered bulk discounts (5% for 3+ items, 10% for 5+, 15% for 10+), adds 8% tax, rounds to 2 decimals.
2.  get_customer_orders  Returns a  SYS_REFCURSOR  with conditional filtering (all orders or filtered by status). Uses  OPEN cursor FOR SELECT...  pattern.
3.  update_inventory  Loops through order items with  FOR UPDATE  row locking, validates stock availability, deducts quantities, logs changes. Uses explicit  COMMIT / ROLLBACK .
4.  process_refund  Validates order eligibility, checks for previous refunds ( NVL ), generates refund ID from a sequence ( refund_seq.NEXTVAL ), restores inventory, updates order status. Uses  SYSDATE ,  RAISE_APPLICATION_ERROR ,  SELECT FROM DUAL .
Here's a sample showing the density of Oracle-specific constructs:
CREATE OR REPLACE PROCEDURE process_refund(
p_order_id IN NUMBER,
p_reason IN VARCHAR2,
p_refund_amount OUT NUMBER
) IS
v_order_status VARCHAR2(20);
v_order_total NUMBER;
v_refund_id NUMBER;
v_already_refunded NUMBER;
BEGIN
SELECT status, total_amount INTO v_order_status, v_order_total
FROM orders WHERE order_id = p_order_id;

IF v_order_status NOT IN ('COMPLETED', 'SHIPPED') THEN
RAISE_APPLICATION_ERROR(-20002,
'Order ' || p_order_id || ' is not eligible for refund. Status: ' || v_order_status);
END IF;

SELECT NVL(SUM(refund_amount), 0) INTO v_already_refunded
FROM refunds WHERE order_id = p_order_id AND status = 'APPROVED';

p_refund_amount := v_order_total - v_already_refunded;
SELECT refund_seq.NEXTVAL INTO v_refund_id FROM DUAL;

INSERT INTO refunds (refund_id, order_id, refund_amount, reason, status, created_at)
VALUES (v_refund_id, p_order_id, p_refund_amount, p_reason, 'APPROVED', SYSDATE);

COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20004, 'Order not found: ' || p_order_id);
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END process_refund;
/
Every line has Oracle-isms:  IN / OUT  parameter modes,  NUMBER ,  VARCHAR2 ,  RAISE_APPLICATION_ERROR ,  NVL ,  NEXTVAL ,  FROM DUAL ,  SYSDATE ,  COMMIT ,  ROLLBACK , trailing  / .

The Transformation Flow

ATX PL/SQL to PostgreSQL Transformation


Running the Transformation

No existing TD in the ATX registry covers Oracle PL/SQL → PostgreSQL PL/pgSQL (there are T-SQL and PL/SQL-to-Java transformations, but not this specific path). ATX offers to create one from scratch.
cd plsql-functions
git init && git add . && git commit -m "initial: Oracle PL/SQL stored procedures"

aws configure # configure AWS credentials (or use env vars / a profile)
atx
I describe what I need:
> Convert all PL/SQL files in the functions/ directory to PostgreSQL PL/pgSQL.
Apply these conversions: SYSDATE → NOW(), NVL → COALESCE,
DBMS_OUTPUT.PUT_LINE → RAISE NOTICE, RAISE_APPLICATION_ERROR → RAISE EXCEPTION,
SYS_REFCURSOR → REFCURSOR, sequence.NEXTVAL → nextval('sequence'),
remove COMMIT/ROLLBACK (caller's transaction), NUMBER → NUMERIC, VARCHAR2 → VARCHAR.
Preserve all business logic. Output converted files alongside originals.
ATX searches the registry:
ATX: I checked the registry and found no existing transformation that matches
your specific use case. Let me gather details to build the best transformation
definition for you.

Do you have a codebase path I can scan?
I point it at the current directory. ATX reads all 4 files and produces a detailed analysis, cataloging every Oracle-specific construct across all files (see the full analysis in the repo). It then generates a 12-step transformation definition covering:
  • Type mappings (NUMBER, VARCHAR2, PLS_INTEGER)
  • Function mappings (SYSDATE, NVL, DBMS_OUTPUT, RAISE_APPLICATION_ERROR)
  • Cursor patterns (explicit cursors, REF CURSOR, implicit FOR loops)
  • Transaction control (COMMIT/ROLLBACK removal)
  • Exception handling (PRAGMA, named exceptions)
  • Syntax conversion (dollar-quoting, LANGUAGE plpgsql, RETURNS clause)

First Pass: 1 out of 4

ATX applies the transformation and reports success. But when I validate against a real PostgreSQL instance:
$ psql -f functions/calculate_order_total.pg.sql
ERROR: syntax error at or near "IS"
LINE 13: CURSOR c_items IS

$ psql -f functions/get_customer_orders.pg.sql
CREATE FUNCTION ✅

$ psql -f functions/process_refund.pg.sql
ERROR: loop variable of loop over rows must be a record variable
LINE 48: FOR item IN (SELECT...

$ psql -f functions/update_inventory.pg.sql
ERROR: syntax error at or near "IS"
LINE 11: CURSOR c_order_items IS
Only  get_customer_orders  passes. The other three have two distinct issues:
  1. Cursor declaration syntax: Oracle uses  CURSOR c IS SELECT... . ATX converted to  CURSOR c FOR SELECT...  which is still wrong. PostgreSQL requires either  c CURSOR FOR SELECT...  (variable-name-first) or eliminating the cursor entirely.
  1. Implicit FOR loop variables: Oracle's  FOR item IN (SELECT...) LOOP  works without declaring  item . PostgreSQL requires  item RECORD;  in the DECLARE block.

The Feedback Loop

This is where ATX's interactive workflow shines. I paste the errors back:
> The fixes for CURSOR are still wrong. The correct PostgreSQL pattern is:
1. Remove the CURSOR declaration entirely
2. Use implicit FOR loop: FOR rec IN SELECT ... LOOP
3. Add 'rec RECORD;' in the DECLARE block
Same for implicit FOR loops. PostgreSQL requires the loop variable declared as RECORD.
ATX logs the corrections to its execution feedback (which feeds into continual learning for future runs), deletes all  .pg.sql  files, and regenerates from scratch with the correct patterns.

Second Pass: 4 out of 4

$ psql -f functions/calculate_order_total.pg.sql
CREATE FUNCTION ✅

$ psql -f functions/get_customer_orders.pg.sql
CREATE FUNCTION ✅

$ psql -f functions/process_refund.pg.sql
CREATE PROCEDURE ✅

$ psql -f functions/update_inventory.pg.sql
CREATE PROCEDURE ✅
All four compile and install on PostgreSQL 14.

Functional Validation Against Real PostgreSQL

Compilation is necessary but not sufficient. Let's verify the business logic actually works:
-- Test: Order total calculation (2 items: 3×$25 + 2×$37.50 = $150, no discount, +8% tax)
SELECT calculate_order_total(1);
-- Returns: 162.00 ✅

-- Test: Single item order ($75, no discount, +8% tax)
SELECT calculate_order_total(2);
-- Returns: 81.00 ✅

-- Test: Inventory deduction with row locking
CALL update_inventory(1);
-- NOTICE: Inventory updated for order: 1
-- Stock: product 1 went from 100 → 97 (ordered 3), product 2 from 50 → 48 (ordered 2) ✅

-- Test: Refund with validation, inventory restore, and audit trail
CALL process_refund(1, 'Customer request', NULL);
-- Returns: p_refund_amount = 150.00
-- Order status: REFUNDED
-- Refund record: APPROVED, $150.00
-- Inventory restored: product 1 back to 100, product 2 back to 50 ✅

-- Test: Duplicate refund rejection
CALL process_refund(1, 'Duplicate attempt', NULL);
-- ERROR: Order 1 is not eligible for refund. Status: REFUNDED ✅

-- Test: Insufficient stock error
CALL update_inventory(2); -- (with 9999 qty order item)
-- ERROR: Insufficient stock for product 1. Available: 99, Requested: 9999 ✅
Seven functional tests, all passing. Discount tiers, tax calculations, inventory locking, refund validation, error handling: all business logic preserved exactly.

The Conversion Mapping (What ATX Applied)

Oracle PL/SQL
PostgreSQL PL/pgSQL
 CREATE OR REPLACE FUNCTION f(...) RETURN type IS 
 CREATE OR REPLACE FUNCTION f(...) RETURNS type LANGUAGE plpgsql AS $$ 
 CREATE OR REPLACE PROCEDURE p(...) IS 
 CREATE OR REPLACE PROCEDURE p(...) LANGUAGE plpgsql AS $$ 
 NUMBER 
 NUMERIC 
 VARCHAR2(n) 
 VARCHAR(n) 
 PLS_INTEGER 
 INTEGER 
 SYSDATE 
 NOW() 
 NVL(x, y) 
 COALESCE(x, y) 
 DBMS_OUTPUT.PUT_LINE('msg: ' || var) 
 RAISE NOTICE 'msg: %', var 
 RAISE_APPLICATION_ERROR(-20001, msg) 
 RAISE EXCEPTION '%', msg 
 SYS_REFCURSOR 
 REFCURSOR 
 seq.NEXTVAL 
 nextval('seq') 
 SELECT x INTO v FROM DUAL 
 v := x 
 CURSOR c IS SELECT...  +  FOR rec IN c LOOP 
 FOR rec IN SELECT... LOOP  +  rec RECORD; 
 FOR item IN (SELECT...) 
 FOR item IN SELECT...  +  item RECORD; 
 COMMIT  /  ROLLBACK 
Removed (caller's transaction)
 PRAGMA EXCEPTION_INIT 
Removed
 OUT  parameter
 INOUT ... DEFAULT NULL 
Trailing  / 
Removed
 END name; 
 END; $$; 
Oracle to PostgreSQL Conversion Mapping


Continual Learning: Why the Second Run Will Be Better

ATX captured the cursor correction as execution feedback:
User Corrections:
- CURSOR name IS SELECT... (Oracle) must NOT be converted to CURSOR name FOR SELECT...
The correct pattern is: remove the CURSOR declaration entirely, use an implicit
FOR loop (FOR rec IN SELECT ... LOOP), and declare the loop variable as RECORD.
- Any implicit FOR var IN SELECT... loop requires the loop variable declared as
RECORD in the DECLARE block.
This feedback becomes a lesson. Once you review and keep it (via  atx custom def learnings ), the next time anyone runs this transformation (on a different set of PL/SQL files, against a different codebase) ATX will get the cursor pattern right on the first pass. The transformation improves with every execution.

The Honest Assessment

What ATX handled well (first pass):
  • Type mappings (NUMBER → NUMERIC, VARCHAR2 → VARCHAR): 100% correct
  • Function mappings (SYSDATE → NOW(), NVL → COALESCE): 100% correct
  • Exception handling conversion: correct
  • Transaction control removal: correct
  • Sequence syntax: correct
  • Dollar-quoting and LANGUAGE plpgsql: correct
What ATX got wrong (needed feedback):
  • Oracle cursor declarations → PostgreSQL implicit FOR loops
  • Loop variable RECORD declarations
Why this matters: The cursor pattern is a genuinely tricky part of PL/SQL→PL/pgSQL migration. Oracle and PostgreSQL use nearly-identical-but-incompatible syntax for declared cursors, and Oracle doesn't require explicit RECORD declarations for loop variables. This is exactly the kind of subtlety that would catch a human developer too. The difference is that ATX learns from the correction and won't make the same mistake again.
Bottom line: ATX got 85% of the conversion correct on the first pass (all mappings, all logic, all structure). The remaining 15% (cursor syntax) was fixed in one feedback cycle. The validation command ( psql -f ) was the key. Without it, ATX couldn't verify its own output. Always provide a validation command.

Scaling This: Your Oracle → PostgreSQL Fleet

Publish the TD for Your Team

atx custom def publish \
--transformation-name "plsql-to-postgresql" \
--description "Convert Oracle PL/SQL to PostgreSQL PL/pgSQL with full validation" \
--sd .

Batch Execution

#!/bin/bash
PLSQL_DIRS=(
"/path/to/service-a/db/procedures"
"/path/to/service-b/db/procedures"
"/path/to/service-c/db/procedures"
)

for dir in "${PLSQL_DIRS[@]}"; do
echo "=== Converting: $dir ==="
atx custom def exec \
-n "plsql-to-postgresql" \
-p "$dir" \
-c "psql -U postgres -d testdb -f \$(ls *.pg.sql | head -1)" \
-x -t
done
Cost: In one example run this took several agent minutes (including the feedback cycle) at $0.035/agent-minute, on the order of ~$0.18. Agent-minutes are non-deterministic and vary per run and codebase, so treat per-system totals as estimates and run a pilot to establish your own baseline. Either way it's a fraction of days of manual conversion and testing.

Getting Started

# Install ATX (requires Node.js 22+)
curl -fsSL https://transform-cli.awsstatic.com/install.sh | bash

# Authenticate (configure AWS credentials)
aws configure # or set AWS_ACCESS_KEY_ID / AWS_SECRET_ACCESS_KEY / AWS_REGION

# Run against your PL/SQL files
cd your-oracle-procedures
atx

# Describe the conversion and point at your files
The transformation definition ATX created is reusable. Publish it to your registry and any team member can run it against their Oracle stored procedures. Each execution improves the accumulated lessons through continual learning.



Resources

  • Test schema: Included in repo ( test-schema.sql ), creates all tables needed to validate the functions

References

[2] DB-Engines, "DB-Engines Ranking Trend," June 2026. Content rephrased for compliance.  https://db-engines.com/en/ranking_trend 
[3] AWS, "AWS Database Migration Service customers."  https://aws.amazon.com/dms/ 
[4] AWS, "AWS Transform pricing."  https://aws.amazon.com/transform/pricing/ 


Disclaimer: Opinions expressed are personal and do not represent the official position of any organization. All claims about ATX capabilities are verified against the  official documentation .
Any opinions in this article are those of the individual author and may not reflect the opinions of AWS.
Enjoyed reading this content? Let the author know!

Your likes, comments, shares, and saves help creators reach more builders.

Loading recommendations

Loading article