Oracle to Postgres Migration: CO Schema Issues and Fixes

Oracle's CO (Customer Orders) schema is the modern replacement for the older OE schema.

It ships with Oracle 19c, it is actively maintained, and it is built the way most real Oracle applications are built today: IDENTITY columns instead of sequence-trigger pairs, JSON stored in BLOB columns, and views that use Oracle-specific SQL functions.

I ran the full migration using ora2pg 25.0 with Oracle 19c as the source and PostgreSQL 18 as the target.

This post covers the five problems that required manual intervention — and why each one will appear in almost every production schema you migrate.

This is the third post in the series.

The HR schema post covered sequence-trigger patterns, %TYPE parameters, and the FK re-apply bug.

The SH schema post covered partitioned tables, bitmap indexes, and materialized views.

CO introduces three new problem categories not seen in either of those.

What CO Contains

The CO schema is Oracle's retail order management reference schema: customers, stores, products, orders, shipments, order items, and inventory.

The absence of stored procedures and triggers keeps the PL/SQL conversion effort at zero. The complexity comes from three places: how Oracle handles IDENTITY columns, a BLOB column that stores JSON, and four views — three of which use Oracle-specific SQL functions that PostgreSQL does not support.

ora2pg rates the CO schema A-3: straightforward migration, estimated three person-hours. The cost drivers are the three non-portable views.

Problem 1: IDENTITY Columns Have No Explicit Sequences

CO uses GENERATED BY DEFAULT ON NULL AS IDENTITY on every primary key column. Oracle manages the underlying sequences internally — there are no CREATE SEQUENCE statements in the schema, and no sequence-trigger pairs like the HR schema used.

ora2pg converts the Oracle syntax correctly: GENERATED BY DEFAULT ON NULL AS IDENTITY becomes GENERATED BY DEFAULT AS IDENTITY in PostgreSQL. That part requires no manual work.

The problem comes after the data load.

When ora2pg exports data via COPY, it exports the actual row IDs from Oracle — 1, 2, 3, … up to whatever the highest ID is. PostgreSQL's COPY protocol accepts those values and inserts them directly into the IDENTITY columns. The IDENTITY sequence is not called during a COPY load. After the load completes, each IDENTITY sequence still sits at its initial value of 1.

The first INSERT into customers after migration will attempt to use customer_id = 1. That row already exists. The insert fails with duplicate key value violates unique constraint.

The fix is to advance each IDENTITY sequence past the highest loaded value:

SET search_path = co, public;
SELECT setval(pg_get_serial_sequence('co.customers',  'customer_id'),  (SELECT MAX(customer_id)  FROM co.customers));
SELECT setval(pg_get_serial_sequence('co.orders',     'order_id'),     (SELECT MAX(order_id)     FROM co.orders));
SELECT setval(pg_get_serial_sequence('co.shipments',  'shipment_id'),  (SELECT MAX(shipment_id)  FROM co.shipments));
SELECT setval(pg_get_serial_sequence('co.stores',     'store_id'),     (SELECT MAX(store_id)     FROM co.stores));
SELECT setval(pg_get_serial_sequence('co.products',   'product_id'),   (SELECT MAX(product_id)   FROM co.products));

pg_get_serial_sequence resolves the sequence name from the table and column names. This avoids hardcoding sequence names, which vary between ora2pg versions.

Run this block immediately after the data load, before any application touches the migrated database. On a production schema, missed sequences cause INSERT failures that are easy to miss in testing if the test data set uses different IDs than production.

Problem 2: BLOB Storing JSON Must Become JSONB, Not BYTEA

products.product_details is declared as BLOB in Oracle with a CHECK (product_details IS JSON) constraint.

Oracle databases before 21c store JSON as BLOB or CLOB — there is no native JSON type.

ora2pg maps all BLOB columns to BYTEA by default.

For product_image and stores.logo, that is correct — they store binary image data.

