Oracle PL/SQL to Serverless PostgreSQL: A 2026 Hands-On Migration Guide

Why Teams Move PL/SQL Off Oracle in 2026

The pressure is mostly economic: Oracle licensing and support costs keep rising, and PostgreSQL has become the default on every major cloud. What stops most migrations is not the tables — it is the PL/SQL. A mature Oracle system carries decades of business logic locked inside packages, procedures, triggers, and functions. That code is where the migration risk lives.

This guide is the hands-on counterpart to our broader Oracle vs PostgreSQL comparison. Here we skip the feature debate and focus on one thing: how to actually move PL/SQL to PostgreSQL — and how serverless changes the rules.

First Decide: Three Migration Targets

Before converting a single line, pick where the logic is going. There are three realistic targets, and mixing them up is the most common mistake.

1. Lift to PL/pgSQL — translate procedures and packages into PostgreSQL functions that run inside the database. Lowest risk, highest reuse. This should be your default for set-based logic.
2. Rewrite to serverless functions (Lambda / Cloud Functions) — move logic into application code outside the database. Right for orchestration, external API calls, and event handling. Wrong for heavy row-by-row data work.
3. Hybrid — keep data-intensive logic in PL/pgSQL, move scheduling and integration into serverless. This is what most successful migrations actually end up as.

The temptation in 2026 is to "modernize" everything into Lambda. Resist it. A stored procedure runs next to the data inside a transaction; a Lambda function talks to the database over the network, statelessly. Move a tight set-based loop into Lambda and you trade one in-database query for thousands of round trips, plus connection-pool pressure and cold starts.

Convert Oracle PL/SQL to PostgreSQL with AI. AI2SQL translates procedures, queries, and dialect-specific functions across Oracle, PostgreSQL, and 8+ databases — useful for the manual "last mile" that automated tools leave behind.

PL/SQL to PL/pgSQL: The Conversion Map

Most of the everyday conversion work is mechanical. Keep this table next to you — it covers the substitutions you will hit most often.

Oracle PL/SQL	PostgreSQL / PL/pgSQL	Notes
NUMBER	NUMERIC / INTEGER / BIGINT	Pick integer types where scale is 0
VARCHAR2(n)	VARCHAR(n) / TEXT	TEXT has no length penalty in PG
DATE	TIMESTAMP	Oracle DATE includes time
CLOB / BLOB	TEXT / BYTEA
NVL(a, b)	COALESCE(a, b)	COALESCE is variadic
DECODE(x, 1,'a', 2,'b', 'c')	CASE expression	No DECODE in PG
SYSDATE	CURRENT_TIMESTAMP / NOW()
ROWNUM <= n	LIMIT n / ROW_NUMBER()	Use window fn when ordering matters
SELECT ... FROM DUAL	SELECT ...	Drop DUAL entirely
seq.NEXTVAL	nextval('seq')	Function-call syntax
LISTAGG(c, ',')	STRING_AGG(c, ',')
CONNECT BY	WITH RECURSIVE CTE	Full rewrite
PACKAGE	schema + functions	No package concept
PRAGMA AUTONOMOUS_TRANSACTION	dblink / extension	Not natively supported

For a printable syntax reference, see our Oracle SQL cheat sheet and PostgreSQL cheat sheet.

Rewriting a Procedure

An Oracle procedure becomes a PostgreSQL function (or, since PostgreSQL 11, a PROCEDURE when you need transaction control inside the body). The shape is similar; the details differ.

-- Oracle PL/SQL
CREATE OR REPLACE PROCEDURE add_credit(p_user_id NUMBER, p_amount NUMBER) IS
    v_balance NUMBER;
BEGIN
    SELECT balance INTO v_balance FROM accounts WHERE user_id = p_user_id;
    UPDATE accounts SET balance = NVL(v_balance, 0) + p_amount
    WHERE user_id = p_user_id;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        INSERT INTO accounts (user_id, balance) VALUES (p_user_id, p_amount);
END;
/

-- PostgreSQL PL/pgSQL
CREATE OR REPLACE PROCEDURE add_credit(p_user_id BIGINT, p_amount NUMERIC)
LANGUAGE plpgsql AS $$
DECLARE
    v_balance NUMERIC;
BEGIN
    SELECT balance INTO v_balance FROM accounts WHERE user_id = p_user_id;
    UPDATE accounts SET balance = COALESCE(v_balance, 0) + p_amount
    WHERE user_id = p_user_id;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        INSERT INTO accounts (user_id, balance) VALUES (p_user_id, p_amount);
END;
$$;

Note the differences: the $$ dollar-quoted body, the explicit LANGUAGE plpgsql, NVL → COALESCE, and number types. The NO_DATA_FOUND exception works the same way — one of the parts that ports cleanly.

Packages Have No Equivalent

This is the structural break that surprises teams. PostgreSQL has no PACKAGE. The standard pattern:

