---
# === IDENTITY ===
id: software/migrations/oracle-to-postgresql/2026
canonical_question: "How do I migrate from Oracle to PostgreSQL?"
aliases:
  - "Oracle to PostgreSQL migration guide"
  - "migrate Oracle database to PostgreSQL"
  - "Oracle to Postgres conversion"
  - "PL/SQL to PL/pgSQL migration"
  - "ora2pg migration tool"
  - "replace Oracle with PostgreSQL"
  - "Oracle to PostgreSQL data type mapping"
  - "Oracle to Aurora PostgreSQL migration"
entity_type: software_reference
domain: software > migrations > oracle_to_postgresql
region: global
jurisdiction: global
temporal_scope: 2020-2026

# === VERIFICATION ===
last_verified: 2026-05-29
confidence: 0.92
version: 2.1
first_published: 2026-02-17

# === TEMPORAL VALIDITY ===
temporal_validity:
  status: evolving
  last_breaking_change: "Ora2Pg 25.0 (2025-02) — parallel partition export, SCRIPT action"
  next_review: 2026-11-25
  change_sensitivity: medium

# === CONSTRAINTS ===
constraints:
  - "Always map Oracle DATE to PostgreSQL timestamp — Oracle DATE includes time; PostgreSQL date does not"
  - "Never run Ora2Pg-generated SQL directly in production — always review in staging first"
  - "Oracle treats empty string ('') as NULL — audit all WHERE col IS NULL and concatenation logic before cutover"
  - "Do not use PostgreSQL ctid as a substitute for Oracle ROWID — ctid changes after VACUUM"
  - "Sequences must be manually reset after AWS DMS CDC replication — DMS does not migrate NEXTVAL state"
  - "PostgreSQL forbids COMMIT/ROLLBACK inside a PL/pgSQL function — only procedures (CALL) can control transactions; move transaction boundaries to the calling application"

# === SKIP CONDITIONS ===
skip_this_unit_if:
  - condition: "Migrating from SQL Server to PostgreSQL instead of Oracle"
    use_instead: "software/migrations/sqlserver-to-postgresql/2026"
  - condition: "Migrating from MySQL to PostgreSQL instead of Oracle"
    use_instead: "software/migrations/mysql-to-postgresql/2026"
  - condition: "Optimizing slow queries on an existing PostgreSQL database (not migrating)"
    use_instead: "software/debugging/postgresql-slow-queries/2026"

# === AGENT HINTS ===
inputs_needed:
  - key: database_size
    question: "What is the Oracle database size?"
    type: choice
    options: ["Small (<100 GB)", "Medium (100 GB - 1 TB)", "Large (>1 TB)"]
  - key: downtime_tolerance
    question: "Is near-zero downtime required during migration?"
    type: choice
    options: ["Yes — need CDC/replication", "No — maintenance window available"]
  - key: plsql_complexity
    question: "How much PL/SQL code exists in the Oracle database?"
    type: choice
    options: ["Minimal (<50 procedures)", "Moderate (50-500 procedures)", "Heavy (>500 or complex packages)"]

# === DISTRIBUTION ===
canonical_source: "https://knowledgelib.io/software/migrations/oracle-to-postgresql/2026"
suggested_citation: "Source: knowledgelib.io — AI Knowledge Library (verified 2026-05-29)"

# === RELATED UNITS ===
related_kos:
  related_to:
    - id: "software/migrations/sqlserver-to-postgresql/2026"
      label: "SQL Server to PostgreSQL Migration"
    - id: "software/migrations/mysql-to-postgresql/2026"
      label: "MySQL to PostgreSQL Migration"
    - id: "software/debugging/postgresql-slow-queries/2026"
      label: "PostgreSQL Slow Query Optimization"
  alternative_to:
    - id: "software/migrations/mongodb-to-postgresql/2026"
      label: "MongoDB to PostgreSQL Migration"
  often_confused_with:
    - id: "software/migrations/sqlserver-to-postgresql/2026"
      label: "SQL Server to PostgreSQL Migration"