For product_details, it is wrong.

BYTEA in PostgreSQL stores raw bytes. There are no JSON operators, no JSON functions, no validation on insert. The product_reviews view queries product_details as JSON using jsonb_array_elements after the rewrite (see Problem 5 below). That function requires a JSONB argument — it will fail with function jsonb_array_elements(bytea) does not exist if the column is BYTEA.

The fix is a MODIFY_TYPE override before running the TABLE export:

MODIFY_TYPE   PRODUCTS:PRODUCT_DETAILS:jsonb

This is part of a longer MODIFY_TYPE line — see Step 6 below for the complete directive.

JSONB enforces valid JSON on every insert automatically. The CHECK (product_details IS JSON) constraint is therefore redundant after the column type change. It also uses Oracle syntax — PostgreSQL has no IS JSON predicate — so it must be removed from the exported DDL before loading. See Problem 4.

Problem 3: FK Columns Are NUMBER(38), PKs Are bigint — Type Mismatch

FORCE_IDENTITY_BIGINT 1 causes ora2pg to export IDENTITY primary key columns as bigint. PG_INTEGER_TYPE 1 converts bare NUMBER (no precision) columns to bigint.

The FK columns in CO — orders.customer_id, orders.store_id, order_items.order_id, and others — are declared as NUMBER(38) in Oracle. That is Oracle's internal representation of an integer column declared without precision. Because the precision is explicitly 38, PG_INTEGER_TYPE 1 does not apply. ora2pg maps them to numeric(38).

The result: primary key columns are bigint, foreign key columns referencing them are numeric(38). PostgreSQL requires that FK and PK columns have the same type. Loading the DDL succeeds, but creating the FK constraints fails:

ERROR:  foreign key constraint "orders_customer_id_fk" cannot be implemented
DETAIL:  Key columns "customer_id" and "customer_id" are of incompatible types: numeric and bigint.

The fix is to add all FK columns and other integer columns to MODIFY_TYPE:

MODIFY_TYPE   INVENTORY:STORE_ID:bigint,INVENTORY:PRODUCT_ID:bigint,INVENTORY:PRODUCT_INVENTORY:integer,ORDERS:CUSTOMER_ID:bigint,ORDERS:STORE_ID:bigint,ORDER_ITEMS:ORDER_ID:bigint,ORDER_ITEMS:LINE_ITEM_ID:integer,ORDER_ITEMS:PRODUCT_ID:bigint,ORDER_ITEMS:QUANTITY:integer,ORDER_ITEMS:SHIPMENT_ID:bigint,SHIPMENTS:STORE_ID:bigint,SHIPMENTS:CUSTOMER_ID:bigint

MODIFY_TYPE reads only the first line of the directive. All overrides — including the JSONB override from Problem 2 and the numeric overrides from below — must be on a single comma-separated line.

Also: do not include type precision inside MODIFY_TYPE (write numeric, not numeric(10,2)).

The comma inside parentheses is treated as a delimiter and breaks parsing.

Problem 4: The IS JSON Check Constraint Uses Oracle Syntax

ora2pg exports the products_json_c constraint as:

CONSTRAINT products_json_c CHECK (product_details IS JSON)

PostgreSQL has no IS JSON predicate. Loading the table DDL fails immediately with a syntax error.

The constraint is also redundant. JSONB in PostgreSQL validates JSON on every insert. Any row that fails JSON validation is rejected before it reaches storage. The IS JSON constraint is doing nothing that the column type does not already do.

The fix: delete the constraint line from the exported DDL before loading.

sed -i '/products_json_c/d' /home/fernando/ora2pg-co/output/CO_tables_fixed.sql

Verify:

grep 'products_json_c' /home/fernando/ora2pg-co/output/CO_tables_fixed.sql
# Expected: no output

The TEST report will show a CHECK CONSTRAINTS DIFF for the products table — one constraint in Oracle, zero in PostgreSQL. This is expected and correct.

