Troubleshooting of ORA-01722 errors reported by Oracle

EBS run request error, ORA-01722. Oracle's description of the 1722 error is like this: ORA-01722: invalid number (invalid number ).

Check whether the SQL statement is faulty. If the same SQL statement is executed in PL/SQL dev, no error is reported.

The error of ORA-01722, in general, is caused by an attempt to convert a string to a number, because the string may contain characters that are not numbers.

The following two points are summarized. If this error is found in the future, troubleshoot the problem from these two aspects.
If a ORA-01722 error occurs, there are two possible causes:
1) The executed SQL statement contains display or implicit type conversion. Conversion from a string to a numeric type fails. The character content contains non-numeric characters. Solution: Check whether the SQL statement contains the conversion of field types.

2) bind a character (varchar2) field (DefineByPos) to the number type, so that the conversion fails when the result is obtained. Solution: Check whether the bound variables correspond to the corresponding fields in sequence and the data type.

Summary: The root cause is oracle implicit type conversion. It is because when character fields and numeric variables are used for conditional queries, oracle automatically converts character fields or variables to numeric types, when the field contains characters, that is, when the to_number function is abnormal, The ORA-01722: Invalid Number error will be reported.