SOQL query returns "ORA-01722: invalid number" error
Last updated 2020-02-15 ·Reference W-4915227 ·Reported By 5 users
If there is any Custom Formula Field used with the formula as
VALUE(Phone)>0 || VALUE(Name)>0
and if Phone field is empty for any record then running SOQL query as
"Select Field1__c,Name from Account" will give ORA-01722: invalid number error
1. On Account Object create a custom Formula Field(any name) of type checkbox and set it as VALUE(Phone)>0 || VALUE(Name)>0
2. Now create an Account record with Phone Field as empty
3. From Dev Console, Run the below SOQL query,
Select Id,TestF__c,Phone from Account
and you will see the error returned.
1. Change the Formula Field to (!ISBLANK(Phone) && VALUE(Phone)>0) || VALUE(Name)>0
2. Alternatively, you can use ISNUMBER() function in place of VALUE()
Is it Fixed?
Any unreleased services, features, statuses, or dates referenced in this or other public statements are not currently available and may not be delivered on time or at all. Customers who purchase our services should make their purchase decisions based upon features that are currently available.