# === SOURCES (9 authoritative sources) ===
sources:
  - id: src1
    title: "Ora2Pg — Oracle to PostgreSQL database schema converter"
    author: Gilles Darold
    url: https://ora2pg.darold.net/
    type: official_docs
    published: 2025-06-01
    reliability: authoritative
  - id: src2
    title: "Migrating from Oracle to Amazon Aurora PostgreSQL"
    author: Amazon Web Services
    url: https://docs.aws.amazon.com/dms/latest/sbs/chap-oracle2postgresql.html
    type: official_docs
    published: 2025-01-15
    reliability: authoritative
  - id: src3
    title: "Oracle to PostgreSQL Migration: Complete Guide"
    author: EnterpriseDB
    url: https://www.enterprisedb.com/blog/the-complete-oracle-to-postgresql-migration-guide-tutorial-move-convert-database-oracle-alternative
    type: technical_blog
    published: 2025-03-10
    reliability: high
  - id: src4
    title: "Oracle Compatibility in PostgreSQL"
    author: PostgreSQL Wiki
    url: https://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion
    type: community_resource
    published: 2025-02-01
    reliability: high
  - id: src5
    title: "AWS Schema Conversion Tool: Oracle to PostgreSQL"
    author: Amazon Web Services
    url: https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.Oracle.html
    type: official_docs
    published: 2025-04-01
    reliability: authoritative
  - id: src6
    title: "Orafce — Oracle compatibility functions for PostgreSQL"
    author: Orafce Project
    url: https://github.com/orafce/orafce
    type: community_resource
    published: 2025-08-01
    reliability: high
  - id: src7
    title: "Ora2Pg v25.0 Release — PostgreSQL News"
    author: PostgreSQL Global Development Group
    url: https://www.postgresql.org/about/news/ora2pg-v250-has-been-released-3063/
    type: official_docs
    published: 2025-02-01
    reliability: authoritative
  - id: src8
    title: "IvorySQL — Open Source Oracle Compatible PostgreSQL"
    author: IvorySQL Project
    url: https://github.com/IvorySQL/IvorySQL
    type: community_resource
    published: 2025-06-01
    reliability: high
  - id: src9
    title: "Converting database schemas using DMS Schema Conversion"
    author: Amazon Web Services
    url: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_SchemaConversion.html
    type: official_docs
    published: 2026-03-01
    reliability: authoritative
---

# How to Migrate from Oracle to PostgreSQL

## How do I migrate from Oracle to PostgreSQL?

## TL;DR

- **Bottom line**: Use Ora2Pg 25.x for schema/code conversion and PgLoader or AWS DMS for data migration — expect 70-80% automatic conversion with manual work required for PL/SQL packages, Oracle-specific functions, and shared database sequences.
- **Key tool/command**: `ora2pg -c ora2pg.conf -t SHOW_REPORT` to assess migration complexity before starting.
- **Watch out for**: Oracle's implicit `NULL = ''` (empty string) behavior — PostgreSQL treats them differently, causing subtle bugs in WHERE clauses and concatenation.
- **Works with**: Oracle 11g-23ai to PostgreSQL 14-18 (18.4 current as of 2026-05); tools: Ora2Pg 25.x, AWS DMS Schema Conversion (now fully managed, replacing standalone AWS SCT downloads), AWS DMS 3.5+, pgLoader 3.6+, IvorySQL 4.x. [src9]

## Constraints
<!-- Agents: read this section before recommending any action from this unit.
     These are hard constraints that must not be violated. -->

- Always map Oracle `DATE` to PostgreSQL `timestamp` — Oracle `DATE` includes time; PostgreSQL `date` does not. Using `date` silently drops time data. [src4, src7]
- Never run Ora2Pg-generated SQL directly in production — always review generated DDL for TODO/FIXME comments and test in a staging database first. [src1]
- Oracle treats empty string (`''`) as `NULL` — audit all `WHERE col IS NULL` and string concatenation logic before cutover; this is the #1 source of post-migration bugs. [src4]
- Do not use PostgreSQL `ctid` as a substitute for Oracle `ROWID` — `ctid` changes after VACUUM; add a real primary key column instead. [src4]
- Sequences must be manually reset after AWS DMS CDC replication — DMS does not migrate NEXTVAL state during ongoing replication. [src2, src5]
- When using `TrimSpaceInChar` endpoint setting in AWS DMS, verify CHAR/NCHAR column data is not silently truncated — this setting trims trailing spaces which may be semantically significant. [src2]
- PostgreSQL forbids `COMMIT`/`ROLLBACK` inside a PL/pgSQL **function** — only procedures invoked via `CALL` can control transactions, and even then not inside an outer transaction block. Oracle code that commits mid-function must move its transaction boundaries to the calling application layer. [src9]

## Quick Reference

