pg2oracle

This is an independent open-source project. It is not an Oracle product and is not affiliated with, endorsed by, or sponsored by Oracle Corporation.

Translate PostgreSQL DDL to Oracle DDL. File in, file out. Honest about what it can't do.

pg2oracle is a single-binary CLI that converts a Postgres schema dump into Oracle-compatible DDL and a Markdown compatibility report. It is intentionally boring: hardcoded type mappings, no AST round-trips, no renames, no hidden defaults. What you give it is what you get back, plus warnings.

Who this is for

• Migration leads and DBAs scoping a Postgres → Oracle move
• M&A and technical due-diligence teams sizing a target's Postgres footprint against Oracle Database, Exadata, or Autonomous Database
• Solution architects and pre-sales engineers producing first-pass landing-zone DDL
• Oracle partners and SI practices standardizing intake before SQL Developer Migration Workbench, ZDM, or GoldenGate engagements
• Platform and FinOps teams estimating license and edition impact (SE2 vs EE, options, features in use) from schema evidence

For full, app-aware migration, use Oracle's own tooling. pg2oracle is the deterministic first pass that produces the artifact everyone agrees on before that work begins.

Oracle version compatibility

Output targets Oracle 19c and 21c+ (current LTS). Specifics:

| Feature in output | 11g | 12.1 | 12.2 | 19c | 21c+ | |---|---|---|---|---|---| | GENERATED BY DEFAULT AS IDENTITY | ❌ | ✅ | ✅ | ✅ | ✅ | | Identifier length limit | 30 | 30 | 128 | 128 | 128 | | CLOB CHECK (col IS JSON) | ❌ | ✅ | ✅ | ✅ | ✅ | | Native JSON type | ❌ | ❌ | ❌ | ❌ | ✅ | | TIMESTAMP WITH TIME ZONE | ✅ | ✅ | ✅ | ✅ | ✅ | | BINARY_FLOAT / BINARY_DOUBLE | ✅ | ✅ | ✅ | ✅ | ✅ |

Targeting 11g / 12.1? Identity columns and IS JSON checks won't compile — plan to rewrite identities as sequences + BEFORE INSERT triggers by hand.

On 21c+? Consider post-processing CLOB CHECK (col IS JSON) columns to the native JSON type for better performance. That rewrite is intentionally out of scope here.

Install

npm install -g pg2oracle

Requires Node 20+.

Usage

# File in, file out
pg2oracle schema.sql -o oracle.sql

# With a compatibility report
pg2oracle schema.sql -o oracle.sql --report compat.md

# Try without installing
pg_dump --schema-only mydb | npx pg2oracle@latest > oracle.sql

# Pipe from pg_dump (after global install)
pg_dump --schema-only mydb | pg2oracle > oracle.sql

# CI mode: exit 2 on any warning
pg2oracle schema.sql --strict -o oracle.sql

Flags

| Flag | Description | |------|-------------| | -o, --output <file> | Write Oracle DDL to file (default: stdout) | | --report <file> | Write compatibility report (Markdown) | | --strict | Exit code 2 if any warnings or high-severity issues | | -h, --help | Show help | | --version | Show version |

What it translates

~30 type mappings, hardcoded in src/map.ts:

| Postgres | Oracle | Notes | |---|---|---| | smallint / int2 | NUMBER(5) | | | integer / int / int4 | NUMBER(10) | | | bigint / int8 | NUMBER(19) | | | serial | NUMBER(10) GENERATED BY DEFAULT AS IDENTITY | | | bigserial | NUMBER(19) GENERATED BY DEFAULT AS IDENTITY | | | real / float4 | BINARY_FLOAT | | | double precision / float8 | BINARY_DOUBLE | | | numeric(p,s) / decimal(p,s) | NUMBER(p,s) | | | text | CLOB | | | varchar(n) / character varying(n) | VARCHAR2(n) | unparameterized → warning | | char(n) / character(n) | CHAR(n) | | | boolean / bool | NUMBER(1) | true→1, false→0 | | timestamp | TIMESTAMP | | | timestamptz | TIMESTAMP WITH TIME ZONE | | | date | DATE | | | time | TIMESTAMP | warning — Oracle has no TIME-only type | | uuid | RAW(16) | warning — caller must convert to 16-byte binary | | bytea | BLOB | | | json / jsonb | CLOB CHECK (col IS JSON) | warning — operators don't translate |

Default expressions:

• now() → SYSTIMESTAMP
• CURRENT_TIMESTAMP → left as-is (valid on Oracle)
• true / false (on boolean columns) → 1 / 0
• gen_random_uuid() / uuid_generate_v4() → dropped with high-severity warning

Statements handled:

• CREATE TABLE — full type translation, constraint passthrough. IF NOT EXISTS is stripped (Oracle rejects it) and a warning is emitted.
• CREATE INDEX — USING btree stripped; other access methods warned and dropped
• ALTER TABLE — preserved verbatim with a high-severity warning when the statement uses Postgres-only syntax (SET DATA TYPE, USING, ALTER COLUMN). No automatic rewrite to Oracle's MODIFY (...) form — rewrite by hand.
• COMMENT ON — passed through verbatim
• CREATE EXTENSION — dropped with warning
• CREATE FUNCTION/PROCEDURE/TRIGGER — preserved verbatim with high-severity warning (PL/pgSQL is not translated)
• SET ... — silently skipped

What it does not do

