Upgrade to ODI 12.2.1.3.0 Fails with "Error while executing DDL Patch M_05.01.01.01_05.01.01.02.xml" and "ORA-01440: column to be modified must be empty"

(Doc ID 2350290.1)

Last updated on JANUARY 31, 2018

Applies to:

Oracle Data Integrator - Version 12.2.1.3.0 and later
Information in this document applies to any platform.

Symptoms

When using the Upgrade Assistant (UA) to upgrade from Oracle Data Integrator (ODI) 11.x (i.e. ODI 11.1.1.9)  to ODI 12.2.1.3, following error occurs:


[2017-11-29T11:03:41.147+08:00] [odi] [NOTIFICATION:16] [ODI-14062] [oracle.odi.setup] ... Upgrading master repository from 04.03.04.02 to 05.01.01.01
[2017-11-29T11:03:41.152+08:00] [odi] [NOTIFICATION:16] [ODI-14063] [oracle.odi.setup] ... Applying DDL from file /odi/test/Oracle/Middleware/Oracle_Home/odi/sdk/lib/scripts/ORACLE/patches/M_04.03.04.02_05.01.01.01.xml
[2017-11-29T11:03:41.884+08:00] [odi] [NOTIFICATION:16] [ODI-14063] [oracle.odi.setup] ... Applying DDL from file /odi/test/Oracle/Middleware/Oracle_Home/odi/sdk/lib/scripts/ORACLE/patches/M_04.03.04.02_05.01.01.01.xml
[2017-11-29T11:03:41.923+08:00] [odi] [NOTIFICATION:16] [ODI-14062] [oracle.odi.setup] ... Upgrading master repository from 05.01.01.01 to 05.01.01.02
[2017-11-29T11:03:41.925+08:00] [odi] [NOTIFICATION:16] [ODI-14063] [oracle.odi.setup] ... Applying DDL from file /odi/test/Oracle/Middleware/Oracle_Home/odi/sdk/lib/scripts/ORACLE/patches/M_05.01.01.01_05.01.01.02.xml
[2017-11-29T11:03:43.445+08:00] [odi] [ERROR] [ODI-14064] [oracle.odi.setup] ... Error while applying DDL from file /odi/test/Oracle/Middleware/Oracle_Home/odi/sdk/lib/scripts/ORACLE/patches/M_05.01.01.01_05.01.01.02.xml
[2017-11-29T11:03:43.446+08:00] [odi] [ERROR] [ODI-14096] [oracle.odi.setup] ... Error While Upgrading Master Repository Error while executing DDL Patch M_05.01.01.01_05.01.01.02.xml
[2017-11-29T11:03:43.451+08:00] [ODI] [INCIDENT_ERROR] [ODIUPG-00008] [upgrade.ODI.ODI1] ... Unknown error happened.
[2017-11-29T11:03:43.451+08:00] [ODI] [INCIDENT_ERROR] [] [upgrade.ODI.ODI1] ... Cause: Unknown error happened.
[2017-11-29T11:03:43.452+08:00] [ODI] [INCIDENT_ERROR] [] [upgrade.ODI.ODI1] ... Action: Restore Master Repository from Backup and try to upgrade again.
[2017-11-29T11:03:43.452+08:00] [ODI] [INCIDENT_ERROR] [] [upgrade.ODI.ODI1] ... [[
oracle.odi.setup.RepositorySetupException: com.sunopsis.repository.manager.upgrade.RepositoryUpgradeException: Error while executing DDL Patch M_05.01.01.01_05.01.01.02.xml
    at oracle.odi.setup.support.MasterRepositorySetupImpl.upgradeMasterRepository(MasterRepositorySetupImpl.java:797)
    at oracle.odi.setup.support.MasterRepositorySetupImpl.upgradeMasterRepository(MasterRepositorySetupImpl.java:687)
    at oracle.odi.setup.support.MasterRepositorySetupImpl.upgradeMasterRepository(MasterRepositorySetupImpl.java:868)
    at oracle.odi.setup.RepositoryUpgradeServiceImpl.upgradeRepositories(RepositoryUpgradeServiceImpl.java:421)
    at oracle.ias.update.plugin.odi.ODIPlugin.upgrade(ODIPlugin.java:432)
    ...
Caused by: com.sunopsis.repository.manager.upgrade.RepositoryUpgradeException: Error while executing DDL Patch M_05.01.01.01_05.01.01.02.xml
    at com.sunopsis.repository.manager.upgrade.AbstractRepositoryUpgrade.executeUpgradeDDL(AbstractRepositoryUpgrade.java:157)
    at com.sunopsis.repository.manager.upgrade.AbstractRepositoryUpgrade.upgradeMasterRepository(AbstractRepositoryUpgrade.java:280)
    at com.sunopsis.repository.manager.upgrade.RepositoryUpgradeRunner.upgradeMasterRepository(RepositoryUpgradeRunner.java:469)
    at com.sunopsis.repository.manager.RepositoryManager.masterRepositoryPatch(RepositoryManager.java:4837)
    at oracle.odi.setup.support.MasterRepositorySetupImpl.upgradeMasterRepository(MasterRepositorySetupImpl.java:782)
    ... 12 more
Caused by: java.lang.RuntimeException: Error while executing alter table SNP_DATA modify I_DATA NUMBER(19)
    at com.sunopsis.dwg.xml.DwgXmlSession.execute(DwgXmlSession.java:101)
    at com.sunopsis.repository.manager.DDLSessionExecutor.executeDDL(DDLSessionExecutor.java:124)
    at com.sunopsis.repository.manager.upgrade.AbstractRepositoryUpgrade.executeUpgradeDDL(AbstractRepositoryUpgrade.java:155)
    ... 16 more
Caused by: java.sql.SQLException: ORA-01440: column to be modified must be empty to decrease precision or scale
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:495)
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:447)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1055)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:624)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:253)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:613)
    ...
Caused by: Error : 1440, Position : 28, Sql = alter table SNP_DATA modify I_DATA NUMBER(19), OriginalSql = alter table SNP_DATA modify I_DATA NUMBER(19), Error Msg = ORA-01440: column to be modified must be empty to decrease precision or scale
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:499)
    ... 40 more
]]

Changes

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms