Oracle-to-Postgres migration seems simple at first, but everything changes when you hit the DBMS_* packages. The 200,000 lines of procedural logic your application has built on Oracle’s built-in package ecosystem — job scheduling, optimizer statistics, transactional messaging, file I/O, and more — don’t migrate automatically, and no tool will tell you that until you’re already over budget. Replacing them means reconstructing decades of kernel-level behavior from scratch.

This guide covers which packages cause the most damage, what replacement actually looks like in practice, and how to build a migration plan with honest numbers in it.

Introduction: the common scenario I’ve seen all too often

Everyone obsesses over table schemas and indexes and, once they’re finished, they announce their success in migrating the database to the new database platform. That’s great! However, they fail to mention the 200,000 lines of PL/SQL wrapped inside DBMS packages that make your database work – or how it’ll absolutely break you in the ‘race’ to migrate from Oracle. 

I’ll begin by describing a scene that plays out in conference rooms across the industry. A scene that I’ve watched for over a decade now.

It’s quite simple: a CTO walks in armed with a spreadsheet. On the left column of said spreadsheet are Oracle licensing costs, which are at least seven figures, growing annually, and negotiated under duress every renewal cycle.

On the other side of the spreadsheet: a projected cloud-native PostgreSQL bill, which is dramatically lower. So, of course, the room agrees this is great. The costs of the upcoming cloud migration and planned AI initiatives will be slashed. And just like that, the organization is given the green light to migrate away from Oracle. 

Fast-forward 18 months to 2 years and it’s a different story. The project is massively over budget, the team is exhausted, half the business-critical functionality is broken in ways that only show up under production load, and the organization still has vendor lock-in.

The spreadsheet didn’t lie per se – the numbers it gave were right – but it’s what it was measuring that was wrong.

What this article is not about

I’m not here to talk about table migration, because that part was solved a long time ago. Tools like ora2pg, AWS Schema Conversion Tool, and others can get your DDL (data definition language) across the wire with easy fidelity. The indexes, constraints, and even foreign keys come over without a hitch.

The problem is, there’s going to be those that signal the celebration without realizing what hasn’t come over and what can’t come over automatically. Oracle has spent four decades baking into its procedural DNA a very powerful and complex category called the DBMS_* package ecosystem. 

The code you can’t read

Oracle ships its built-in packages as wrapped, compiled bytecode – meaning you can call DBMS_SCHEDULER.CREATE_JOB and read its documented API, but you can’t read – under any circumstances – its implementation. Effectively, it’s a very beneficial ‘black box’ delivered with your license. 

This matters enormously during migration because, if your organization has PL/SQL developers, it’s almost certainly built years of logic on top of these packages. Every call to a DBMS_* package is a dependency on behavior that Oracle doesn’t document fully, with performance tuned over decades and behavior that your developers have come to rely on in ways they don’t even consciously recognize.

There are over 200 DBMS_* – and another 40 or so UTL_* packages – available for use in Oracle version 19c or newer. This article will touch on only a few of these as examples of what challenges a migration may face due to their use. 

The first challenge: the code is wrapped

The code is wrapped, so developers simply can’t see the code ‘under the covers.’ This is challenge number one.

Here’s an Oracle DBMS metadata view wrapped package body (and what you’ll see):

You can’t reverse-engineer it or diff it against a PostgreSQL equivalent. You can only read the documentation, observe the behavior, and attempt to reproduce it in a different platform environment. The code is functionally, semantically, and performatively hidden from anyone outside of those internal at Oracle.

The core problem is that Oracle’s DBMS packages are not just APIs, but are opaque, compiled executables that interact deeply with the Oracle kernel: the shared memory pool, the undo tablespace, the redo log, and the scheduler. Replacing them means replacing not just function signatures, but decades of kernel-level integration. 

The DBMS_* universe: a taxonomy of pain

Specifically, here are the most commonly relied-upon Oracle built-in packages. I’ll mention what they actually do, and why replacing them is genuinely hard work.

DBMS_SCHEDULER 

Enterprise job scheduling with calendaring syntax, job chains, windows, resource groups, and distributed execution. 

The facts: pg_cron covers maybe 30% of this. 

DBMS_STATS 

Optimizer statistics gathering, histogram management, pending stats, extended stats, and stat locking. 

The facts: ANALYZE is still in its infancy. 

DBMS_SQL 

Dynamic SQL execution with fine-grained cursor control, bulk binds, and describe capability. 

The facts: EXECUTE IMMEDIATE gaps are non-trivial.

DBMS_PIPE 

Synchronous and asynchronous inter-session messaging within the database. 

The facts: no direct PostgreSQL equivalent exists.

DBMS_ALERT 

Transactional event notification alerts fire only when the triggering transaction commits. 

The facts: LISTEN/NOTIFY lacks transactional binding.

DBMS_LOB 

Fine-grained LOB manipulation: chunked reads, writes, appends, substr, instr on BLOBs and CLOBs. 

The facts: large objects in PostgreSQL differ semantically, so this is an apples-to-oranges comparison. 

DBMS_CRYPTO 

Encryption, hashing, MAC generation using AES, 3DES, RSA, SHA variants. 