| Oracle Feature | PostgreSQL Equivalent | Migration Notes |
|---|---|---|
| `VARCHAR2(N)` | `varchar(N)` or `text` | PostgreSQL `text` has no performance penalty vs `varchar` [src4] |
| `NUMBER` | `numeric`, `bigint`, `integer` | Map by precision: `NUMBER` -> `numeric`, `NUMBER(10)` -> `bigint`, `NUMBER(5)` -> `integer` [src4] |
| `NUMBER(p,s)` | `numeric(p,s)` | Direct mapping; use `real`/`double precision` for approximate math [src4] |
| `DATE` | `timestamp` | Oracle `DATE` includes time component; PostgreSQL `date` does not [src4] |
| `CLOB` / `NCLOB` | `text` | PostgreSQL `text` can hold up to 1 GB; no special handling needed [src4] |
| `BLOB` / `RAW` | `bytea` | Or use Large Objects (`lo`) for files > 1 GB [src3] |
| `SYSDATE` | `CURRENT_TIMESTAMP` or `clock_timestamp()` | `CURRENT_TIMESTAMP` returns transaction start time; `clock_timestamp()` returns actual current time [src4] |
| `NVL(a, b)` | `COALESCE(a, b)` | `COALESCE` is SQL standard and supports multiple arguments [src4] |
| `DECODE(x,a,b,c,d,e)` | `CASE WHEN x=a THEN b WHEN x=c THEN d ELSE e END` | Ora2Pg converts this automatically [src1] |
| `ROWNUM` | `LIMIT` / `ROW_NUMBER()` | `ROWNUM` in WHERE -> `LIMIT N`; `ROWNUM` in subquery -> `ROW_NUMBER() OVER(...)` [src4] |
| `CONNECT BY` / `START WITH` | `WITH RECURSIVE` CTE | Recursive CTEs are more flexible but syntax differs significantly [src4] |
| `SEQUENCES` (`.NEXTVAL` / `.CURRVAL`) | `nextval('seq_name')` / `currval('seq_name')` | Or use `GENERATED ALWAYS AS IDENTITY` for auto-increment columns [src3] |
| `PACKAGES` | Schemas + functions | No direct equivalent; split package into schema with individual functions [src1, src3] |
| `SYNONYMS` | `SET search_path` or views | Use `search_path` for schema-level synonyms, views for table-level [src4] |
| `DBMS_OUTPUT.PUT_LINE` | `RAISE NOTICE` | Direct replacement in PL/pgSQL [src4] |
| `DUAL` table | Omit `FROM DUAL` | PostgreSQL allows `SELECT 1;` without `FROM` clause [src4] |
| `(+)` outer join syntax | `LEFT JOIN` / `RIGHT JOIN` | ANSI join syntax is required in PostgreSQL [src4] |
| `TIMESTAMP WITH TIME ZONE` | `timestamptz` | Semantics differ: PostgreSQL `timestamptz` stores UTC and maps to Oracle's `TIMESTAMP WITH LOCAL TIME ZONE`, not Oracle's `WITH TIME ZONE` — test conversions for off-by-offset bugs [src9] |
| `NUMBER` foreign keys | `bigint` / `integer` | `numeric` joins/indexes are measurably slower than integer types — prefer `bigint` for surrogate keys and FKs [src9] |

## Decision Tree

```
START
├── What is the database size?
│   ├── < 100 GB → Ora2Pg export + psql import (simplest approach) [src1]
│   └── > 100 GB ↓
├── Is near-zero downtime required?
│   ├── YES → AWS DMS or logical replication for CDC during cutover [src2, src5]
│   └── NO → Ora2Pg bulk export + PgLoader parallel import [src1]
├── How much PL/SQL code exists?
│   ├── Minimal (< 50 procedures) → Ora2Pg auto-converts most code [src1]
│   ├── Moderate (50-500) → Ora2Pg + manual review of complex packages [src1, src3]
│   └── Heavy (> 500 or complex packages) → Consider EDB Advanced Server or IvorySQL for Oracle compatibility layer [src3, src8]
├── Are you migrating to AWS?
│   ├── YES → Use AWS SCT + DMS pipeline [src2, src5]
│   └── NO ↓
├── Do you need ongoing Oracle compatibility?
│   ├── YES → Install Orafce extension or use IvorySQL [src6, src8]
│   └── NO → Convert all code to native PostgreSQL (recommended long-term) [src3]
└── DEFAULT → Start with Ora2Pg assessment report, plan phase by phase
```

## Decision Logic

Structured if/then rules an agent can apply directly once it has the user's `inputs_needed` answers.

### If the database is small (<100 GB) and a maintenance window is available
--> Use Ora2Pg for both schema and data: `ora2pg -t EXPORT_SCHEMA` then `ora2pg -t COPY -j 8`, import with `psql`. Simplest, fewest moving parts. [src1]

### If near-zero downtime is required (>100 GB or 24/7 system)
--> Run Ora2Pg (or DMS Schema Conversion) for the schema, then AWS DMS full-load + CDC — or open-source CDC such as Debezium reading Oracle redo logs — so cutover takes only seconds. Reset all sequences immediately after cutover. [src2, src9]

