ORA-01438:value larger than specified precision allowed error in SOA 11g logs

Issue:
SQLDataException: ORA-01438 Error Frequently in SOA Log Files (the SOA managed server out and diognostic log file)


Applies to:
Oracle SOA Suite 11.1.1.3.0 and later (11g R1)


What you see in logs:

Error while invoking bean "cube delivery": Exception not handled by the Collaxa Cube system.[[

an unhandled exception has been thrown in the Collaxa Cube systemr; exception reported is: "ORABPEL-00000

Exception not handled by the Collaxa Cube system.

an unhandled exception has been thrown in the Collaxa Cube systemr; exception reported is: "java.sql.SQLDataException: ORA-01438: value larger than specified precision allowed for this column

at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:83)

at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:135)

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:210)

Effects:

Degraded performance of SOA Composites. Server spends additional processing power on logging errors, so performance of server is impacted. Also lots of log files are generated which might fill your disk system fast.

Cause:

This is an issue which has been identified by Oracle. The bug 12621337: ORA-01438 ON LIVE_INSTANCES COLUMN

The problem is with the precision of the column COMPOSITE_INSTANCE.LIVE_INSTANCES currently defined to be NUMBER(3) which can at the most hold a value up to 999. As there is no explicit limitation on this column, the correct way to handle this is to increase the precision of this column to accommodate more instances.


Solution:
The workaround is to modify the COMPOSITE_INSTANCE.LIVE_INSTANCES column's precision to be NUMBER(38) in the SOAINFRA schema.
To apply this workaround, follow the steps below:

1. Stop the SOA domain

2. Log in to the SOA infra repository database as SYSDBA

3. Modify the COMPOSITE_INSTANCE table as shown below

ALTER TABLE 

soa11g_soainfra.composite_instance 

MODIFY (live_instances NUMBER(38))

;

In the above sql statement I have assumed the dehydration prefix is soa11g. Please modify the statement according to your prefix.Restart the SOA domain. Check the logs.