Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-01722 Invalid Number for numeric values.

User_FWAW3Mar 25 2022

I have a table:
SQL> desc tab1;
Name Type Nullable Default Comments
------------- ------------- -------- ------- --------
EMSID NUMBER(9)
EMS_VAL VARCHAR2(255)

EMS_VAL stores some alphanumeric values and some numeric values and this depends on EMSID (1 for numeric, 2 for alphanumeric)
Table data has several rows like this:
SQL> select emsid, ems_val from TAB1;
EMSID EMS_VAL
------------ --------
1 198
2 F

But, this query returns ORA-01722 Invalid Number :
SQL> select to_number(ems_val) from tab1 where emsid = 1;
select to_number(ems_val) from tab1 where emsid = 1
ORA-01722: invalid number
Even though we are using emsid=1 which will only lead to numeric values in ems_val, we are getting the invalid number.
The exact same data is in multiple DBs but strangely I am getting invalid number exception only in 1 of the DBs and not in the rest.
Is there a solution to this ?

Please advise.

Thanks

Comments
Post Details
Added on Mar 25 2022
7 comments
899 views