Skip to main content
Delphix

How to resolve Oracle error ORA-01722

Applicable Masking Versions

  • All masking versions - this is a generic data type issue.

Issue

The masking job is failing and Oracle error ORA-01722 is reported in the logs.  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

The error code seen in the logs is related to the Update step, which reports an ERROR and then following 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.

External Links

Additional reference for the Oracle error ORA-01722 can be found here: http://www.dba-oracle.com/sf_ora_01722_invalid_number.htm

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