ORA-01722: invalid number

Hi,

I got “ORA-01722 invalid number ” error in Oracle SQL .

ORA-01722 invalid number

Details of error are as follows.

SQL> select count(1),trunc(droptime) from dba_recyclebin group by trunc(droptime) order by 1 desc;
select count(1),trunc(droptime) from dba_recyclebin group by trunc(droptime) order by 1 desc
*
ERROR at line 1:
ORA-01722: invalid number

ORA-01722: invalid number

Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.

Action: Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.

Invalid number Oracle

The ORA-01722 error is related with the character string conversion to number.

If you get this error, use only numeric fields for numeric operations, don’t use character fields.

All expressions in the Statement evaluate to number if you use for numeric condition.

When you add or substract from dates, use only numeric value from the date.

In briefly, compare numeric values with the numeric values, and character or string values with character or string values. Firstly convert the expression to number if you compare it with the number.

You can use the TO_NUMBER, TO_BINARY_FLOAT, TO_BINARY_DOUBLE, TO_DATE, TO_TIMESTAMP, TO_TIMESTAMP_TZ  functions) can help you deal with data conversion errors.

To solve this error, avoid implicit conversion and compare with same datatypes.

If you compare between different datatypes, then use the explicit conversion.

Do you want to learn Oracle SQL, then read the following articles.

Oracle SQL Tutorials For Beginners – Learn Oracle SQL from scratch with Oracle SQL Online Course