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 becomeCOALESCE(x, y)
-
RAISE_APPLICATION_ERROR(-20001, msg)→ completely different exception model
-
COMMIT/ROLLBACKinside 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

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-functionsgit init && git add . && git commit -m "initial: Oracle PL/SQL stored procedures"
aws configure # configure AWS credentials (or use env vars / a profile)atxI 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 matchesyour specific use case. Let me gather details to build the best transformationdefinition 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.sqlERROR: syntax error at or near "IS"LINE 13: CURSOR c_items IS
$ psql -f functions/get_customer_orders.pg.sqlCREATE FUNCTION ✅
$ psql -f functions/process_refund.pg.sqlERROR: loop variable of loop over rows must be a record variableLINE 48: FOR item IN (SELECT...
$ psql -f functions/update_inventory.pg.sqlERROR: syntax error at or near "IS"LINE 11: CURSOR c_order_items ISOnly
get_customer_orders passes. The other three have two distinct issues:- Cursor declaration syntax: Oracle uses
CURSOR c IS SELECT.... ATX converted toCURSOR c FOR SELECT...which is still wrong. PostgreSQL requires eitherc CURSOR FOR SELECT...(variable-name-first) or eliminating the cursor entirely.
- Implicit FOR loop variables: Oracle's
FOR item IN (SELECT...) LOOPworks without declaringitem. PostgreSQL requiresitem 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.sqlCREATE FUNCTION ✅
$ psql -f functions/get_customer_orders.pg.sqlCREATE FUNCTION ✅
$ psql -f functions/process_refund.pg.sqlCREATE PROCEDURE ✅
$ psql -f functions/update_inventory.pg.sqlCREATE 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 lockingCALL 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 trailCALL 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 rejectionCALL process_refund(1, 'Duplicate attempt', NULL);-- ERROR: Order 1 is not eligible for refund. Status: REFUNDED ✅
-- Test: Insufficient stock errorCALL 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; $$; |

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/bashPLSQL_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 -tdoneCost: 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 filescd your-oracle-proceduresatx
# Describe the conversion and point at your filesThe 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
- AWS Transform Custom docs: docs.aws.amazon.com/transform/latest/userguide/custom.html
- ATX pricing: $0.035/agent-minute
References
[1] Oracle, "Oracle Database Pricing," 2026. https://www.oracle.com/database/technologies/oracle-database-pricing.html
[2] DB-Engines, "DB-Engines Ranking Trend," June 2026. Content rephrased for compliance. https://db-engines.com/en/ranking_trend
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 .
Enjoyed reading this content? Let the author know!
Your likes, comments, shares, and saves help creators reach more builders.
Loading recommendations
Loading article