Problem 5: Three Views Use Oracle-Specific SQL Functions

CO has four views. One (product_orders) uses standard SQL and converts without changes. Three use functions that have no direct PostgreSQL equivalent:

customer_order_products — LISTAGG with ON OVERFLOW

Oracle's LISTAGG(expr, separator ON OVERFLOW TRUNCATE '...' WITH COUNT) concatenates values into a delimited string with overflow handling.

PostgreSQL's STRING_AGG(expr, separator ORDER BY ...) is the equivalent — no overflow option.

-- Oracle
LISTAGG(p.product_name, ', ' ON OVERFLOW TRUNCATE '...' WITH COUNT)
  WITHIN GROUP (ORDER BY oi.line_item_id) items

-- PostgreSQL
STRING_AGG(p.product_name, ', ' ORDER BY oi.line_item_id) items

store_orders — GROUPING_ID

Oracle's GROUPING_ID(col1, col2) returns an integer bit vector encoding which columns are aggregated in a GROUPING SETS query.

PostgreSQL's GROUPING(col1, col2) does exactly the same thing — the function name differs, the semantics are identical.

The replacement is one-for-one.

-- Oracle
CASE grouping_id(store_name, order_status)
  WHEN 1 THEN 'STORE TOTAL'
  ...

-- PostgreSQL
CASE grouping(s.store_name, o.order_status)
  WHEN 1 THEN 'STORE TOTAL'
  ...

product_reviews — JSON_TABLE

Oracle's JSON_TABLE expands a JSON array into rows using a relational projection.

PostgreSQL has no JSON_TABLE equivalent before version 15 (it was added in PostgreSQL 15 as part of the SQL/JSON standard, but with different syntax).

For PostgreSQL 14 and earlier — or for consistent cross-version behaviour — rewrite using jsonb_array_elements:

-- Oracle
FROM products p,
     JSON_TABLE(p.product_details, '$'
       COLUMNS (NESTED PATH '$.reviews[*]'
         COLUMNS (rating INTEGER PATH '$.rating',
                  review VARCHAR2(4000) PATH '$.review'))) r

-- PostgreSQL
FROM products p,
     jsonb_array_elements(p.product_details->'reviews') AS review_row

In the PostgreSQL version, each JSON array element is exposed as a jsonb value named review_row.

Column values are extracted with the ->> operator: review_row->>'rating' and review_row->>'review'.

The cast ::INTEGER on the rating field is required because ->> always returns text.

This rewrite depends on product_details being JSONB — if the column is BYTEA (Problem 2 not fixed), the view fails immediately.

Running the Migration: Every Command in Order

Environment

Two VMs on the same network. srv1 (192.168.0.180) runs Oracle 19c with the CO schema in PDB pdb1. srv2 (192.168.0.181) runs Ubuntu with PostgreSQL 18 and ora2pg installed. All commands below run on srv2 unless stated otherwise.

Step 1 — Gather Oracle statistics

Run on srv1 before generating the report. ora2pg's effort estimates use Oracle's stored statistics — stale stats produce inaccurate counts.

-- On Oracle (srv1)
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS('CO');
  DBMS_STATS.GATHER_DICTIONARY_STATS;
END;
/

Step 2 — Create the output directory

mkdir -p /home/fernando/ora2pg-co/output

Step 3 — Base ora2pg.conf

# /etc/ora2pg/ora2pg.conf
ORACLE_DSN    dbi:Oracle:host=192.168.0.180;service_name=pdb1;port=1521
ORACLE_USER   system
ORACLE_PWD    <system password>
SCHEMA        CO
EXPORT_SCHEMA 1
CREATE_SCHEMA 1
OUTPUT_DIR    /home/fernando/ora2pg-co/output

Use system, not co — the COPY export reads v$database, which requires DBA privilege.

Step 4 — Generate the assessment report