This is intentional. pg2oracle will never:

• Rename your objects. Identifiers >30 chars (Oracle 11g/12.1 limit) are flagged in the report but the output is left unchanged. You decide whether to rename in Postgres or target Oracle 12.2+.
• Rewrite PL/pgSQL. Function and trigger bodies are preserved verbatim and flagged. Rewrite as PL/SQL by hand.
• Translate jsonb operators (@>, ->, ->>). The column becomes CLOB IS JSON but queries that use those operators must be rewritten.
• Invent defaults. If a default expression has no Oracle equivalent (gen_random_uuid()), it is dropped and the report flags it. Add a BEFORE INSERT trigger using SYS_GUID() if you need it.
• Make network calls during translation.

The compatibility report is the UX. Read it.

Known gaps (v0.1.x)

These are deliberately out of scope today. If your schema relies on them, plan a manual pass — pg2oracle will either pass them through verbatim with a warning or skip them entirely.

| Area | Status | What you need to do | |---|---|---| | CREATE SEQUENCE | not translated | Rewrite to Oracle CREATE SEQUENCE syntax (most syntax is portable; OWNED BY and AS <type> are not). | | nextval('seq_name') defaults | dropped with warning | Replace with seq_name.NEXTVAL or migrate the column to an identity column. | | Partitioning (PARTITION BY RANGE/LIST/HASH) | not translated | Re-author using Oracle partitioning DDL (different syntax and capabilities). | | Tablespaces & storage clauses | stripped | Add Oracle TABLESPACE / storage clauses post-translation per your standards. | | Schemas / SET search_path | skipped | Decide on Oracle schema/user mapping up front; prefix object names if needed. | | Materialized views | passed through verbatim, will likely fail | Rewrite as Oracle materialized views (refresh modes differ). | | Views with PG-specific functions | passed through verbatim | Audit and rewrite by hand. | | Foreign data wrappers, INHERITS, RULE, LISTEN/NOTIFY, generated columns with PG expressions | not handled | No equivalent or out of scope. | | PL/pgSQL function/trigger bodies | preserved verbatim with high warning | Rewrite as PL/SQL by hand. | | jsonb operators (@>, ->, ->>) in views/checks | not rewritten | Rewrite using JSON_VALUE, JSON_QUERY, JSON_EXISTS. | | Row-level security policies | not handled | Re-implement using Oracle VPD / OLS. | | Collations, extensions, CREATE TYPE (composite/enum) | dropped or warned | Manual mapping required. |

The point is the report, not magical translation. If you need any of the above translated automatically, the right tool is Oracle SQL Developer Migration Workbench, not this.

Compatibility report

Two parts: per-statement findings and an Oracle edition / option signal table.

Severities (per-statement findings):

• high — will likely break on Oracle (jsonb operators, long identifiers, dropped defaults)
• warn — lossy or behavior change (boolean → NUMBER(1), uuid → RAW(16))
• info — non-blocking notes (statements skipped)

Use --strict to fail CI on any warn or high (exit code 2).

Oracle edition & option signals

Every report ends with an "Oracle edition & option signals" table that flags schema features whose Oracle landing zone tends to involve specific Database editions or options. Examples:

| If the source uses … | The report flags … | |---|---| | PARTITION BY RANGE/LIST/HASH | Oracle Partitioning option (EE-only on-prem; included in ADB) | | jsonb columns | Native JSON type (21c+) vs JSON-on-CLOB (12c–19c) | | CREATE POLICY / ENABLE ROW LEVEL SECURITY | VPD (included in EE) / OLS (separately licensed) | | geometry / geography columns | Oracle Spatial (EE option on-prem) / Locator (all editions) | | tsvector / to_tsvector / GIN indexes | Oracle Text (all editions, different syntax) | | pgcrypto, uuid-ossp, vector extensions | DBMS_CRYPTO, SYS_GUID, AI Vector Search (23ai) | | Identifiers > 30 chars | Oracle 12.2+ extended identifier length (128 chars) | | Multiple non-public schemas | Oracle Multitenant option / schema-as-tenant tradeoff |

Wording is observational and asks the reader to verify against their Oracle contract, deployment model (on-prem / OCI / ADB), and actual usage. pg2oracle is not affiliated with Oracle and does not provide licensing advice — the section exists to make the conversation faster, not to settle it.

Examples

Three reference translations live in examples/, each with input SQL, generated Oracle DDL, and a compatibility report:

• examples/ghost/ — Ghost CMS (CMS schema, varchar-heavy)
• examples/saas/ — multi-tenant B2B SaaS (RLS, JSON, UUID, pgcrypto)
• examples/warehouse/ — Kimball star schema with partitioned fact + materialized view (Exadata / ADW shape)

Each compat.md ends with an Oracle edition & option signal table — read those first if you're scoping a target.

Develop

bun install
bun run test          # 29 unit tests
bun run build         # → dist/cli.cjs (single-file, ~134 KB)
bun run uat           # 22-criteria acceptance script
bun run typecheck

Trademarks

pg2oracle is an independent open-source project and is not affiliated with, endorsed by, or sponsored by Oracle Corporation or the PostgreSQL Global Development Group. "Oracle" is a registered trademark of Oracle Corporation. "PostgreSQL" and the Slonik logo are trademarks of the PostgreSQL Community Association of Canada. All trademarks are the property of their respective owners and are used here for descriptive purposes only.

License

MIT © Sam <[email protected]>