### If migrating into AWS and you want a managed, console-driven path
--> Use **AWS DMS Schema Conversion** (now fully managed, with GenAI-assisted conversion since March 2026) instead of downloading the standalone AWS SCT client, then DMS for data. [src5, src9]

### If the codebase has heavy or complex PL/SQL (>500 procedures or stateful packages)
--> Either budget for manual PL/pgSQL refactoring or adopt an Oracle-compatible engine — IvorySQL (open source) or EDB Advanced Server (commercial) — to avoid rewriting business logic. [src3, src8]

### If existing code commits or rolls back inside Oracle functions
--> Refactor: PostgreSQL forbids transaction control inside PL/pgSQL functions. Convert to a `PROCEDURE` (callable via `CALL`) or move the transaction boundary to the application layer. [src9]

### If the application relies on Oracle's empty-string-equals-NULL behavior
--> Do not skip data normalization: run `UPDATE t SET col = NULL WHERE col = ''` during cutover and audit every `WHERE col IS NULL` and string-concatenation path before go-live. [src4]

### If you need ongoing Oracle SQL/function compatibility after the move
--> Install the `orafce` extension for the 100+ Oracle built-ins, or run IvorySQL in Oracle-compatible mode — but do not mix Oracle and PostgreSQL modes in the same IvorySQL database. [src6, src8]

## Step-by-Step Guide

### 1. Run the Ora2Pg migration assessment

Install Ora2Pg and run a migration complexity report. This tells you exactly how much manual work is needed before touching any code. Ora2Pg 25.x adds parallel partition export, SCRIPT action for sqlplus scripts, and multiple assessment report formats. [src1, src7]

```bash
# Install Ora2Pg (Debian/Ubuntu)
sudo apt-get install ora2pg

# Or from source (for Ora2Pg 25.x)
git clone https://github.com/darold/ora2pg.git
cd ora2pg && perl Makefile.PL && make && sudo make install

# Create config file
ora2pg --init_project myproject
cd myproject

# Edit ora2pg.conf — set Oracle connection
# ORACLE_DSN    dbi:Oracle:host=orahost;sid=ORCL;port=1521
# ORACLE_USER   migration_user
# ORACLE_PWD    secret

# Run assessment report (Ora2Pg 25.x supports multiple output formats at once)
ora2pg -c ora2pg.conf -t SHOW_REPORT
# Or with HTML + JSON output:
ora2pg -c ora2pg.conf -t SHOW_REPORT --dump_as_html --dump_as_json
```

**Verify**: Report shows migration levels A (trivial) through E (very complex) per object type. Total migration cost estimate in person-days.

### 2. Export and convert the schema

Use Ora2Pg to export Oracle schema objects and auto-convert to PostgreSQL DDL. Review and fix any objects flagged as unconvertible. [src1, src3]

```bash
# Export all schema objects
ora2pg -c ora2pg.conf -t TABLE -o tables.sql
ora2pg -c ora2pg.conf -t SEQUENCE -o sequences.sql
ora2pg -c ora2pg.conf -t VIEW -o views.sql
ora2pg -c ora2pg.conf -t FUNCTION -o functions.sql
ora2pg -c ora2pg.conf -t PROCEDURE -o procedures.sql
ora2pg -c ora2pg.conf -t TRIGGER -o triggers.sql
ora2pg -c ora2pg.conf -t PACKAGE -o packages.sql
ora2pg -c ora2pg.conf -t TYPE -o types.sql

# Or export everything at once
ora2pg -c ora2pg.conf -t EXPORT_SCHEMA --no_header -o schema.sql

# Override config on the fly (Ora2Pg 25.x -O flag)
ora2pg -c ora2pg.conf -t TABLE -O "CASE_SENSITIVE=1" -o tables.sql

# Review the generated SQL for conversion warnings
grep -n "TODO" schema.sql
grep -n "FIXME" schema.sql
```

**Verify**: Load schema into a test PostgreSQL instance: `psql -f schema.sql testdb` -> zero errors.

### 3. Convert PL/SQL to PL/pgSQL

Ora2Pg handles most conversions automatically, but review these common manual fixes. Ora2Pg 25.x adds improved Oracle-to-PostgreSQL exception mapping. [src1, src4, src7]

