Convert Oracle PL/SQL to PostgreSQL: stored procedure migration tools, what converts automatically, and the constructs that convert cleanly and return different answers
10 min read Migration The Adapters team
Last updated September 2026
Field mapping auto-plugged · tap a port to rewire
Plug a source port into
Transform on this cable
JSON in
JSON out
5 sample records ready
Oracle PL/SQL converts to PostgreSQL PL/pgSQL partly, and the two organizations best placed to know both say so in their own documentation. AWS publishes an automation rating for every Oracle feature area in its Oracle to Aurora PostgreSQL playbook, and rates two areas at no automation at all. The Ora2Pg project describes its own PL/SQL conversion as basic and states that generated code has to be reviewed. What follows is the construct-by-construct list, the shorter list that experienced teams actually worry about, and three places where the AWS playbook has fallen behind modern PostgreSQL.
Key takeaways
- Two areas carry no automation. AWS rates MERGE statements and database links as requiring entirely manual work, and describes database links as needing a full rewrite of the mechanism.
- The vendors are honest about the gap. Ora2Pg calls its PL/SQL to PL/pgSQL conversion basic and says functions, procedures, packages and triggers must be reviewed by hand.
- The dangerous list is the converted one. GREATEST, LEAST, SYSDATE and bind variable handling all convert successfully and are separately flagged as possibly behaving differently.
- Empty exception blocks are the worst outcome. The converter can produce a handler with no body, which swallows the error and reports success.
- Some of the playbook is stale. It lists MERGE, GROUPING SETS and SKIP LOCKED as unsupported by PostgreSQL. All three are supported now. Check every claim against your target version.
- Estimate from procedure count, not data volume. The rows move in hours. The packages are what take quarters.
Can Oracle PL/SQL be converted to PostgreSQL automatically?
Partly, and both major tools say so themselves rather than leaving you to find out. The Ora2Pg documentation states that the tool does its best to convert an Oracle database automatically but that there is still manual work to do, and that the Oracle-specific PL/SQL generated for functions, procedures, packages and triggers has to be reviewed to match PostgreSQL syntax. It describes the conversion of PL/SQL to PL/pgSQL as basic. AWS takes a different approach and publishes an automation rating per feature area, on a five-level scale from full automation down to no automation.
Those ratings are the most useful public estimate of Oracle migration effort that exists, and almost nobody reads them. Tables, data types, indexes, views, flow control and transaction isolation sit at high automation. SQL, stored procedures, triggers, cursors, user-defined types, sequences, partitioning and query hints sit at medium. Materialized views drop to low. MERGE statements and database links are rated no automation at all. The full area-by-area table is on our Oracle to PostgreSQL migration tools page.
What is the equivalent of PL/SQL in PostgreSQL?
PL/pgSQL, and the resemblance is close enough to be a trap. Both are block-structured procedural languages with DECLARE, BEGIN, EXCEPTION and END, both support cursors and loops, and a simple procedure often moves across with only cosmetic edits. That surface similarity is why teams underestimate this work. The differences are not in the syntax you can see, they are in the semantics underneath: transaction behavior, how a name resolves when a variable and a column share it, and what happens to the rest of the block when an exception is caught.
PostgreSQL also has no packages, which is the structural difference with the largest knock-on effect. An Oracle package bundles related procedures with private state that persists for the session. PostgreSQL has schemas, which give you the grouping and the namespace but not the state. Package variables have to move somewhere else, usually a table or a session-level setting, and that decision ripples through every procedure that read them.
How do I convert an Oracle stored procedure to PostgreSQL?
Run a converter first, then treat its output as a draft with a defect list attached. Point Ora2Pg or AWS DMS Schema Conversion at the schema, read the exception report, and count the objects by type rather than by percentage. The percentage converted is always flattering because most objects in any schema are ordinary tables. The count of packages, procedures and triggers on the exception list is what predicts the schedule.
Then work through the constructs below in order of how badly they behave. Anything AWS rates as no automation is guaranteed hand work. Anything it converts with a warning is worse, because it will pass a compile and fail in production. And decide the conversion settings deliberately: whether procedures become void functions or PostgreSQL procedures, whether unsupported built-ins become stubs, and whether the orafce extension is available on the target, because orafce reimplements a long list of Oracle built-in functions and every one of them is off by default.
Which Oracle constructs do not convert to PostgreSQL?
Twenty of them, with the replacement for each. The status column reflects what the converter and the target engine do today, which is not always what the AWS playbook says, and the rows where those disagree are called out.
| Oracle construct | Status | What you write instead |
|---|---|---|
| Packages | No equivalent | A schema per package, with the procedures as functions inside it. Package-level variables need a different home entirely, usually a table or a session setting. |
| PRAGMA AUTONOMOUS_TRANSACTION | Not supported | A loopback connection through dblink or postgres_fdw, or move the work out of the transaction. AWS lists this explicitly: PostgreSQL does not explicitly support autonomous transactions. |
| MERGE | Not converted | PostgreSQL 15 and later support MERGE natively, so the statement often moves across unchanged. The converter still will not do it for you. |
| Database links | Full rewrite | postgres_fdw foreign tables, or move the call into the application. Note that the foreign data wrapper does not support user-defined functions. |
| Synonyms | Not supported | A view over the target object, or a search_path that resolves the name. There is no direct replacement. |
| BULK COLLECT INTO | Not supported | Ordinary set-based SQL, or array_agg into an array variable. Most BULK COLLECT loops exist to work around Oracle context switching that PostgreSQL does not have. |
| FORALL | Not supported | One set-based statement. The construct exists purely as an Oracle performance workaround. |
| Associative arrays | Not supported | A PostgreSQL array, an hstore, or a temporary table when the keys are not integers. |
| TYPE ... IS REF CURSOR | Not supported | The built-in refcursor type. Global cursors have no equivalent and get converted to local ones. |
| DBMS_SQL | Partially supported | EXECUTE with format(). AWS states PostgreSQL does not support all features of the DBMS_SQL package. |
| Dynamic SQL | Not converted | Hand translation. AWS states plainly that it cannot convert statements with dynamic SQL, and flags converted code that touches it as possibly incorrect. |
| GOTO | Not supported | Restructured control flow. Same for conditional compilation. |
| SAVEPOINT inside routines | Not supported | A BEGIN ... EXCEPTION block, which creates an implicit subtransaction. |
| ROWID and UROWID | Not supported | A surrogate key column. The converter can emulate ROWID with a bigint if you turn that setting on, and it is off by default. |
| Virtual columns | Not supported by the converter | PostgreSQL generated columns, which cover most cases. This is another entry where PostgreSQL has caught up and the playbook has not. |
| Index-organized tables | Not supported | An ordinary table plus CLUSTER on the primary key index, accepting that PostgreSQL clustering is a one-time reorganization. |
| Bitmap and domain indexes | Not supported | B-tree, partial or expression indexes. Bitmap index scans exist in PostgreSQL as a planner strategy, not as an index type. |
| Compound triggers | Not supported | Separate statement-level and row-level triggers. FOLLOWS and PRECEDES ordering is gone, so name triggers carefully because PostgreSQL fires them alphabetically. |
| Java stored routines | Not converted | Application code, or PL/Java if you genuinely must keep it in the database. |
| Nested tables and VARRAY of VARRAY | Not supported | Child tables, or jsonb where the structure is genuinely variable. |
Does PostgreSQL support autonomous transactions?
No, and AWS says so directly in its action code list: PostgreSQL does not explicitly support autonomous transactions. This matters more than its position on the list suggests, because autonomous transactions are usually load-bearing. The classic use is an audit or error log that must survive a rollback of the main transaction, and if that write gets folded back into the parent transaction it disappears exactly when someone needs it most.
There are three honest options. Open a loopback connection with dblink or postgres_fdw so the write commits independently, which works and costs a connection per call. Move the logging out of the database and into the application or a queue. Or accept that the record is transactional and design around it. What does not work is assuming a converter handled it, because the converted code will compile and the log entry will vanish on every rollback.
Which converted code returns different answers?
This is the shorter list and the one worth reading twice. Every row here converts, most of them at a high automation rating, and every one carries a separate AWS warning that the behavior may differ from Oracle. A converter that reports ninety-five percent success has still handed you these, and they do not show up in a compile, a smoke test, or a row count.
| Construct | Converter says | The warning | Why it bites |
|---|---|---|---|
| GREATEST and LEAST | High automation | AWS flags both as possibly producing different results than the source | Null handling and implicit type coercion differ between the engines, so the same arguments can return a different row. |
| Empty exception blocks | Converted | AWS raises an action item stating the exception block in the converted code is empty | An empty handler swallows the error. The procedure returns success and the work did not happen. |
| SYSDATE | Emulated | The emulating function depends on the time zone settings | Unless you pin the source time zone in the conversion settings, every date written by converted code can be offset. |
| Bind variable names | Converted | AWS warns converted code might not work correctly because of the bind variable names | Name resolution between a variable and a column differs, and PL/pgSQL will happily resolve to the wrong one. |
| Timestamps in partitioned code | Medium automation | AWS warns the timestamp data type in converted code might produce different results | Oracle DATE carries a time component and PostgreSQL DATE does not, so a boundary comparison shifts. |
| Calls to user-defined functions | Converted | AWS warns converted code might not work correctly because of the user-defined functions | The called function may itself have converted with an action item, so the caller inherits a defect it cannot see. |
| Unsupported built-ins as stubs | Optional setting | The stub has the same signature, so the converted object compiles | A schema full of compiling stubs deploys with zero errors and does none of the work. |
| Converted sequences | Medium automation | Sequences start at their initial value unless you turn on the setting that carries the last value across | The first insert after cutover collides with keys that already exist. |
The empty exception block is the one to fix first. An Oracle handler that logged and re-raised can convert into a PL/pgSQL handler with nothing in it, and a handler with nothing in it catches the error, does nothing about it, and lets the procedure return normally. The caller sees success. Every one of those needs opening by hand, and there is no way to find them other than reading the action items.
Where the AWS playbook is out of date
Worth knowing before you plan a rewrite around it. The action code index lists three things as unsupported by PostgreSQL that PostgreSQL now supports. MERGE statements are listed as unsupported, and PostgreSQL has had MERGE since version 15. GROUPING SETS, CUBE and ROLLUP are listed as unsupported, and all three arrived in PostgreSQL 9.5. FOR UPDATE SKIP LOCKED is listed as unsupported, and SKIP LOCKED also arrived in 9.5.
The automation ratings are still correct in the sense that matters: the converter will not translate these for you, so they remain hand work. But the hand work is much smaller than the playbook implies. A MERGE statement on a PostgreSQL 15 or later target often moves across close to unchanged rather than being rewritten as an INSERT with an ON CONFLICT clause, and a report using ROLLUP does not need restructuring at all. Check the version you are actually targeting before you budget a rewrite, because a playbook written against an older PostgreSQL will cost you weeks that the engine has already given back.
How long does converting Oracle PL/SQL to PostgreSQL take?
Estimate from the number of procedures, not the number of gigabytes. Ora2Pg produces a migration cost assessment in person-days, which is genuinely useful, but read the assumption underneath it. Its default cost unit is five minutes, and the documentation states that five minutes corresponds to a migration conducted by a PostgreSQL expert. A team learning PostgreSQL on this project is not that, so the estimate is optimistic by construction rather than by accident. Ora2Pg also switches its difficulty grading from B to C at a default of ten person-days, so a project that crosses that line has been told something by the tool.
A rough shape that holds up: a schema that is mostly tables and views moves in weeks. A schema with a few hundred procedures that avoid the constructs in the first table moves in a quarter. A schema with thousands of procedures built on packages, autonomous transactions and DBMS_SQL is a multi-quarter program, and it is the case where buying Oracle compatibility from EDB instead of removing it deserves a serious look. The data movement is not the variable in any of these. Once the code is settled, the rows are a full load plus change capture, and the mechanics of that are covered in change data capture tools.
What to do after the schema is PostgreSQL
Two things usually remain, and both are worth planning before cutover rather than after. The first is that the Oracle instance rarely switches off on schedule. Something keeps reading it, a report depends on it, or a second system needs the same data continuously, and that leftover is an ongoing integration with a permanent cost rather than a migration with an end date. Decide in week one whether one field mapping can serve both the move and what follows, because discovering it in week ten means buying a second tool under time pressure.
The second is that business logic which used to live in packages is now spread between the application and ad-hoc SQL, and the people who used to ask a DBA for a report will start writing queries themselves. That is mostly a good outcome, and it goes better when analysts can ask questions in plain English and get the SQL back rather than learning PL/pgSQL to answer a question about last quarter. Pair that with a reconciliation job that compares row counts and column sums between the two databases for the length of the parallel run, and you will find the differences while both systems are still available to compare.
The short version
Convert with Ora2Pg or DMS Schema Conversion, read the exception report rather than the success percentage, and count packages and procedures because they are the schedule. Budget hand work for MERGE and database links, which carry no automation, and for autonomous transactions, BULK COLLECT, FORALL, associative arrays, synonyms and DBMS_SQL, which have no direct equivalent. Then spend the same effort again on the code that converted successfully and carries a behavior warning, because GREATEST, LEAST, SYSDATE, bind variables and empty exception blocks are the ones that reach production. And verify every unsupported claim against the PostgreSQL version you are actually targeting, because the reference material is older than the engine.
The tool comparison, the platform limits that decide the cutover plan and the eight failures that report success are on Oracle to PostgreSQL migration tools. The equivalent route off SQL Server is in SQL Server to PostgreSQL migration, and the budget model behind either program is in what a data migration really costs. Where the destination under consideration is a warehouse rather than PostgreSQL, the same Oracle source is compared against Snowflake in Oracle to Snowflake migration tools, and the column by column detail is in Oracle to Snowflake data type mapping.
Once the schema is PostgreSQL, keep it in agreement with everything else
Map the fields once, run the backfill, then let the same mapping run incrementally with retries, alerts and per-record logs. From $49 a month, never metered by rows.
No credit card required.