| Packages and package variables | A schema per package with functions and procedures; session state held in custom settings (set_config / current_setting) or temporary tables | No packages in PostgreSQL; initialization blocks and state need redesign. Usually the highest-effort item. |
| Autonomous transactions | No native equivalent. Loopback through the dblink or pg_background extensions, or redesign the logic | Common in audit and error logging that must survive a rollback. |
| CONNECT BY hierarchical queries | WITH RECURSIVE common table expressions; SEARCH and CYCLE clauses in PostgreSQL 14 and later | LEVEL, SYS_CONNECT_BY_PATH, ordering and cycle detection need rewriting. |
| Sequences | nextval('seq') instead of seq.NEXTVAL, or identity columns | Start values must be reset after the data load, and cache settings reviewed. |
| Data types | NUMBER to integer, bigint or numeric; VARCHAR2 to varchar or text; CLOB to text; BLOB and RAW to bytea | Mapping every unconstrained NUMBER to numeric is safe but slow for keys and joins. |
| NULL versus empty string | PostgreSQL treats '' as a real value, not NULL | Oracle treats an empty string as NULL. IS NULL checks, string concatenation with NULLs (use concat() or coalesce) and unique constraints behave differently. |
| DATE semantics | Oracle DATE includes time to the second, so it maps to timestamp(0), not date | SYSDATE, date arithmetic such as date + 1, TRUNC and TO_DATE formats all need checking. now() returns the transaction start time. |
| Oracle-only syntax | ANSI joins for (+), CASE for DECODE, coalesce for NVL, LIMIT or row_number() for ROWNUM; drop DUAL and optimizer hints | Mostly automated, except SQL embedded in application code. |
| Transactions and errors | An error aborts the PostgreSQL transaction until rollback; exception blocks use subtransactions; COMMIT works in procedures (PostgreSQL 11+), not functions | Batch code that commits in loops needs restructuring. |
| Identifiers, synonyms, DB links, temp tables | Lower-case identifiers; search_path or views for synonyms; postgres_fdw or oracle_fdw for links; session temporary tables | Upper- versus lower-case folding breaks quoted names in application SQL. |
| RAC and Data Guard | Streaming replication with Patroni, or the high availability of a managed cloud service | An architecture decision; agree recovery targets first. |