```sql
-- Oracle PL/SQL (BEFORE)
CREATE OR REPLACE PROCEDURE update_salary(
  p_emp_id IN NUMBER,
  p_raise IN NUMBER
) IS
  v_current_sal NUMBER;
BEGIN
  SELECT salary INTO v_current_sal FROM employees WHERE emp_id = p_emp_id;
  IF v_current_sal IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found');
    RETURN;
  END IF;
  UPDATE employees SET salary = salary + p_raise WHERE emp_id = p_emp_id;
  COMMIT;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee ' || p_emp_id || ' not found');
END;
/

-- PostgreSQL PL/pgSQL (AFTER)
CREATE OR REPLACE PROCEDURE update_salary(
  p_emp_id bigint,
  p_raise numeric
)
LANGUAGE plpgsql
AS $$
DECLARE
  v_current_sal numeric;
BEGIN
  SELECT salary INTO v_current_sal FROM employees WHERE emp_id = p_emp_id;
  IF NOT FOUND THEN
    RAISE NOTICE 'Employee % not found', p_emp_id;
    RETURN;
  END IF;
  UPDATE employees SET salary = salary + p_raise WHERE emp_id = p_emp_id;
  -- No explicit COMMIT in procedures (auto-commit outside transaction)
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE NOTICE 'Employee % not found', p_emp_id;
END;
$$;
```

**Verify**: `\df update_salary` in psql shows the function. Call it: `CALL update_salary(101, 5000);` -> no errors.

### 4. Migrate the data

Choose the migration method based on database size and downtime tolerance. Ora2Pg 25.x adds parallel partition export for faster data extraction from partitioned tables. [src1, src2, src7]

```bash
# METHOD 1: Ora2Pg direct data export (small-medium databases < 100 GB)
ora2pg -c ora2pg.conf -t COPY -o data.sql
# Or for parallel export (faster):
ora2pg -c ora2pg.conf -t COPY -j 8 -o data.sql

# Import into PostgreSQL
psql -d targetdb -f data.sql

# METHOD 2: PgLoader (fast parallel load with type casting)
pgloader oracle://user:pass@orahost/ORCL \
         postgresql://user:pass@pghost/targetdb

# METHOD 3: AWS DMS (continuous replication for near-zero downtime)
# Configure via AWS Console:
# Source endpoint: Oracle (DBI:Oracle:host=...;sid=...)
# Target endpoint: PostgreSQL (RDS or Aurora)
# Replication task: Full load + CDC (Change Data Capture)
# Note: Set TrimSpaceInChar=true if CHAR columns have trailing spaces
# In 2026, schema conversion is bundled: use DMS Schema Conversion (managed,
# GenAI-assisted) inside the DMS console instead of the standalone SCT client.

# METHOD 4: Open-source CDC (Debezium / BryteFlow) reading Oracle redo logs
# Streams live changes into PostgreSQL for vendor-neutral zero-downtime cutover
```

**Verify**: Compare row counts: `SELECT table_name, num_rows FROM all_tables WHERE owner='SCHEMA'` (Oracle) vs `SELECT relname, n_live_tup FROM pg_stat_user_tables` (PostgreSQL).

### 5. Handle Oracle-specific NULL and empty string behavior

Oracle treats empty string (`''`) as `NULL`. PostgreSQL does not. This causes subtle bugs in migrated code. [src4]

```sql
-- Oracle: these are equivalent
SELECT * FROM users WHERE name IS NULL;     -- finds empty strings TOO
SELECT * FROM users WHERE name = '';        -- matches NULL in Oracle

-- PostgreSQL: these are different!
SELECT * FROM users WHERE name IS NULL;     -- only finds actual NULLs
SELECT * FROM users WHERE name = '';        -- only finds empty strings

-- Fix: Update application code and queries to handle both
SELECT * FROM users WHERE name IS NULL OR name = '';
-- Or normalize data during migration:
UPDATE users SET name = NULL WHERE name = '';
```

**Verify**: `SELECT COUNT(*) FROM users WHERE name = ''` should return 0 after normalization.

### 6. Rebuild indexes and optimize performance

Oracle and PostgreSQL use different index types and optimizer strategies. Recreate indexes optimized for PostgreSQL. [src3]

```sql
-- Oracle bitmap indexes -> PostgreSQL GIN or partial indexes
-- Oracle: CREATE BITMAP INDEX idx_status ON orders(status);
-- PostgreSQL equivalent:
CREATE INDEX idx_status ON orders USING btree(status);
-- For low-cardinality columns, partial indexes work better:
CREATE INDEX idx_active_orders ON orders(created_at) WHERE status = 'active';

-- Oracle function-based indexes -> PostgreSQL expression indexes
-- Oracle: CREATE INDEX idx_upper_name ON users(UPPER(name));
-- PostgreSQL:
CREATE INDEX idx_upper_name ON users(UPPER(name));  -- same syntax works!

-- Analyze all tables after data load
ANALYZE;

-- Check for missing indexes
SELECT schemaname, relname, seq_scan, idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > 1000 AND idx_scan < 50
ORDER BY seq_scan DESC;
```

