Oracle Reports ORA

Welcome to the Oracle community forum and interact with 2 million technicians entering the EBS running request to report an error, the ORA-01722.Oracle's description of the 1722 error is like this: ORA-01722: invalidnumber (invalid number ). Check whether the SQL statement is faulty. If the same SQL statement is executed in PLSQLdev, no error is reported. Reporting ORA-01

Welcome to the Oracle Community Forum, interact with 2 million technicians> enter the EBS run request to report an error, the 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. Reporting ORA-01

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

The EBS run request reported an error where the 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.