Resolving Oracle Error ORA-01722 (KBA1614)

Issue

This is a generic data type issue across all Masking versions.

The masking job fails with error log Oracle error ORA-01722.  The error reports that an attempt to convert a character string to a number failed because the character string was not a valid numeric literal.

This is a common error, especially in Secure Lookup Algorithms, if data in the lookup is a string and the column being updated is numeric (for example, a column with type NUMBER).

Troubleshooting Oracle Error ORA-01722

The error code seen in the logs is related to the Update step, which reports an ERROR  followed by the Oracle Error ORA-01722.

in # Example of Tranformation log entry showing the error
2017-04-11 17:09:34,948 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_job_exec]
2017-04-11 17:09:45,295 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_job_exec] ERROR 11-04 17:09:45,295 - Update - Unable to commit Update connection [CONNECTION NAME] :org.pentaho.di.core.exception.KettleDatabaseBatchException:
2017-04-11 17:09:45,295 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_job_exec] Error updating batch
2017-04-11 17:09:45,295 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_job_exec] ORA-01722: invalid number

Resolution 

Verify that the Masking Algorithm (usually Secure Lookup)  is fit for the Data Type. In the case of ORA-01722, a numeric value is required.

• Check that the Algorithm only generates numeric values.
• Check that the Algorithm generates masked data without leading/trailing spaces.
• Check that the Algorithm doesn't generate a masked data with a new line with no number.