IBM Db2 treats an empty string (”) as an actual string value with zero length.

Oracle Database does not distinguish between an empty string and NULL; when ” is stored or compared, Oracle automatically converts it to NULL.

DB2 treats '' as a real value
Oracle collapses '' into NULL

-- DB2 table: this is perfectly valid
CREATE TABLE PSXLATITEM (
    FIELDNAME     VARCHAR(18) NOT NULL,
    FIELDVALUE    VARCHAR(4)  NOT NULL,
    EFFDT         DATE        NOT NULL,
    EFF_STATUS    CHAR(1)     NOT NULL DEFAULT 'A',
    XLATLONGNAME  VARCHAR(30) NOT NULL,   -- stores '' (empty) in many rows
    XLATSHORTNAME VARCHAR(10) NOT NULL    -- stores '' (empty) in many rows
);

-- DB2 data (valid in DB2)
INSERT INTO PSXLATITEM VALUES('ACCT_TYPE','A',DATE('2020-01-01'),'A','','');
--                                                                    
-- empty strings, NOT NULL but valid in DB2



-- What Oracle receives
INSERT INTO PSXLATITEM (FIELDNAME, FIELDVALUE, EFFDT, EFF_STATUS, XLATLONGNAME, XLATSHORTNAME)
VALUES ('ACCT_TYPE', 'A', DATE '2020-01-01', 'A', '', '');
--                                               
--                   Oracle converts '' → NULL here

-- Oracle throws:
-- ORA-01400: cannot insert NULL into ("SCHEMA"."PSXLATITEM"."XLATLONGNAME")

When SQL Developer migrates tables, it carries over NOT NULL constraints — but data movement can fail or cause errors if:

  • Target columns have NOT NULL but source data has gaps
  • Db2 allows empty strings ” where Oracle treats them as NULL
  • Load order violates constraints mid-transaction
  • Default values differ between platforms

Fix Options
Option 1 — Add a DEFAULT on the Oracle side
sqlALTER TABLE emp MODIFY name VARCHAR2(100) DEFAULT ‘ ‘ NOT NULL;

Option 2 — Transform during migration (preferred)
sql– In your migration SELECT or view, coalesce empty strings
SELECT COALESCE(NULLIF(name, ”), ‘UNKNOWN’) AS name FROM db2_table;

Option 3 — Temporarily allow NULLs, clean up, then re-add constraint
sql– Step 1: migrate without constraint
ALTER TABLE emp MODIFY name VARCHAR2(100) NULL;

— Step 2: fix the data
UPDATE emp SET name = ‘N/A’ WHERE name IS NULL;

— Step 3: restore constraint
ALTER TABLE emp MODIFY name VARCHAR2(100) NOT NULL;

Author