The facts: pgcrypto maps well, but not identically. However, it can compete! 

DBMS_OUTPUT 

Buffer-based text output from PL/SQL. Ubiquitous in debugging and reporting procedures. 

The facts: RAISE NOTICE is not semantically equivalent.

DBMS_UTILITY 

Compilation, dependency analysis, name resolution, comma-string parsing, call stack introspection. 

The facts: no single PostgreSQL substitute exists. 

DBMS_XMLGEN 

SQL-to-XML conversion with full control over element naming, null handling, and row encapsulation. 

The facts: xmlforest/xmlelement behavior differs.

DBMS_PARALLEL_EXECUTE 

Chunk-based parallel DML (data manipulation language) for large table operations with restart capability. 

The facts: no native equivalent; requires custom design. 

UTL_FILE 

Server-side file I/O, reading and writing OS files from within PL/SQL. 

The facts: pg_read_file is read-only, and restricted.

Cases where it gets brutal (case A: DBMS_SCHEDULER)

On the surface, DBMS_SCHEDULER looks replaceable. You have jobs? Just use pg_cron and you’re done. Except it’s not done – and experienced teams know that within the first week. 

Oracle PL/SQL – a DBMS_SCHEDULER job chain (simplified) 

Notice what this one job does? It uses a calendaring expression that explicitly excludes NYSE holidays by name. It runs in a named job class with resource allocation, and is part of a dependency chain where step 2 can’t begin until step 1 completes. That dependency is managed inside the database. 

With pg_cron you get: SELECT cron.schedule('0 18 * * 1-5', $$CALL load_positions()$$); 

That’s Monday through Friday at 6pm. It has no concept of market holidays, no job chaining, and no resource classes. To replicate the Oracle behavior fully, you need an external orchestration layer (meaning Apache Airflow, Prefect, Temporal)…which also means your scheduling logic leaves the database.

This creates problems further down the line, as the scheduling logic now crosses a network boundary and becomes  new infrastructure dependency. Dependency that falls onto your team – who now has to operate, monitor, and maintain it.

Case B: DBMS_STATS

We’ll call this one the ‘silent killer.’ It breaks slowly, through query plan degradation that only starts many weeks after go-live, as the data distribution begins to drift from what the optimizer saw at the time of migration.

Oracle PL/SQL – fine-grained statistics management 

PostgreSQL’s ANALYZE is coming along nicely, but has the following shortfalls:

No concept of pending statistics.

Lack of column group (extended) statistics configuration of this granularity.

No built-in sampling percentage control per column.

And finally, no mechanism to lock statistics on stable tables while aggressively refreshing volatile ones.

DBAs who spent years tuning Oracle’s optimizer through DBMS_STATS will find themselves starting over. And they very often won’t understand why certain queries that ran in milliseconds in Oracle are now scanning millions of rows in PostgreSQL.   

Case C: DBMS_PIPE and DBMS_ALERT

These two packages represent something that simply does not have a PostgreSQL native equivalent: transactional inter-session communication that lives entirely inside the database. 

Oracle PL/SQL – transactional alert pattern 

The critical property here is one that’s nearly invisible until it breaks. The alert is transactional and it fires if – and ONLY if – the transaction commits. Otherwise, a rollback suppresses it.

PostgreSQL’s LISTEN/NOTIFY don’t have this guarantee, however, so a notification sent inside a transaction still gets delivered to listeners even if it’s rolled back. Replicating this behavior is awkward, non-trivial, and most certainly was not part of your migration plan.

Specifically, it requires building a custom transactional outbox pattern: inserting notifications into a table within the transaction, then having a background worker read and dispatch them post-commit.

PL/SQL is not PL/pgSQL 

Beyond the DBMS packages, the procedural language itself introduces a category of migration problems that automated tools handle poorly at best. 

Oracle PL/SQL – patterns that have no direct PL/pgSQL equivalent 

Each of these patterns, unfortunately, require more than just syntax translation.

Autonomous transactions in PostgreSQL require a dblink or postgres_fdw – a network hop to a connection back to the same database. This is both architecturally odd and has performance implications.  

Meanwhile, Package-level session state requires rearchitecting around pg_temp schemas or application-layer caching. BULK COLLECT / FORALL  maps imperfectly to PL/pgSQL’s array handling, and also loses some of its performance characteristics in the translation.

In summary…

The migration tools will confidently convert your PL/SQL to PL/pgSQL. What they won’t tell you is that the converted code will compile, run, and produce wrong answers in production under conditions the automated tests never exercised.

Congratulations! You now belong to AWS, Google, or Azure

Here’s where the migration story gets a second, underappreciated chapter. Organizations rightly worry about Oracle licensing, so they run toward PostgreSQL because it’s open-source. This is a true statement.

However, “open-source database” and “no vendor lock-in” are not the same thing – especially when your destination is a managed cloud service.

What “open-source PostgreSQL” actually looks like at scale on the cloud

Amazon Aurora PostgreSQL is not the same as open-source PostgreSQL. Instead, it’s a PostgreSQL-compatible interface sitting on top of a proprietary distributed storage engine, deeply integrated with AWS infrastructure.  

