Mapping Oracle Feature Usage to PostgreSQL Equivalents

Three Categories, Not One Table

Feature mapping is usually presented as a two-column table, which hides the only distinction that matters. Some features have a direct equivalent and need a translation. Some need a different design that reaches the same outcome. And some have no equivalent, where the honest answer is an architectural change or a decision not to migrate that part at all.

Sorting your feature list into those three buckets is the useful exercise, because the third bucket is where projects get into trouble — not through difficulty, but through a translation being attempted where a decision was required.

Direct Equivalents

A large part of any schema lands here, and these are mostly mechanical once the rules are right.

Oracle                        PostgreSQL
----------------------------  ------------------------------------
VARCHAR2 / CLOB               varchar / text
NUMBER(p,s)                   numeric(p,s)
Sequences                     Sequences, or identity columns
GENERATED AS IDENTITY         GENERATED AS IDENTITY
B-tree indexes                B-tree indexes
Check / unique / FK           The same
Views                         Views
Materialized views            Materialized views (refresh differs)
Row-level triggers            Triggers calling a trigger function
Partitioning (range/list)     Declarative partitioning
CONNECT BY                    WITH RECURSIVE
Outer join (+) syntax         ANSI LEFT JOIN
DECODE                        CASE
NVL / NVL2                    COALESCE

Two in that list repay care despite looking simple. Unconstrained NUMBER becomes unconstrained numeric, which is correct and often not what you want: it is slower than an integer type and cannot carry an identity. Where a column is conceptually an integer, say so on the target. And materialized views map in name while their refresh semantics differ, so anything depending on refresh behaviour needs checking rather than porting.

Sequences: Easy to Map, Easy to Break

Sequences deserve their own note because they map cleanly and are still one of the most common sources of post-migration breakage. The object converts; the problem is its current value.

Load a table's rows without advancing the sequence that feeds its key, and the first insert after cutover collides with data that is already there. It passes every row count and checksum, because the data is correct — the counter is not. Reconcile every sequence past the maximum value actually present, and verify it rather than assuming it.

-- After the data lands, move the sequence clear of it
SELECT setval('app.orders_order_id_seq',
              COALESCE((SELECT MAX(order_id) FROM app.orders), 0) + 1,
              false);

The same applies to a sequence nothing in the schema references by default — one driven by application code or a trigger. A per-table migration has no way to attribute it to a table, so it needs finding deliberately at schema level.

Packages Become Schemas, and Lose Their State

A package converts into a schema containing functions, and the structural part is straightforward. What does not survive is package state: variables declared at package level that persist for a session.

Code relying on that state needs redesigning rather than translating. The usual replacements are a temporary table, a session-scoped configuration setting, or passing the value explicitly. All three work; the point is that this is a design change and the conversion should surface it as one rather than quietly producing something that compiles.

Overloading is worth checking too. PostgreSQL supports it, but resolution rules differ enough that heavily overloaded packages deserve a careful read.

No Equivalent: Where Decisions Live

Autonomous transactions are the clearest case. A procedure that commits independently of its caller — typically for audit logging that must survive a rollback — has no direct PostgreSQL form. The options are a background worker, a separate connection, or accepting that the write participates in the surrounding transaction. Each is a different behaviour and somebody has to choose.

Advanced Queuing is the other. The nearest mechanism is LISTEN and NOTIFY, and it is important to be clear that the guarantees differ: notifications are not durable, and a listener that is not connected does not receive them. If your queue needs persistence and redelivery, the right answer is usually a real broker outside the database rather than an approximation inside it.

Oracle                     Nearest option            What changes
-------------------------  ------------------------  ----------------------------
Autonomous transaction     Background worker, or     Independent commit is no
                           separate connection       longer implicit

Advanced Queuing           LISTEN/NOTIFY, or an      Durability and redelivery
                           external broker           are not equivalent

DBMS_SCHEDULER             pg_cron, or external      Scheduling may move out of
                           orchestration             the database

Flashback Query            Point-in-time recovery    Not queryable in-place

RAC                        Patroni / streaming       Shared-everything becomes
                           replication               shared-nothing

Scheduling Usually Leaves the Database

Scheduler jobs can go to pg_cron, and on a managed service that extension may or may not be available to you. Many teams take the migration as the moment to move scheduling out to whatever already orchestrates the rest of their estate, which tends to be better operationally — visible runs, real alerting, version-controlled definitions.

Either way it is a decision with an owner, and it is easy to leave until after cutover and then discover that a nightly process has not run for a week.

High Availability Changes Shape

RAC is a shared-everything cluster; PostgreSQL high availability is shared-nothing replication with a managed failover, typically via Patroni. These are not the same architecture and the mapping is not a translation.

What matters is which property you were relying on. If RAC was providing failover, replication with automated promotion covers it. If applications were distributing load across active instances writing to the same data, that assumption does not carry and the application needs to know about the distinction between a primary and a replica.

What the Mapping Should Produce

The output of this exercise is not a reference table — those exist already. It is a list specific to your schema in which every feature you actually use has a named target approach, and every item in the third category has a decision recorded against it with an owner.

Done properly it is short, and the short list is the valuable part: a handful of genuine design decisions, separated cleanly from the large volume of mechanical conversion that needs no decision at all.