Skip to main content
Delphix

Numeric Overflow Error when Profiling or Masking an Oracle Table (KBA1367)

 

 

Issue

A profiling or masking job fails with "Numeric Overflow" error when executed against an Oracle table containing a NUMBER column with entries larger than 263-1 (9223372036854775807).

Troubleshooting Numeric Overflow Error

The dmsuite.log file will show errors similar to "Unable to get value 'Integer(38)' from database resultset, index 1" and "Numeric Overflow", as below.  In this example, table OVERFLOW contains a column defined as NUMBER(38) with an entry of 9223372036854775808.

2017-05-24 16:19:39,186 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204] INFO  24-05 16:19:39,186 - OVERFLOW - Dispatching started for transformation [KETTLE_MASK_XML_18_OVERFLOW_204]
2017-05-24 16:19:39,357 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204] ERROR 24-05 16:19:39,357 - Table input - Unexpected error
2017-05-24 16:19:39,357 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204] ERROR 24-05 16:19:39,357 - Table input - org.pentaho.di.core.exception.KettleDatabaseException:
2017-05-24 16:19:39,357 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204] Couldn't get row from result set
2017-05-24 16:19:39,357 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204]
2017-05-24 16:19:39,357 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204] Unable to get value 'Integer(38)' from database resultset, index 1
2017-05-24 16:19:39,357 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204] Numeric Overflow
2017-05-24 16:19:39,357 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204]
2017-05-24 16:19:39,357 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204]
2017-05-24 16:19:39,357 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204]         at org.pentaho.di.core.database.Database.getRow(Database.java:2758)
2017-05-24 16:19:39,357 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204]         at org.pentaho.di.core.database.Database.getRow(Database.java:2730)
2017-05-24 16:19:39,357 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204]         at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:153)
2017-05-24 16:19:39,357 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204]         at org.pentaho.di.trans.step.RunThread.run(RunThread.java:50)
2017-05-24 16:19:39,358 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204]         at java.lang.Thread.run(Thread.java:745)
2017-05-24 16:19:39,358 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204] Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
2017-05-24 16:19:39,358 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204] Unable to get value 'Integer(38)' from database resultset, index 1
2017-05-24 16:19:39,358 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204] Numeric Overflow
2017-05-24 16:19:39,358 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204]
2017-05-24 16:19:39,358 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204]         at org.pentaho.di.core.database.BaseDatabaseMeta.getValueFromResultSet(BaseDatabaseMeta.java:1900)
2017-05-24 16:19:39,358 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204]         at org.pentaho.di.core.database.DatabaseMeta.getValueFromResultSet(DatabaseMeta.java:2613)
2017-05-24 16:19:39,358 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204]         at org.pentaho.di.core.database.Database.getRow(Database.java:2750)
2017-05-24 16:19:39,358 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204]         ... 4 more
2017-05-24 16:19:39,358 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204] Caused by: java.sql.SQLException: Numeric Overflow
2017-05-24 16:19:39,358 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204]         at oracle.jdbc.driver.NumberCommonAccessor.throwOverflow(NumberCommonAccessor.java:4380)
2017-05-24 16:19:39,358 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204]         at oracle.jdbc.driver.NumberCommonAccessor.getLong(NumberCommonAccessor.java:667)
2017-05-24 16:19:39,358 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204]         at oracle.jdbc.driver.OracleResultSetImpl.getLong(OracleResultSetImpl.java:971)
2017-05-24 16:19:39,358 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204]         at org.pentaho.di.core.database.BaseDatabaseMeta.getValueFromResultSet(BaseDatabaseMeta.java:1864)
2017-05-24 16:19:39,358 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204]         ... 6 more
2017-05-24 16:19:39,358 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204]
2017-05-24 16:19:39,358 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_18_204] ERROR 24-05 16:19:39,358 - OVERFLOW - Errors detected!

The underlying transformation engine represents the NUMBER column type as a 64-bit value which cannot store values larger than 263-1.

Resolution 

A possible workaround for this issue is using the generic connector type. The Delphix masking engine includes the Oracle JDBC driver (ojdbc6.jar) which can be used with the generic connector type. 

To configure the JDBC connector see How to use generic JDBC connector. The following settings can be used when creating the connector.

JDBC URL:  jdbc:oracle:thin:@(description=(address=(host=<hostname>)(protocol=tcp)(port=<port>))(connect_data=(sid=<ORACLE_SID>)))

    Example:  jdbc:oracle:thin:@(description=(address=(host=10.0.0.1)(protocol=tcp)(port=1521))(connect_data=(sid=marina)))

Custom Driver Name:  oracle.jdbc.OracleDriver

The generic connector has limitations when compared with the Oracle connector type.  The generic connector supports neither dropping indexes nor disabling triggers and constraints. It also does not include support for identity columns, which is required for in-place masking of tables with no primary key defined nor a logical key specified within the ruleset although this is not required for in-place masking where the Bulk Data option is used.

Applicable Delphix Versions

This article applies to the following versions of the Delphix Engine:

Major Release

Sub Releases

5.1

5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0

5.0

5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1 ,5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4

Related Articles 

How to use generic JDBC connector

Can we use this link as the prior one is broken: https://docs.delphix.com/display/SUP...JDBC+connector