ora2pg -t SHOW_REPORT --estimate_cost --dump_as_html \
  > /home/fernando/ora2pg-co/output/report-co.html

Open the HTML in a browser. CO should score A-3. The cost drivers are the three non-portable views.

Step 5 — Analyse column types

ora2pg -t SHOW_COLUMN > /home/fernando/ora2pg-co/output/columns-co.txt

Key columns to check:

Step 6 — Add MODIFY_TYPE and re-save the config

All overrides on a single line — MODIFY_TYPE ignores everything after the first line.

Do not use type precision (write numeric, not numeric(10,2)).

MODIFY_TYPE   PRODUCTS:PRODUCT_DETAILS:jsonb,PRODUCTS:UNIT_PRICE:numeric,ORDER_ITEMS:UNIT_PRICE:numeric,INVENTORY:STORE_ID:bigint,INVENTORY:PRODUCT_ID:bigint,INVENTORY:PRODUCT_INVENTORY:integer,ORDERS:CUSTOMER_ID:bigint,ORDERS:STORE_ID:bigint,ORDER_ITEMS:ORDER_ID:bigint,ORDER_ITEMS:LINE_ITEM_ID:integer,ORDER_ITEMS:PRODUCT_ID:bigint,ORDER_ITEMS:QUANTITY:integer,ORDER_ITEMS:SHIPMENT_ID:bigint,SHIPMENTS:STORE_ID:bigint,SHIPMENTS:CUSTOMER_ID:bigint

Step 7 — Run the schema exports

CO has no explicit sequences and no stored procedures or triggers.

Only TABLE and VIEW exports are needed.

ora2pg -t TABLE -o CO_tables.sql 2>&1 | tee /home/fernando/ora2pg-co/output/table-export-co.log
ora2pg -t VIEW  -o CO_views.sql  2>&1 | tee /home/fernando/ora2pg-co/output/view-export-co.log

After the TABLE export, verify the critical columns:

grep -E 'product_details|UNIT_PRICE|IDENTITY' /home/fernando/ora2pg-co/output/CO_tables.sql

product_details must show JSONB. Both UNIT_PRICE columns must show NUMERIC. IDENTITY columns must show GENERATED BY DEFAULT AS IDENTITY.

If any column shows the wrong type, the MODIFY_TYPE directive did not take effect — check the config.

Step 8 — Write the manual fix files

Two fix files cannot be generated from the ora2pg output.

CO_tables_fixed.sql — removes the IS JSON constraint:

cp /home/fernando/ora2pg-co/output/CO_tables.sql \
   /home/fernando/ora2pg-co/output/CO_tables_fixed.sql
sed -i '/products_json_c/d' /home/fernando/ora2pg-co/output/CO_tables_fixed.sql

CO_views_fixed.sql — rewrites the three Oracle-specific views:

SET search_path = co,public;

-- product_orders: standard SQL, no changes needed
CREATE OR REPLACE VIEW product_orders AS
  SELECT p.product_name, o.order_status,
         SUM(oi.quantity * oi.unit_price) total_sales,
         COUNT(*) order_count
  FROM   orders o
  JOIN   order_items oi ON o.order_id = oi.order_id
  JOIN   customers c    ON o.customer_id = c.customer_id
  JOIN   products p     ON oi.product_id = p.product_id
  GROUP  BY p.product_name, o.order_status;

-- customer_order_products: LISTAGG → STRING_AGG
CREATE OR REPLACE VIEW customer_order_products AS
  SELECT o.order_id, o.order_tms, o.order_status,
         c.customer_id, c.email_address, c.full_name,
         SUM(oi.quantity * oi.unit_price) order_total,
         STRING_AGG(p.product_name, ', ' ORDER BY oi.line_item_id) items
  FROM   orders o
  JOIN   order_items oi ON o.order_id = oi.order_id
  JOIN   customers c    ON o.customer_id = c.customer_id
  JOIN   products p     ON oi.product_id = p.product_id
  GROUP  BY o.order_id, o.order_tms, o.order_status,
            c.customer_id, c.email_address, c.full_name;

-- store_orders: GROUPING_ID() → GROUPING()
CREATE OR REPLACE VIEW store_orders AS
  SELECT CASE grouping(s.store_name, o.order_status)
           WHEN 1 THEN 'STORE TOTAL'
           WHEN 2 THEN 'STATUS TOTAL'
           WHEN 3 THEN 'GRAND TOTAL'
         END total,
         s.store_name,
         COALESCE(s.web_address, s.physical_address) address,
         s.latitude, s.longitude,
         o.order_status,
         COUNT(DISTINCT o.order_id) order_count,
         SUM(oi.quantity * oi.unit_price) total_sales
  FROM   stores s
  JOIN   orders o    ON s.store_id = o.store_id
  JOIN   order_items oi ON o.order_id = oi.order_id
  GROUP  BY GROUPING SETS (
    (s.store_name, COALESCE(s.web_address, s.physical_address), s.latitude, s.longitude),
    (s.store_name, COALESCE(s.web_address, s.physical_address), s.latitude, s.longitude, o.order_status),
    o.order_status,
    ()
  );

-- product_reviews: JSON_TABLE → jsonb_array_elements
CREATE OR REPLACE VIEW product_reviews AS
  SELECT p.product_name,
         (review_row->>'rating')::INTEGER AS rating,
         ROUND(
           AVG((review_row->>'rating')::INTEGER)
             OVER (PARTITION BY p.product_name),
           2
         ) avg_rating,
         review_row->>'review' AS review
  FROM   products p,
         jsonb_array_elements(p.product_details->'reviews') AS review_row;

Step 9 — Export the data

ora2pg -t COPY -o CO_data.sql 2>&1 | tee /home/fernando/ora2pg-co/output/copy-export-co.log

After the export, check for embedded newlines in the JSON data. ora2pg does not escape literal newline characters inside JSON string values.

If any product_details value contains an actual newline, the COPY parser treats it as a row separator and fails with invalid input syntax for type json.

Fix embedded newlines before loading:

python3 << 'EOF'
import re

with open('/home/fernando/ora2pg-co/output/CO_data.sql', 'r') as f:
    lines = f.read().split('\n')

result = []
in_products = False
pending = None

for line in lines:
    if re.match(r'COPY products\b', line):
        in_products = True
        result.append(line)
        continue
    if in_products:
        if line == '\\.':
            if pending is not None:
                result.append(pending)
                pending = None
            result.append(line)
            in_products = False
            continue
        if re.match(r'^\d+\t', line):
            if pending is not None:
                result.append(pending)
            pending = line
        else:
            pending = (pending + '\\n' + line) if pending else line
    else:
        result.append(line)

with open('/home/fernando/ora2pg-co/output/CO_data_fixed.sql', 'w') as f:
    f.write('\n'.join(result))
print("Done")
EOF

Verify the row count is correct:

grep -c $'\t' /home/fernando/ora2pg-co/output/CO_data_fixed.sql
# Expected: 8783

Step 10 — Create the database

sudo -u postgres psql -c "CREATE ROLE co WITH LOGIN PASSWORD 'co';"
sudo -u postgres psql -c "CREATE DATABASE codb OWNER co;"
PGPASSWORD=co psql -U co -d codb -h localhost -c "CREATE SCHEMA co;"

Step 11 — Load in dependency order

# Tables first
PGPASSWORD=co psql -U co -d codb -h localhost \
  -f /home/fernando/ora2pg-co/output/CO_tables_fixed.sql

# Data — FKs are dropped inside the file before load, not re-added after
PGPASSWORD=co psql -U co -d codb -h localhost \
  -f /home/fernando/ora2pg-co/output/CO_data_fixed.sql

Step 12 — Re-apply FK constraints

