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