Constructive Changes Are Not Working With Upgrade Install Mode: ORA-1440: "column to be modified must be empty to decrease precision or scale" (Doc ID 2419815.1)

Last updated on DECEMBER 17, 2024

Applies to:

Symptoms

On Oracle Life Sciences Data Management Workbench (DMW) 2.4.7 version,

When attempting to upgrade install a table with precision changes the following error occurs in the install log.

ERROR
-----------------------
Set install status to $INSTSTATUSES$UPGPREPARED
Set install status to $INSTSTATUSES$UPGDEPLACTIVE
Starting UPGDEPL_ACTIVE.
Deploying CDR_<ID>$UPGRADE_2
Unit Name=CDR_<ID>_$UPGRADE_2
An error prevents the Upgrade script from executing successfully. Please review upgrade log file $OWB_HOME/log/reorg.log. Generated upgrade script is $OWB_HOME/log/reorg.sql.

If applicable, original data can be restored from recovery table (typically postfixed '$$OCMrecovery'). Remove the recovery table afterward.
ORA-01440: column to be modified must be empty to decrease precision or scale

Execution failed

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Login to DMW with valid Credentials.
2. Enter Study Name and click Enter
3. Select the Study and navigate to Study Configuration Page
4. Select the Datamodel “MY_DM1”
5. Verify that the data is already loaded for the data model “MY_DM1” under “LISTINGS “ tab.
6. Navigate back to Study Configuration Page
7. Select the Datamodel “MY_DM1”
8. Click on “Checkout” Icon.
9. Verify that MY_TABLE1 is already present with 5 columns in Columns Section.
10. Select the “COL4” and click on Edit Icon.
11. Update the Precision value from 0 to 5 in “Modify Clinical Data Model” window
12. Click OK.
13. Verify that the updated value is reflected for respective column in Column Section
14. Click on “Install Model” Icon.
15. Click “Install Model” in confirmation box.
16. Verify that the Install Job in Progress Message is displayed.
17. Verify that Installation is failed with Install Job Status as “Failure”

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.