ORA-01722
- invalid_char: Either this is the first invalid character of the character string that is being converted to a number or UNISTR (character string) if the character is not compatible with the database character set.
- column_or_expression: The column or expression from where the invalid character comes.
Cause
The attempted conversion of a character string for column or expression to a number failed because the character string is not a valid numeric literal. Only numeric fields or character fields containing numeric data can be used in arithmetic functions or expressions. Only numeric fields can be added to or subtracted from dates. If "UNISTR" appears in the error message, the value is not compatible with the national character set and cannot be represented directly.
Action
Use the LIKE expression to identify the problematic value. Ensure that it contains only digits, a sign, a decimal separator, and the character "E" or "e", and retry the operation.
Additional Information
Details about the violating values are provided with the parameter
ERROR_MESSAGE_DETAILS=ON. You can enable the parameter using the
statement ALTER SESSION SET ERROR_MESSAGE_DETAILS = ON. The
error message details are:
ORA-01722: unable to convert string value containing invalid_char
to a number: column_or_expression
ORA-03302: (ORA-01722 details) invalid string value: string_value
For example, the following query indicates the string that cannot be converted to a number:
SQL> SELECT to_number(ename) FROM emp;
SELECT to_number(ename) FROM emp
*
ERROR at line 1:
ORA-01722: unable to convert string value containing 'S' to
a number: ENAME
ORA-03302: (ORA-01722 details) invalid string value: SMITH
If the parameter ERROR_MESSAGE_DETAILS is set to OFF or
DISALLOWED, the entire character string is not printed. However,
you can check the problematic string using the LIKE expression.
For example:
SQL> SELECT ename FROM emp WHERE ename LIKE '%S%';