Skip to main content
Delphix

Numeric Overflow Error when Profiling or Masking an Oracle Table

Applicable Delphix Versions

  • 5.1
  • 5.0

Issue

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

Troubleshooting

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

It is possible to work around the issue by 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. Steps to configure the JDBC connector are provided at 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

Note that 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.

Additional Information

 

 

Conditional content (Pro member)
  • Was this article helpful?