Migrating Oracle to PostgreSQL or MongoDB: What Actually Breaks

In short: Migrating off Oracle to PostgreSQL or MongoDB is not a straight data-copy exercise. The parts that actually break — PL/SQL procedural logic, sequence and trigger behaviour, hierarchical query syntax, and application code that assumes Oracle-specific functions — are usually underestimated in the initial scoping, and that gap is where migration projects overrun. This is what to check before committing to a timeline.

PL/SQL is the biggest single line item

Oracle's procedural language, PL/SQL, has no direct equivalent — PostgreSQL's PL/pgSQL is deliberately similar in syntax but differs in enough specifics (exception handling semantics, cursor behaviour, package structures — PostgreSQL has no native concept of a PL/SQL package, so packaged procedures and functions typically need to be restructured into schemas of standalone functions) that a large PL/SQL codebase cannot be mechanically translated with full confidence. Automated conversion tools (Ora2Pg being the most common for Oracle-to-PostgreSQL) handle a meaningful share of straightforward procedures but reliably flag a residual percentage — commonly cited in the 10-30% range depending on codebase complexity — for manual rewrite, particularly anything using Oracle-specific packages like DBMS_* or complex exception handling.

For a migration to MongoDB the gap is larger still, because it is not a like-for-like relational engine — PL/SQL business logic generally has to move into the application layer entirely, which is a re-architecture, not a translation.

Sequences, triggers, and auto-increment behaviour

Oracle sequences (NEXTVAL/CURRVAL) map reasonably cleanly to PostgreSQL sequences, but the common Oracle pattern of a BEFORE INSERT trigger populating a primary key from a sequence is usually better replaced with PostgreSQL's native GENERATED ALWAYS AS IDENTITY or SERIAL columns rather than ported trigger-for-trigger — porting it as-is works, but carries the trigger's per-row overhead forward unnecessarily.

Hierarchical queries — CONNECT BY vs. recursive CTEs

Oracle's CONNECT BY PRIOR syntax for hierarchical queries (organisational charts, bill-of-materials trees, category hierarchies) has no direct PostgreSQL equivalent. It has to be rewritten as a recursive common table expression (WITH RECURSIVE), which is a different mental model and a different query structure, not a syntax swap — every CONNECT BY query in the codebase needs to be found and individually rewritten and tested.

Data type and function differences that break silently

Several categories of mismatch tend to surface late, during testing rather than during initial code review: Oracle's NUMBER type versus PostgreSQL's NUMERIC/INTEGER/BIGINT (Oracle's single flexible numeric type has to be mapped deliberately, or precision issues appear); Oracle's implicit date/string conversions, which PostgreSQL is stricter about; Oracle-specific functions (DECODE, NVL, TO_CHAR format masks) that have PostgreSQL equivalents but not identical syntax or edge-case behaviour; and Oracle's case-insensitive default behaviour in some contexts versus PostgreSQL's case sensitivity for unquoted identifiers.

Application-layer dependencies

Beyond the database itself, the application code connecting to it usually has Oracle-specific assumptions: JDBC/ODBC driver differences, connection pooling configured around Oracle's session model, and ORM mappings (if the application uses one) that may generate Oracle-dialect SQL by default and need reconfiguration for PostgreSQL's dialect. None of this shows up in a database-only migration assessment — it requires reviewing the application layer as part of scoping, not as a surprise during UAT.

A realistic scoping approach

The projects that stay on schedule are the ones that inventory PL/SQL object count and complexity, hierarchical query usage, and application-layer database dependencies before quoting a timeline — not after. A short assessment phase (typically two to four weeks for a mid-sized schema) that runs an automated conversion tool against the actual codebase and reports a real percentage of objects needing manual rewrite is worth more than an estimate based on database size alone. Database size is a poor predictor of migration effort; PL/SQL complexity and application coupling are the real drivers.

Written & reviewed by

Virender Kumar

Head of Cloud & Database, ROSTAN Technologies

Virender Kumar leads the cloud and database practice at ROSTAN Technologies, covering Oracle Database administration, Oracle Cloud Infrastructure (OCI) and enterprise cloud migration. More from Virender →