IBM Support

Error ORA-01758 table must be empty to add mandatory (NOT NULL) column

Troubleshooting


Problem

Attempts to execute extension.xml by running deployer.sh command leads to the error java.sql.SQLSyntaxErrorException: ORA-01758: table must be empty to add mandatory (NOT NULL) column in IBM Sterling Order Management.

Symptom

Steps to reproduce:

  1. The extensions.xml comprises of the following entry:
    <Attribute ColumnName="RETURN_MESSAGE" DataType="CLOB" DefaultValue=""
    Description="" Nullable="false" XMLName="RETURNMESSAGE" />
  3. Run the command
    > deployer.sh - entitydeployer

    The following error is seen:
    [java] [saneSqlExec] Failed to execute:  ALTER TABLE EXTN_HP_EXCHANGE_ORDER_LIST ADD ( RETURN_MESSAGE CLOB  NOT NULL )
    [java] C:Sterling93propertiesentityDeployer.xml:873: The following
    error occurred while executing this line:[java] C:Sterling93propertiesentityDeployer.xml:853:java.sql.SQLSyntaxErrorException: ORA-01758: table must be empty to add mandatory (NOT NULL) column

Cause

You can't add a NOT NULL column into a table that already contains records.

Diagnosing The Problem

You can't add a NOT NULL column into a table that already contains records (because that would automatically violate NOT NULL constraint). First add a column, insert some values in there (that would be the UPDATE statement), and - finally - alter a table to set the column NOT NULL.

Resolving The Problem

Steps to resolve:

  1. Update the extensions.xml as show below:
    --------------------------
    <Attribute ColumnName="RETURN_MESSAGE" DataType="CLOB" DefaultValue=""
    Description="" Nullable="true" XMLName="RETURNMESSAGE" />
    --------------------------
  2. Run the following command:
    > deployer.sh - entitydeployer

  3. After updating ( use update command) some values, you can re-run the same extensions.xml
    with Nullable="false"

    NOTE : You might want to get in touch with your oracle dbadmin since this is database level issue.

    Also when attempting to update data through API tester, you may come across the the error:
    --------------------------
    <Error ErrorCode="org.xml.sax.SAXParseException" ErrorDescription=""
    ErrorRelatedMoreInfo="The value of attribute "RETURNMESSAGE" associated
    with an element type "hpexchangeOrderList" must not contain the '<'
    character.">
    --------------------------

    As a workaround, instead of the special character "<", try passing "&lg" to resolve the SAXParser exception.

[{"Product":{"code":"SS6PEW","label":"Sterling Order Management"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"--","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.3","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]

Document Information

Modified date:
11 February 2020

UID

swg21959834

Page Feedback