ORA-01722: invalid number

Hi,

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

 

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.

 

 

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.

If you use INSERT INTO … VALUES (…), make sure that you provide numeric value for number fields.

 

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

 

 972 views last month,  205 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience. I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource. I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks. I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients. If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Check Also

ORA-01438: value larger than specified precision allowed for this column

Hi, I got ” ORA-01438: value larger than specified precision allowed for this column” error …

Leave a Reply