• Map the package to a schema (CREATE SCHEMA billing;).
• Turn each packaged procedure/function into a standalone function in that schema (billing.add_credit(...)).
• Replace package-level variables with set_config() / current_setting() session settings, or a small state table.
• Consider the orafce extension to emulate common Oracle built-ins (DBMS_OUTPUT, NVL, date helpers) and cut rewriting.

The Serverless Gotchas Nobody Warns You About

If your target is serverless PostgreSQL (Neon, Supabase, Aurora Serverless v2) or you are pushing logic into Lambda, four things will bite you that never existed on a always-on Oracle box.

1. Connection pooling is mandatory. Lambda can spawn hundreds of concurrent executions, each opening a database connection. PostgreSQL handles connections with a process per connection, so it falls over far sooner than Oracle's shared-server model. You must put a pooler in front: RDS Proxy, PgBouncer, or the platform's built-in pooler (Neon, Supabase). Use transaction-mode pooling for serverless.

2. Cold starts compound. A Lambda cold start plus an Aurora Serverless v2 scale-up (or Neon scale-from-zero) can stack into multi-second first-request latency. Keep hot paths on a warm, pooled connection and accept cold starts only on background work.

3. In-database schedulers may not run. Oracle's DBMS_SCHEDULER and DBMS_JOB assume an always-on instance. Serverless Postgres that scales to zero will not keep a background worker alive. On an always-on instance use pg_cron; on serverless, move scheduling out: a cloud scheduler (EventBridge, Cloud Scheduler) triggers a function that runs the job through the pooler.

4. Autonomous transactions are gone. PRAGMA AUTONOMOUS_TRANSACTION (commonly used for logging that survives a rollback) has no native PostgreSQL equivalent. Options: dblink to open a separate connection, or move that side-effect into a serverless function called asynchronously.

Automating the Rewrite (and the 80/20 Reality)

Be realistic about what tools do. Rule-based converters — ora2pg, AWS DMS Schema Conversion — handle roughly the easy 80%: data types, simple functions, sequences, straightforward procedures. AWS now layers generative AI on top of DMS to push conversion rates toward 90%. But the last 20% — packages, autonomous transactions, hierarchical queries, Oracle built-ins — is where the real time goes, and it is mostly manual.

That last mile is where an AI assistant earns its place. Instead of hand-translating each gnarly procedure, paste it in, pick your target dialect, and review the output. AI2SQL converts individual procedures and queries between Oracle and PostgreSQL (and SQL Server, MySQL, Snowflake, BigQuery, and more), so you spend your time validating logic rather than looking up syntax. For the broader cross-dialect workflow, see our guide to converting SQL dialects with AI, or jump straight to the Oracle to PostgreSQL converter.

A Pragmatic Migration Order

1. Inventory every package, procedure, function, and trigger. Size the effort honestly.
2. Auto-convert the easy 80% with ora2pg / DMS; keep a diff.
3. Classify the remaining 20%: which stays in PL/pgSQL, which moves to serverless.
4. Rewrite packages as schemas + functions; replace schedulers and autonomous transactions.
5. Put a connection pooler in front before you load-test.
6. Validate behavior with real data — output parity matters more than line-by-line equivalence.

Migrating from MySQL instead of Oracle? The mechanics differ — see our MySQL to PostgreSQL migration guide.

Frequently Asked Questions

Can you convert Oracle PL/SQL to PostgreSQL automatically?

Partially. Tools like ora2pg and AWS DMS automate ~70-90% — data types, simple functions, sequences, straightforward procedures. Packages, autonomous transactions, complex cursors, and Oracle-specific built-ins usually need manual rewriting. AI assistants like AI2SQL help with that last mile by converting procedures and queries on demand.

Should you move PL/SQL stored procedures to AWS Lambda?

Usually not as a direct translation. A procedure runs inside the database session with set-based access; Lambda is stateless and talks over the network. Moving heavy data logic to Lambda adds latency, pooling pressure, and cold starts. Keep set-based logic in PL/pgSQL; move only orchestration, scheduling, and integrations to serverless.

Does PostgreSQL support packages like Oracle?

No. Map each Oracle package to a PostgreSQL schema, convert packaged routines into standalone functions, and replace package variables with session settings or a state table. The orafce extension emulates some Oracle built-ins.

What is the hardest part of a PL/SQL to PL/pgSQL migration?

Constructs with no direct equivalent: packages, autonomous transactions, DBMS_* built-ins, CONNECT BY hierarchies, and global temporary tables. These need redesign, not translation — the hard 20% where most migration time is spent.

How do you replace DBMS_SCHEDULER jobs on serverless PostgreSQL?

Serverless Postgres may scale to zero, so in-database schedulers are limited. On an always-on instance use pg_cron. On serverless, move scheduling out: a cloud scheduler (EventBridge, Cloud Scheduler, GitHub Actions cron) triggers a function that connects through a pooler and runs the job.