**Verify**: `EXPLAIN ANALYZE SELECT ...` on critical queries shows index scans, not sequential scans.

## Code Examples

### Python: Automated Oracle to PostgreSQL data migration with validation

> Full script: [python-automated-oracle-to-postgresql-data-migrati.py](scripts/python-automated-oracle-to-postgresql-data-migrati.py) (63 lines)

```python
# Input:  Oracle connection + PostgreSQL connection + list of tables
# Output: Migrated data with row count validation per table
import oracledb  # cx_Oracle is now oracledb (v2.0+)
import psycopg  # psycopg 3.x (async-capable)
from typing import Dict, Tuple
# ... (see full script)
```

### Bash: Complete Ora2Pg migration script with validation

> Full script: [bash-complete-ora2pg-migration-script-with-validat.sh](scripts/bash-complete-ora2pg-migration-script-with-validat.sh) (37 lines)

```bash
#!/bin/bash
# Input:  ora2pg.conf configured with Oracle and PostgreSQL connections
# Output: Fully migrated PostgreSQL database with validation report
set -euo pipefail
CONFIG="ora2pg.conf"
# ... (see full script)
```

## Anti-Patterns

### Wrong: Treating empty strings and NULLs as interchangeable

```sql
-- ❌ BAD — Oracle code that relies on '' = NULL
-- Oracle implicitly converts empty strings to NULL
INSERT INTO users (name) VALUES ('');
SELECT * FROM users WHERE name IS NULL;  -- Returns the row in Oracle!
-- In PostgreSQL, this query will NOT find the row with ''
```

### Correct: Explicitly handle NULL vs empty string

```sql
-- ✅ GOOD — Code that works in both databases or is PostgreSQL-native
-- During migration, normalize empty strings to NULL:
UPDATE users SET name = NULL WHERE name = '';
-- Or use COALESCE/NULLIF in queries:
SELECT * FROM users WHERE NULLIF(name, '') IS NULL;
```

### Wrong: Converting Oracle PACKAGES as single functions

```sql
-- ❌ BAD — Cramming an entire Oracle package into one giant function
-- Oracle packages contain state (variables), multiple procedures, and types
-- Putting it all in one PL/pgSQL function creates unmaintainable code
CREATE OR REPLACE FUNCTION pkg_orders_everything(action text, ...) ...
```

### Correct: Split packages into schema + individual functions

```sql
-- ✅ GOOD — Use a PostgreSQL schema to group related functions [src1, src3]
CREATE SCHEMA pkg_orders;

CREATE OR REPLACE FUNCTION pkg_orders.create_order(p_customer_id bigint, ...)
  RETURNS bigint LANGUAGE plpgsql AS $$ ... $$;

CREATE OR REPLACE FUNCTION pkg_orders.cancel_order(p_order_id bigint)
  RETURNS void LANGUAGE plpgsql AS $$ ... $$;

-- Package variables -> table or session variables
CREATE TABLE pkg_orders.config (key text PRIMARY KEY, value text);
-- Or use: SET LOCAL myapp.setting = 'value';
```

### Wrong: Using Ora2Pg without reviewing output

```bash
# ❌ BAD — Blindly running generated SQL without review
ora2pg -c ora2pg.conf -t EXPORT_SCHEMA -o schema.sql
psql -d production -f schema.sql  # DANGEROUS: unreviewed in production
```

### Correct: Review, test, iterate

```bash
# ✅ GOOD — Review generated SQL, test in staging, iterate [src1]
ora2pg -c ora2pg.conf -t EXPORT_SCHEMA -o schema.sql
# 1. Review for TODO/FIXME comments
grep -n "TODO\|FIXME\|WARNING" schema.sql
# 2. Load in test database
psql -d test_migration -f schema.sql
# 3. Run application test suite against PostgreSQL
# 4. Fix issues, re-export, repeat
```

### Wrong: Forgetting to reset sequences after DMS replication

```sql
-- ❌ BAD — Assuming AWS DMS migrates sequence state during CDC
-- After cutover, new INSERTs fail with duplicate key errors
-- because NEXTVAL is still at 1 on the target
INSERT INTO orders (id, ...) VALUES (DEFAULT, ...);
-- ERROR: duplicate key value violates unique constraint
```

### Correct: Manually reset sequences after DMS cutover

```sql
-- ✅ GOOD — Reset all sequences to MAX(id)+1 after CDC cutover [src2]
DO $$
DECLARE
  r RECORD;
BEGIN
  FOR r IN SELECT sequencename FROM pg_sequences WHERE schemaname = 'public'
  LOOP
    EXECUTE format(
      'SELECT setval(%L, COALESCE((SELECT MAX(id) FROM %I), 1))',
      r.sequencename,
      replace(r.sequencename, '_id_seq', '')
    );
  END LOOP;
END $$;
```