ora2pg's COPY file drops all FK constraints before the data load and never re-adds them.

This is the same bug as in the HR lab (issue #1960).

(echo "SET search_path TO co;"; \
 grep 'ADD CONSTRAINT.*FOREIGN KEY' \
   /home/fernando/ora2pg-co/output/CO_tables_fixed.sql) | \
  PGPASSWORD=co psql -U co -d codb -h localhost

Verify the count:

PGPASSWORD=co psql -U co -d codb -h localhost -c "
SELECT COUNT(*) FROM information_schema.table_constraints
WHERE constraint_schema = 'co' AND constraint_type = 'FOREIGN KEY';"
# Expected: 9

Step 13 — Advance IDENTITY sequences

PGPASSWORD=co psql -U co -d codb -h localhost << 'EOF'
SET search_path = co, public;
SELECT setval(pg_get_serial_sequence('co.customers',  'customer_id'),  (SELECT MAX(customer_id)  FROM co.customers));
SELECT setval(pg_get_serial_sequence('co.orders',     'order_id'),     (SELECT MAX(order_id)     FROM co.orders));
SELECT setval(pg_get_serial_sequence('co.shipments',  'shipment_id'),  (SELECT MAX(shipment_id)  FROM co.shipments));
SELECT setval(pg_get_serial_sequence('co.stores',     'store_id'),     (SELECT MAX(store_id)     FROM co.stores));
SELECT setval(pg_get_serial_sequence('co.products',   'product_id'),   (SELECT MAX(product_id)   FROM co.products));
EOF

Step 14 — Load the views

PGPASSWORD=co psql -U co -d codb -h localhost \
  -f /home/fernando/ora2pg-co/output/CO_views_fixed.sql
# Expected: CREATE VIEW × 4 — no errors

Step 15 — Run the TEST comparison

Add PostgreSQL connection to ora2pg.conf:

PG_DSN    dbi:Pg:dbname=codb;host=localhost;port=5432
PG_USER   co
PG_PWD    co

ora2pg -t TEST 2>&1 | tee /home/fernando/ora2pg-co/output/CO_test.txt

What the Final TEST Report Looks Like

After all five fixes are applied, ora2pg -t TEST should show one DIFF and everything else OK.

CHECK CONSTRAINTS DIFF on products — Oracle has one check constraint (products_json_c), PostgreSQL has zero.

This is expected: the constraint was intentionally removed because it uses Oracle syntax and is redundant with JSONB column type validation.

Every other section — columns, indexes, PKs, FKs, tables, views, sequences, row counts — should show OK.

Any other DIFF line is a real problem that must be resolved before cutover.

In Summary

CO is a cleaner migration than HR or SH in some ways — no stored procedures, no triggers, no bitmap indexes, no partitioned tables. The problems it introduces are different in character.

IDENTITY sequences failing after a data load is invisible until the first INSERT in production — there is no warning during the migration itself. The setval step is easy to miss because the load succeeds cleanly without it.

The BLOB-as-JSON pattern is common in Oracle databases built before 21c, where there was no native JSON type. Every Oracle schema that stores JSON needs the same MODIFY_TYPE override and the same IS JSON constraint removal. The view rewrites are one-time manual work, but the STRING_AGG, GROUPING, and jsonb_array_elements substitutions are patterns you will see again on any Oracle schema that has analytical or JSON-querying views.

The FK type mismatch from NUMBER(38) columns is a systematic problem. PG_INTEGER_TYPE 1 handles bare NUMBER columns correctly, but anything declared with precision — even NUMBER(38), which is how Oracle represents a plain integer in its data dictionary — falls outside the rule. On a production schema, the SHOW_COLUMN output will contain many such columns. Each one needs a MODIFY_TYPE entry before the TABLE export.

If you are planning an Oracle-to-PostgreSQL migration and want an independent assessment of complexity, effort, and risk before committing to a timeline, I offer a fixed-price migration audit that produces exactly that.