Its performance profile, failure modes, scaling behavior…these are AWS’s, not the PostgreSQL community’s. The same is true of Google’s AlloyDB, which adds a proprietary columnar acceleration layer. These are excellent products but also have vendor lock-in, just dressed in the clothing of open-source. 

The extensions trap

The moment your team starts using aws_s3, Aurora-specific global database features, AlloyDB’s columnar store hints, or Azure-specific authentication integrations, you have rebuilt your lock-in dependency. You just moved it from an Oracle Enterprise License Agreement to an AWS Enterprise Discount Program negotiation. 

The hyperscalers are not naive about this: their managed database services are deliberately designed to be excellent, and to accumulate switching costs naturally through network effects, deep integrations, and proprietary extensions.  

They win when you migrate to their platform, and there’s a reason migration away from their platform becomes difficult like it is from other platforms, such as Oracle. 

The spreadsheet that lied

Let’s revisit that boardroom spreadsheet we mentioned at the start. The licensing cost comparison is real – sure, Oracle is expensive. But the column labeled “savings” is systematically missing several entries: 

This is not an argument against migration. Indeed, there are times where migration genuinely makes sense for teams with smaller Oracle footprints – or for organizations whose Oracle usage is simple enough that the DBMS_* dependency surface is manageable. Instead, the argument is against doing the math wrong and calling the result a victory. 

Shifting Oracle licensing cost to cloud infrastructure cost is not saving money. It’s simply moving a number from the left column to the right column of a budget spreadsheet, then presenting the left column to the CFO as evidence of savings…while hoping nobody notices that the right column grew. 

A more honest migration framework (what you should do)

If your organization is evaluating or mid-stream in an Oracle migration, here is a more grounded approach to the decision: 

Step 1: Audit your DBMS_* dependency first 

Before any migration decision is finalized, run a full dependency audit of every DBMS_* call in your codebase. Not just which packages are used, but also which specific procedures, which behaviors, and which edge cases your developers have come to rely on. This is not a two-hour query, but more like weeks of analysis. 

Oracle DBA – query to surface DBMS_* dependencies across all schema objects 

Step 2: Classify by ‘replaceability’ 

Not all DBMS_* usage is equally difficult. DBMS_OUTPUT.PUT_LINE is trivially replaced, whereas DBMS_SCHEDULER job chains with holiday calendars are not.

So, build a tiered inventory: trivial replacements, hard replacements requiring architectural decisions, and functionally irreplaceable behaviors that would require rebuilding application logic from scratch. 

Step 3: Model the full cost (including time-to-risk) 

The risks from an incomplete DBMS_* replacement only surface later down the line – usually in production and under load, in edge cases. Budget for a sustained post-migration engineering stabilization period must be realistically built, including two to three years for a complex Oracle workload.

If that cost makes the business case weaker, so be it. It’s the correct, honest information, and doesn’t need any more explanation than that.

Step 4: Evaluate cloud-independent deployment 

If you migrate to PostgreSQL, consider what option gives you better long-term positioning than a full managed, cloud-native service. Would a self-managed PostgreSQL on cloud virtual machines (VMs) do the job, or does a simple on-premises approach suffice?

I know this may be unpopular but, before you swap one lock-in for another, make sure you’ve only lost some operational convenience. If you gain portability and predictable cost, then you can decide if the cloud makes sense after proving it out. For large, stable workloads, the math often favors the latter. 

Step 5: Consider Oracle alternatives that don’t require full migration 

License cost negotiation with Oracle is not fixed. Oracle’s licensing practices are aggressive, but so is competition and organizations with leverage can be persuasive if they are actively evaluating alternatives.  

Most find that Oracle’s negotiating floor is significantly lower than their current contract and a credible migration threat, even a partial one, changes the conversation completely. This sometimes even leads to discounts upwards of 80% for large enterprise customers. 

The goal is not loyalty to Oracle, but in making decisions with accurate data about true total cost. This includes the cost of what you’d have to rebuild, and the cost of the new lock-in you’d acquire on the way out. 

The migration that actually works

Successful Oracle migrations do exist, and they all look quite similar. They took longer than initially planned, were led by engineers with familiarity of both platforms, and there was heavy investment in understanding and replacing DBMS_* behavior before cutting over. And they were honest with leadership about the real costs throughout.

What they did not do is treat the database schema as the hard part, because the schema is, in fact, the easy part.

The hard part is the 200,000 lines of accumulated behavioral logic that Oracle has made fast, reliable, and invisible over 40 years. The very logic that your application has silently learned to trust. 

The DBMS packages are wrapped because Oracle’s IP requires it. But they are also, in a sense, wrapped in your organization’s institutional knowledge, production incident history, and in the edge cases your DBA patched at 2am. That knowledge is what doesn’t migrate – it has to be reconstructed, tested, and earned again on the new platform. 

Know what you’re buying when you sign the migration project plan, and make sure the right column of that spreadsheet has honest numbers in it. 

And remember: there is no such thing as a free migration. There are only migrations where the true costs show up later, and migrations where you accounted for them honestly from the start.