## Common Pitfalls

- **Oracle `(+)` outer join syntax not supported**: PostgreSQL only supports ANSI SQL join syntax (`LEFT JOIN`, `RIGHT JOIN`). Ora2Pg converts most cases, but review complex multi-table joins with `(+)` in subqueries. Fix: rewrite using explicit `LEFT JOIN ... ON` syntax. [src4]
- **CONNECT BY hierarchical queries**: Oracle's `CONNECT BY PRIOR` + `START WITH` has no direct equivalent. Fix: rewrite using `WITH RECURSIVE` CTEs, which are more verbose but equally powerful. [src4]
- **Transaction behavior differences**: Oracle auto-commits DDL statements; PostgreSQL wraps DDL in transactions (which can be rolled back). Applications that rely on DDL auto-commit need adjustment. Fix: add explicit `COMMIT` after DDL in migration scripts, or use PostgreSQL's transactional DDL as an advantage. [src3]
- **Case sensitivity in identifiers**: Oracle folds unquoted identifiers to uppercase; PostgreSQL folds to lowercase. Quoted identifiers in Oracle (`"MyTable"`) become case-sensitive in PostgreSQL. Fix: use lowercase unquoted names consistently, or use Ora2Pg's `CASE_SENSITIVE` option. [src4]
- **`ROWID` pseudo-column**: Oracle's `ROWID` is used for fast row access. PostgreSQL has `ctid` but it changes after VACUUM. Fix: add a real primary key column if the table relies on `ROWID` for updates. [src4]
- **Global temporary tables**: Oracle's global temporary tables persist across sessions. Fix: use PostgreSQL `CREATE TEMPORARY TABLE` (session-scoped) or `UNLOGGED TABLE` for performance-sensitive temp data. [src3]
- **Materialized view refresh**: Oracle `DBMS_MVIEW.REFRESH` -> PostgreSQL `REFRESH MATERIALIZED VIEW CONCURRENTLY`. Fix: create a cron job or pg_cron extension for scheduled refreshes. [src4]
- **NULL character in VARCHAR columns**: AWS DMS converts Oracle NULL character (0x00) to a space when targeting PostgreSQL. Fix: verify data integrity after migration for columns that may contain embedded NULL bytes. [src2]
- **Transaction control inside functions**: Oracle PL/SQL routinely `COMMIT`s mid-function; PostgreSQL throws an error if a PL/pgSQL *function* tries to `COMMIT`/`ROLLBACK`. Fix: convert such routines to a `PROCEDURE` (callable via `CALL`) or hoist the transaction boundary into the calling application. [src9]
- **`numeric` foreign keys slow joins**: Ora2Pg maps unsized Oracle `NUMBER` to `numeric`, which is slower in joins and indexes than integer types. Fix: explicitly map surrogate keys and FKs to `bigint`/`integer`. [src9]

## Diagnostic Commands

```bash
# Assess migration complexity with Ora2Pg
ora2pg -c ora2pg.conf -t SHOW_REPORT

# Count objects by type in Oracle
sqlplus -s user/pass@ORCL <<< "SELECT object_type, COUNT(*) FROM user_objects GROUP BY object_type ORDER BY 2 DESC;"

# Compare row counts between Oracle and PostgreSQL
# Oracle:
sqlplus -s user/pass@ORCL <<< "SELECT table_name, num_rows FROM user_tables ORDER BY num_rows DESC;"
# PostgreSQL:
psql -d targetdb -c "SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC;"

# Find unconverted Oracle-isms in migrated SQL
grep -rn "SYSDATE\|NVL\|DECODE\|ROWNUM\|CONNECT BY\|DBMS_" --include='*.sql' migrated/

# Check for missing indexes after migration
psql -d targetdb -c "SELECT schemaname, relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE seq_scan > 100 AND (idx_scan = 0 OR idx_scan IS NULL) ORDER BY seq_scan DESC LIMIT 20;"

# Verify extension availability
psql -d targetdb -c "SELECT * FROM pg_available_extensions WHERE name IN ('orafce', 'oracle_fdw', 'pgcrypto');"

# Process sqlplus scripts as a whole (Ora2Pg 25.x SCRIPT action)
ora2pg -c ora2pg.conf -t SCRIPT -i input_script.sql -o converted_script.sql
```

## Version History & Compatibility

| Tool/Version | Status | Key Features | Notes |
|---|---|---|---|
| Ora2Pg 25.x | Current (2025) | Parallel partition export, SCRIPT action, multiple assessment formats, enhanced exception mapping | Recommended for new migrations [src1, src7] |
| Ora2Pg 24.x | Stable | Full PL/SQL conversion, parallel export, cost estimation | Proven in production [src1] |
| AWS DMS Schema Conversion | Current (2026) | Fully managed, in-console, GenAI-assisted conversion (9 added regions Mar 2026) | Preferred over standalone SCT for new AWS migrations [src9] |
| AWS SCT 1.x (standalone client) | Legacy | GUI-based, extension packs, embedded SQL conversion | Superseded by managed DMS Schema Conversion [src5, src9] |
| AWS DMS 3.5+ | Current | CDC, full load + ongoing replication, TrimSpaceInChar setting | Use with DMS Schema Conversion for schema [src2] |
| PgLoader 3.6+ | Current | Fast parallel data loading with type casting | Data-only, no schema conversion |
| Orafce 4.x | Current | 100+ Oracle compatibility functions | Install with `CREATE EXTENSION orafce` [src6] |
| IvorySQL 4.x | Current (2025) | Full Oracle PL/SQL compatibility, compatible_db toggle, PL/iSQL | Open-source Oracle-compatible PostgreSQL [src8] |
| EDB Advanced Server 17 | Current | Full Oracle PL/SQL compatibility layer, up to 80% code reduction | Commercial; best for heavy PL/SQL codebases [src3] |

## When to Use / When Not to Use

| Use When | Don't Use When | Use Instead |
|---|---|---|
| Oracle licensing costs are unsustainable | Application uses Oracle RAC extensively | Consider cloud-managed Oracle (OCI) |
| Moving to cloud-native architecture | Heavy dependency on Oracle Spatial 3D features | Keep Oracle or use PostGIS for 2D |
| Team has PostgreSQL expertise | Migration timeline is < 3 months for large DB | Plan longer timeline or use EDB |
| Database is < 1 TB with moderate PL/SQL | 100% Oracle Forms/APEX application | Rewrite frontend first |
| Compliance requires open-source stack | Oracle Advanced Security (TDE, VPD) is critical | Evaluate PostgreSQL pgcrypto + RLS first |
| Need Oracle compatibility in open-source | Complex PL/SQL but no budget for EDB | Use IvorySQL (free Oracle-compat PG) [src8] |

## Important Caveats

- Oracle `DATE` includes a time component (datetime); PostgreSQL `date` is date-only. Always map Oracle `DATE` to PostgreSQL `timestamp` to avoid losing time data.
- Oracle treats empty strings as NULL silently — this is one of the most common sources of post-migration bugs. Audit all `WHERE col IS NULL` and concatenation operations.
- Oracle's optimizer (CBO) and PostgreSQL's query planner behave differently. Queries that performed well on Oracle may need index strategy changes and `ANALYZE` runs on PostgreSQL.
- Oracle's `CONNECT BY LEVEL` trick for generating series -> use `generate_series()` in PostgreSQL.
- PL/SQL exception handling maps mostly to PL/pgSQL, but `WHEN OTHERS THEN` catch-all blocks should be reviewed — PostgreSQL's error codes differ from Oracle's. Ora2Pg 25.x has improved exception mapping but manual review is still required for complex handlers. [src7]
- AWS DMS converts Oracle NULL characters (0x00) to spaces when PostgreSQL is the target — verify columns that may contain embedded control characters. [src2]
- IvorySQL's `compatible_db` toggle allows running in Oracle mode, but mixing Oracle and PostgreSQL modes in the same database is not supported. [src8]
- PostgreSQL 18 is the current major (18.4 released 2026-05-14); 14-18 are all supported targets. Validate Ora2Pg's `PG_VERSION`/`PG_SUPPORTS_*` settings against your exact target so generated DDL uses features available in that release. [src7, src9]
- As of 2026, AWS recommends managed **DMS Schema Conversion** (with optional GenAI assistance) over the downloadable AWS SCT client for AWS-target migrations — the standalone SCT still works but is no longer the primary path. [src9]
- Transaction control differs fundamentally: PostgreSQL cannot `COMMIT`/`ROLLBACK` inside a PL/pgSQL function, and even procedures cannot manage transactions when called inside an outer transaction block. Audit every Oracle routine that commits mid-body. [src9]

## Related Units

- [SQL Server to PostgreSQL Migration](/software/migrations/sqlserver-to-postgresql/2026)
- [MySQL to PostgreSQL Migration](/software/migrations/mysql-to-postgresql/2026)
- [MongoDB to PostgreSQL Migration](/software/migrations/mongodb-to-postgresql/2026)
- [PostgreSQL Slow Query Optimization](/software/debugging/postgresql-slow-queries/2026)